Problem with date SQL 1

[jimb0ne]

Hi folks, I have a problem with a date lookup in an SQL statement. Basically I have a date field in a query, I'm using the findfirst method

recordset.findfirst "Date = #" & formatdatetime(datestring, vblongdate) & "#"

this worked fine on one computer, upon moving my database to another computer I got error 3077 "syntax error in date in expression" I changed the date type to vbgeneraldate and the code works fine. Why is there a difference between the 2 computers, and is there a way to check for this before I run the find method, or do I just have to set up error checking to check for the error, and change the date type accordingly?

thanks in advance,
James

 

[Remou]

Hi
As far as I recall, the date formats above are set by the user on the control panel, so they will vary from computer to computer. Here is a thread regarding date formats for sql: thread705-619383.

 

[TheAceMan1]

How are ya jimb0ne . . . . .

Have you tried:

[blue]recordset.findfirst "Date = #" & [blue]datestring[/blue] & "#"[/blue]

See Ya! . . . . . .

 

[jimb0ne]

hi guys, yeah I have tried just the date, but I had a problem on the original computer with the short type (mm/dd/yyyy) creating a problem, and it worked when I changed the type to vblongdate, on this other computer though the shortdate type works, and longdate doesn't work. It must have something to do with the regional settings, I've got it set up to change the date type accordingly if the error is generated, so the problem is no more, I was just hoping to find more information about what setting is causing this.....

thanks again,
James

 

[RoyVidar]

jimbOne - when using dynamic SQL, and dates, there are two formats that would normally provide unambiguous date format when concatenated into a string regardless of locale:

[tt]format$(mydate, "mm\/dd\/yyyy") ' and
format$(mydate, "yyyy-mm-dd")[/tt]

Note the escape characters in the first one, and have a look at the explanations here International Dates in Access for why.

Roy-Vidar