SQL Syntax for CDate Function

[Guest]

I am having the follwing issue:

I allow a user to enter a starting date on a form that dynamically generates
the SQL code behind it.

Dates in the DB are stored as strings in the format "yyyy/mm/dd", so I need
to convert them to date using CDate.

I have tried every version of syntax I can think of to make the SQL
statement to generate correctly with no luck. I'm hoping another set of eyes
will see the probably obvious issue with this code. The code generates error
2001 "You cancelled the previous operation" which most likley indicates an
SQL syntax issue.

where = where & " AND "
+ "(Format(Cdate([MAINT].[MAINT_TEST_DATE]), 'yyyy/mm/dd'))"
+ " >= #"
+ (Me![txtTestStartDate])
+ " #"

 

[Jeff Boyce]

Take a look at the DateSerial() function. You could "parse" out the pieces
of the date using Left(), Right(), Mid() functions.

 

[Van T. Dinh]

Try:

strWhere = strWhere & " And [MAINT].[MAINT_TEST_DATE] = '" & _
Format(Me![txtTestStartDate], "yyyy/mm/dd") & "'"

Notes:

* Single-quote + double-quote near the end of line 1.

* Double-quote + single-quote + double-quote at end of line 2.

This way is muchmore efficient as the Format function has to be evaluated
only _once_ rather than trying to manipulate the Table Field values (the
function used will be called once for each Record in the Table).

 

[Brendan Reynolds]

This works for me. The Format property of my text box ('Text0' in this
example) is 'General Date' - that *might* make a difference.

Private Sub Command2_Click()

Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT TestID FROM tblTest WHERE TestText = '" & _
Format$(Me.Text0, "yyyy/mm/dd") & "'"
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = strSQL
.Open

'The record with this value in the ID field
'is the only record that matches the criteria.
If .Fields("TestID") = 2 Then
MsgBox "OK!"
Else
MsgBox "Uh-Oh!"
End If
.Close
End With

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

Thanks for the input - That was a much more efficient solution, as you
mentioned, and it was effective in getting the proper results. Thanks so
much!