using Date with SQL statement

a_20120

tan
Local time
Today, 22:22
Joined
Nov 21, 2006
Messages
175
Hello everyone,

I want to have advanced search through VBA and I use the below code.
"WHERE (tblPicture.PicDate) = (Me.txtDate.Value)"
after the select statment, everything is ok, but it asks me for date (parameter)
could anyone plz help me
 
Try this:-

"where tblPicture.PicDate = DateValue('" & Me.txtDate & "')"


On a system using US date format m/d/yyyy, you can also use the # signs to delimit the date in the text box:-

"where tblPicture.PicDate = #" & Me.txtDate & "#"
.
 
Thanks Much,
Yeah that is what I wanted. Thnx again much
 
Be aware, m/d/yyyy isn't always enough. For instance, it causes Access to barf where I live. To find out why, check out http://allenbrowne.com/ser-36.html, which demonstrates one safe format. Another safe format, is the ISO 8601 date format "yyyy-mm-dd".

"where tblPicture.PicDate = #" & format$(Me!txtDate, "yyyy-mm-dd") & "#"
 
Originally Posted by Jon K
On a system using US date format m/d/yyyy, you can also use the # signs to delimit the date in the text box:-

"where tblPicture.PicDate = #" & Me.txtDate & "#"

Originally Posted by RoyVidar
Be aware, m/d/yyyy isn't always enough. For instance, it causes Access to barf where I live. To find out why, check out http://allenbrowne.com/ser-36.html, which demonstrates one safe format. Another safe format, is the ISO 8601 date format "yyyy-mm-dd".

"where tblPicture.PicDate = #" & format$(Me!txtDate, "yyyy-mm-dd") & "#"

On a system using US date format, a user would naturally type the date in the text box in US format, so

"where tblPicture.PicDate = #" & Me.txtDate & "#"

is a very safe format and there is absolutely no need to use the Format() function to convert the US date in the text box to "yyyy-mm-dd" format (or any other format) for use with the # signs as in:-

"where tblPicture.PicDate = #" & format$(Me!txtDate, "yyyy-mm-dd") & "#"


On the other hand, if the user has typed the date in UK format (or a format other than US format) on a system using US date format, then even using

"where tblPicture.PicDate = #" & format$(Me!txtDate, "yyyy-mm-dd") & "#"

cannot convert the non-US date to the correct US date for use with the # signs.
.
 
Last edited:
The easiest way to get around the date format is to use Format() on both sides of the operator.

For instance:
WHERE Format(tblPicture.PicDate,...) = Format(Me.txtDate,...)

It will slow down the database, but in cases where the format is changing regularly, and in cases where a dates are stored as a text or a date datatype, it is more useful.
 
I'm sorry, I thought you were operating on dates, but you are operating on text.

I don't use text criteria for date fields, I find it too risky, so I use dates. When using dates, proper formatting when concatenating into an SQL string, will ensure the app will work regardless of locale.
 

Users who are viewing this thread

Back
Top Bottom