Problem with Format(Date) in SQL statement

[G Gerard]

Hello

I am having some problem comparing dates with the SQL statement below
MySQL = "SELECT Format(Date, 'yyyy/mmmm/dd') as [Expr1] FROM TblDates WHERE
_
Format(Date, 'yyyy/mmmm/dd') = #2006/April/18#"

Even though the date 2006/April/18 is in TblDates, it is never found by the
above SQL statement

Anyone know why?
Thanks

G Gerard

[John Mishefske]

G Gerard wrote:

Hello

I am having some problem comparing dates with the SQL statement below
MySQL = "SELECT Format(Date, 'yyyy/mmmm/dd') as [Expr1] FROM TblDates WHERE
_
Format(Date, 'yyyy/mmmm/dd') = #2006/April/18#"

Even though the date 2006/April/18 is in TblDates, it is never found by the
above SQL statement

Anyone know why?

If the field in your table is named "Date" then that's probably the problem. "Date" is a
reserved word and should never be used as a field name. You might get this to work by
enclosing the field name in brackets but using "Date" as a field name is a no-no.

And you don't need to use format in the WHERE clause; that's just going to slow you down.

MySQL = "SELECT Format([Date], 'yyyy/mmmm/dd') as yourDateName FROM TblDates" & _
" WHERE [Date] = #4/18/2006#;"

but it would be better as:

MySQL = "SELECT Format([myDate], 'yyyy/mmmm/dd') as yourDateName FROM TblDates" & _
" WHERE myDate = #4/18/2006#;"
--
'---------------
'John Mishefske
'---------------