Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with date SQL 1

Status
Not open for further replies.

jimb0ne

Programmer
Oct 6, 2003
291
CA
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
 
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.
 
How are ya jimb0ne . . . . .

Have you tried:
Code:
[blue]recordset.findfirst "Date = #" & [blue]datestring[/blue] & "#"[/blue]


Calvin.gif
See Ya! . . . . . .
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top