Puzzled by Date

[Stevie]

I have a form which displays the results of a search on a table which holds records on all changes made to any employee record in the database.
Searching is done by entering an employee Id, or an employee id and a date.
If I enter just the employee id I get all the changes that have occurred returned.
Now heres the strange bit, if I enter an id and a date from March (ie 20/03/2000) it returns all changes on that date correctly for the particular employee, but if I enter a date from April (ie 06/04/2000) then no records are returned when there should be.

Anybody out there have any ideas at all?

[UKPhil]

I'm not sure if you're having the same problem that I did but I find that the Microsoft drivers can often revert the date back into US format e.g. mm/dd/yyyy which means that your query will look for 4th June 20000 !!

Try using the Format function in your query to force the UK date format, e.g. Format(Date_Field,"dd/mm/yyyy").

Hope this helps.

[Bonker Gudd]

I always pass dates about in "dd mmm yyyy" format.

[Stevie]

Thanks for the input but I'm already formating as dd/mm/yyyy.

Anyone else?

[FireBeast]

either make sure you're using US date format in your SQL or use datevalue() function.


FireBeast

[Stevie]

Thanks for your ideas as well but I've tried both of those as well.

Anybody else?

[SonGouki]

Is your table in a database on your machine or on a remote one? The reason I ask is because most database keep their own standard Date format, which means regardless of what format you're machine is in the database only really knows it's own format. It will try to convert the date you pass it but it won't know which way to convert it if it is ambiguous, such as "05/05/2000". It will only take a guess at which field is for the month and which is for the day. When you submit a date which actually has day over 12 it will obviously know which fields are which. This is why you're getting mixed results, probably.

Long story short, the best way to get around this (IMHO) is to be sure to format all dates that you query the database with into the format of "yyyy-mmm-dd" (ie. "2000-FEB-02"). This is one of the universal date formats and when you use it the database will know what you're sending.

Hope this helps.

[Stevie]

Cheers, thanks for your help.
I can stop giving myself brain strain now.

[SonGouki]

No problem.
By the way thanks for replying, it's always nice to have acknowledgement of the solutions posted. I always check any threads I've posted to and it's good to know if the information was useful or not. This goes out to anybody else reading this post as well, always check on threads you've opened and post a response when you've gotten what you need (or not), and close the thread when you've gotten what you need. Your only being polite to all those who spent time replying to your post trying to solve your problems. We like to know if our info was useful.

Once again, thanks Stevie,
later.

[Stevie]

My sentiments exactly, thats the same as what I do.
Always nice to know you helped someone.
Thanks for the thanks.