Help with Select Syntax

[Chaster]

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)]

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"