SQL Date Criteria Format (1 Viewer)

npjonze

Registered User.
Local time
Today, 13:42
Joined
Jul 31, 2001
Messages
10
I've got a problem where my SQL string is finding dates in US format ie mm/dd/yy. All my forms, tables etc are dd/mm/yy ( my regional setting is dd/mm/yy). Reading the help file it says that all 'Date literals' in the criteria argument must be in US format. How do you acheive this when every other date is in UK format?

My code is as follows. All I want to do is find if the recordcount >0 of the found set as a check to an import procedure.

Dim strSQL as String
Dim rs as Recordset
Dim db as Database


strSQL = "SELECT Distinctrow from tblData WHERE DatetblData = #" & txtDate & "#" ' txtDate is Date from textbox on an unbound form

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

if rs.recordcount>0 then
blah blah
else
blah blah blah
 

jwindon

Registered User.
Local time
Today, 13:42
Joined
Aug 12, 2001
Messages
531
I won't attempt to code this as I am way too tired tonight. Just thought I could make a suggestion. Try using the DatePart functions to "take out" each piece of your dates.

DatePart("d",[datefield])

Then piecing them back together in a string to feed your query.

I might also suggest updating your data in the same method to the US format since Access requires this type of format to run queries. You could display the data on your forms, etc. in UK format.

Also, I strongly recommend using one format or the other. Not both. for your dates. Either way you go, you will need to update some data.

[This message has been edited by jwindon (edited 12-16-2001).]
 

R. Hicks

AWF VIP
Local time
Today, 08:42
Joined
Dec 23, 1999
Messages
619
Try using the following:

strSQL = "SELECT Distinctrow from tblData WHERE DatetblData = #" & Format(Me.txtDate, "mm/dd/yy") & "#"

HTH
RDH
 

npjonze

Registered User.
Local time
Today, 13:42
Joined
Jul 31, 2001
Messages
10
Thanks for the advice. Helpfull as always. I've done what you suggested ie Format ( mm/dd/yy) and it works just fine and I'll try to stick to one date format!

Once againg thanks!!
 
R

Rich

Guest
Should I throw a spanner in the works and suggest that I've never had to format dates in the U.S. format to get them to work in queries.
Could it really be just Luck?
 

RossG

Registered User.
Local time
Today, 13:42
Joined
Apr 18, 2001
Messages
21
I had the same problem as NP during my first exercise in QBF coding. I used code from the MS Knowledge Base as a template but just could not get dates to work in the SQL. Eventually found that the dates had to be in mm/dd/yy format, not dd/mm/yy as used locally.

[This message has been edited by RossG (edited 02-03-2002).]
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:42
Joined
Feb 19, 2002
Messages
42,970
Rich, you've said that before. I'm a little curious. Is it that you dont' have any queries that use user entered date parameters? The date format only becomes an issue when you are trying to use a string date as a parameter for a query.
 
R

Rich

Guest
Yes I have to admit I've never had to construct an SQL string as posted here so as an exercise I tackled it. After numerous syntax errors etc I used this
strSQL = "SELECT OrderDate FROM tblOrder WHERE [OrderDate] = " & Format(Me!txtDate, "0"). Although I may never use it I'll save it for future reference
 

Users who are viewing this thread

Top Bottom