Professional Web Applications Themes

Help with Select Syntax - Microsoft Access

I created my query, in the query builder, and am now trying to paste it into my module. I don't know how to end a line and let the program know to continue on with the next line. I also don't understand where the parts of the Select Statement should be broken (in the module). Is there an easy way to remember how the statement should be constructed? I am reading this as Select all the airportcodes and areas from the SupplierRates table Where the airportcode and the area = the values on the form named instructions. '**** AirPort code ...

  1. #1

    Default Help with Select Syntax

    I created my query, in the query builder, and am now trying to paste it into
    my module. I don't know how to end a line and let the program know to
    continue on with the next line.

    I also don't understand where the parts of the Select Statement should be
    broken (in the module). Is there an easy way to remember how the statement
    should be constructed?

    I am reading this as
    Select all the airportcodes and areas
    from the SupplierRates table
    Where the airportcode and the area = the values on the form named
    instructions.

    '**** AirPort code found - Now check Area
    SELECT SupplierRates.AirPortCode, SupplierRates.Area
    FROM SupplierRates WHERE
    (((SupplierRates.AirPortCode)=[forms]![instructions]![airportcode]) AND
    ((SupplierRates.Area)=[forms]![instructions]![area]));

    The other part I would like help with is now that I have run this query I
    want to know if a value was returned. If a value is returned I would do
    nothing but if no records were found matching this criteria I would want to
    display a
    MsgBox " We Can Not Service This Area"


    Chaster Guest

  2. #2

    Default Re: Help with Select Syntax


    strSQL = "SELECT SupplierRates.AirPortCode, SupplierRates.Area" & _
    " FROM SupplierRates " & _
    " WHERE (((SupplierRates.AirPortCode)=" & _
    & Chr(34) & [forms]![instructions]![airportcode]) & Chr(34) & _
    " AND ((SupplierRates.Area)=" & _
    & Chr(34) & [forms]![instructions]![area] & Chr(34) & "));"

    I made the guess that AirPortCode and Area fields are text fields. If not,
    remove the Chr(34) that are there to force quotes around the values of the controls.

    End a line (continuation character) is a space followed by an underscore. The
    continuation cannot be within a string.

    What does the rest of your code look like? You might be able to use DCount to
    do what you need without running a query, putting the results into a recordset
    and seeing if any records are in the recordset.

    code might look something like:

    If DCount("*","SupplierRates","SupplierRates.AirPortC ode=" & _
    & Chr(34) & [forms]![instructions]![airportcode]) & Chr(34) & _
    " AND SupplierRates.Area=" & _
    & Chr(34) & [forms]![instructions]![area] & Chr(34) ) = 0 Then
    MsgBox "I canna do it, Captain. I need moooore time."
    End If

    Chaster wrote:
    >
    > I created my query, in the query builder, and am now trying to paste it into
    > my module. I don't know how to end a line and let the program know to
    > continue on with the next line.
    >
    > I also don't understand where the parts of the Select Statement should be
    > broken (in the module). Is there an easy way to remember how the statement
    > should be constructed?
    >
    > I am reading this as
    > Select all the airportcodes and areas
    > from the SupplierRates table
    > Where the airportcode and the area = the values on the form named
    > instructions.
    >
    > '**** AirPort code found - Now check Area
    > SELECT SupplierRates.AirPortCode, SupplierRates.Area
    > FROM SupplierRates WHERE
    > (((SupplierRates.AirPortCode)=[forms]![instructions]![airportcode]) AND
    > ((SupplierRates.Area)=[forms]![instructions]![area]));
    >
    > The other part I would like help with is now that I have run this query I
    > want to know if a value was returned. If a value is returned I would do
    > nothing but if no records were found matching this criteria I would want to
    > display a
    > MsgBox " We Can Not Service This Area"
    John Spencer (MVP) Guest

Similar Threads

  1. Need help formatting syntax for SELECT query on a Bittype data field
    By ca_nimrod in forum Coldfusion Database Access
    Replies: 2
    Last Post: January 11th, 03:23 AM
  2. Tricky SQL SELECT syntax, can it be done?
    By Mattsala in forum Macromedia ColdFusion
    Replies: 3
    Last Post: March 15th, 04:27 PM
  3. error : syntax error at or near $1 for over select rows
    By vinita bansal in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 27th, 06:51 PM
  4. SELECT: Syntax error. Please help
    By sam in forum PHP Development
    Replies: 14
    Last Post: September 29th, 10:32 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139