Regardless of what your regional settings may be, you cannot use dd-mm-yy
as the format. In queries, Access will ALWAYS treat 12/01/09 as 1 Dec,
2009. (It will treat 13/01/09 as 13 Jan, 2009 only because there's no 13th
month)
And Alex unfortunately left out the fact that against Jet tables you need
to use # as a delimiter with dates (against SQL Server tables, you use a
single quote). Without the delimiter, Access is going to treat 13/01/09 as
arithmetic, or 1.444444, which to Access is 10:40 AM on 31 Dec, 1899.
(Access stores date/time values as eight byte floating point numbers where
the integer portion represents the date as the number of days relative to
30 Dec, 1899, and the decimal portion represents the time as a fraction of
a day)
Try:
If Not IsNull(Me.txtDate) Then
strWhere = strWhere & "[Date] = " & _
Format(CDate(Me.txtDate), "\#yyyy\-mm\-dd\#") & " AND "
End If
Incidentally, you really should rename the table field from Date. Date is
a reserved word, and use it can lead to issues. For a comprehensive list
of names to avoid (plus a link to a free utility to check your application
for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html
You might also find it useful to read Allen's discussion on International
Dates in Access at
http://www.allenbrowne.com/ser-36.html, or what I had
in my September, 2003 "Access Answers" column in Pinnacle Publication's
"Smart Access". You can download the column (and sample database) for free
at
http://www.accessmvp.com/DJSteele/SmartAccess.html
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
lhtan123 said:
Hi,
I rewrote it as:
strWhere = strWhere & "[Date] = " & format(Me.txtDate,"dd-mm-yy") &
"AND "
There is a trailer " AND " as there is more than one criterion that forms
the WHERE condition. What I'm more concerned is the
"format(Me.txtDate,"dd-mm-yy") " that prompts me to enter "Jan" which
doesn't
makes any sense to me.
I suppose it's Jet engine cos' my database is not using SQL server.
Alex Dybenko said:
Hi,
if you are using Jet tables - then date should be formatted as
mm\dd\yyyy:
strWhere = strWhere & "[Date] = " & format(Me.txtDate,"mm\/dd\/yyyy")
and if you are using SQL server tables - then ddmmyyyyy:
strWhere = strWhere & "[Date] = '" & format(Me.txtDate,"ddmmyyyy") &
"'"
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
I have a Search form with two combo boxes and one textbox that allows
records
to be filtered by one or more of these criterion.
The textbox allows a date to be entered. As it is a string value, how
could
I convert it into a DateTime value using SQL ? The following code is
to
form
an SQL statement used as a WHERE condition. It does not generate any
result
and I'm not sure how to write it.
If Not IsNull(Me.txtDate) Then
strWhere = strWhere & "[Date] = " & CDate(Me.txtDate)
End If
