Using Date fields in SQL

  • Thread starter Thread starter John Budding
  • Start date Start date
J

John Budding

I have a form with a date control (Checkdate), which I need to pass to the
WHERE part of a SELECT SQL
at present I have VBA Code . . .
SQLS = "SELECT [T: Stewards ROTA].Rotadate, [T: Stewards ROTA].Vol1, [T:
Stewards ROTA].Vol2, [T: Stewards ROTA].Vol3 " & vbCrLf & _
"FROM [T: Stewards ROTA] " & vbCrLf & _
"WHERE ((([T: Stewards ROTA].Rotadate)=[Forms]![F: Refreshment
ROTA]![Checkdate]));"
DoCmd.RunSQL SQLS

and get the following
"Run-Time error '2342'
A RunSQL action requires an argument consisting of a SQL statement."

I feel I am not correctly passing the Checkdate, but can't see how
Help Please!
 
Concatenate the date into the string. This kind of thing:

With Forms![Forms]![F: Refreshment ROTA]![Checkdate]
If IsDate(.Value) Then
SQLS = "SELECT [T: Stewards ROTA].Rotadate, [T: Stewards ROTA].Vol1,
[T: Stewards ROTA].Vol2, [T: Stewards ROTA].Vol3 " & vbCrLf & _
"FROM [T: Stewards ROTA] " & vbCrLf & _
"WHERE [T: Stewards ROTA].Rotadate = " & Format(.Value,
"\#mm\/dd\/yyyy\#") & ";"
dbEngine(0)(0).Execute SQLS, dbFailOnError
Else
MsgBox "Enter a valid date."
End If
End With

Notes:
====
1. Don't change the format string, regardless of your local date settings.
Explanation:
http://allenbrowne.com/ser-36.html

2. Execute is more powerful than RunSQL. Explanation:
http://allenbrowne.com/ser-60.html

3. If you're still stuck, use:
Debug.Print SQLS
Then when it fails open the Immediate Window (Ctrl+G) to see the statement,
and see what's wrong.
 
I have a form with a date control (Checkdate), which I need to pass to the
WHERE part of a SELECT SQL
at present I have VBA Code . . .
SQLS = "SELECT [T: Stewards ROTA].Rotadate, [T: Stewards ROTA].Vol1, [T:
Stewards ROTA].Vol2, [T: Stewards ROTA].Vol3 " & vbCrLf & _
"FROM [T: Stewards ROTA] " & vbCrLf & _
"WHERE ((([T: Stewards ROTA].Rotadate)=[Forms]![F: Refreshment
ROTA]![Checkdate]));"
DoCmd.RunSQL SQLS

and get the following
"Run-Time error '2342'
A RunSQL action requires an argument consisting of a SQL statement."

I feel I am not correctly passing the Checkdate, but can't see how
Help Please!
Click to expand...

It has nothing to do with CheckDate.

The RunSQL command is used to run Action queries - Delete, Insert, Update
queries. It does not work for a SELECT query.

As for the date field, you may need to define it as a date parameter: put

Parameters [Forms]![F: Refreshment ROTA]![Checkdate] DateTime;

before the SELECT statement.
 
Back
Top