Date format problem in 2001

[goldcomm]

I have a problem with an event procedure that was working fine up until 2001

A user is prompted on a form for a date to be used as a criteria for an SQL query e.g.

dtCriteria = Format(Me.Weeksaturday, "mm\/dd\/yyyy")

As a UK user I have converted the date to USA format as I understand SQL assumes this

However if the date is greater than 12 of the month e.g. 17 March 2001 it gets interpreted as 01-Mar-17

17 March 2000 worked fine using above methodology

Anyone out there encountered a similar problem or can help?

 

[Rich]

I've never had to convert to US format change your format back to short date and set the query criteria parameters to date/time.
HTH

 

[Marko Stojovic]

I'm a UK user too, and I've had a similar problem with dates. If I remember right, I think that if the day was less than 13, it interpreted this as the month instead!

At the moment I have a programme which takes the system date and puts it in a field. It used to malfunction but doesn't any more (last time I checked). I take the left 6 characters, insert "20", and add the right 2 characters. This works fine. I'm also on the UK calendar.

I came to the conclusion (whether right or wrong) that you must give SQL the year in 4-digit format, and normally it's passed around in 2-digit format. Is it possible that, although your user is prompted for 4 digits, what's actually passed to the query is a format with the year as only 2 digits? In this case, I'd suggest that you do something so that the SQL contains 4 digits (insert "20").

This works for me without necessarily using US date format... Good luck.

Marko.