VB - Access date querying is messed up!

[oka97]

Hi, have a look at this:

SELECT id FROM client WHERE existing_exists='no' AND (personal_date BETWEEN #02/06/2003# AND #02/06/2003#)

This comes up with nothing even tho there is an entry with 02/06/2003 in it!

If I push 02 to 06 in the query string, it picks it up, I'm confused!

I've set text fields to the date format, I've set Access date variables to short date format, nothing is helping!

 

[Genimuse]

It sounds like a date format internationalization problem... when you say "02/06" are you referring to February 6th or June 2nd?

 

[oka97]

Hi, short date formate in Access implies dd/mm/yyyy

 

[oka97]

I changed all my date fields in the database to dd-mmm-yy and it fixed the problem. Access sux

 

[CCLINT]

>I changed all my date fields in the database to dd-mmm-yy and it fixed the problem. Access sux

No, this isn't an ACCESS problem...it is a programming error on the part of the one who wrote this SQL statement, and failure of knowing how to work with dates, and not getting the proper instructions on how to do so.

Changing the fields in the db is certainly not the way to correct the problem either, as you are only changing the symptoms.

Pass the date in US format.

This means you MUST be working with hard coded dates in your code in US format, and for user input, using Date conversion functions and date variables to store dates, and if the latter, format the date to US format when passing the date to something like a dbms.

Otherwise, as already mentioned, how is the system supposed to know what date 10/11/12 really means?

So, by changing the datadase, latter when you change to another database, what is supposed to happen?
And what is supposed to happen when the dbms is on a server and one client has their system set in US format and another in non-US Format?

 

[oka97]

CCLINT, I format all dates in dd/mm/yy format.

When I queried dates which "could" be mistaken for mm/dd/yy it did, for dates that couldn't be mistaken eg. 13/12/99, it was fine.

Anyway I don't trust Access, I'm sticking to dd-mmm-yy. It once confused two text fields on an Access Form because they were so similarly named!

 

[CCLINT]

1. dd-mmm-yy will NOT work on systems set for a Country that is non-english when passing a literal date in a SQL statement.
Why? Because of the month name being spellt different in other languages....

2. The date was mis-understood because you didn't pass them right, as I said.

Assumming your system is set as dd/mm/yyyy:
The date in the database shows 02/06/2003 because when opening the table in ACCESS, ACCESS formats it to the system format, even though it is stored in the US format
So you need to pass the date as 06/02/2003.

So, entering a date in a text box needs to be entered in the same format as the system is set as.
Then you need to format the date correctly:

SqlDate = Format$(TheDate, "mm\/dd\/yyyy&quot

or

SqlDate = Format$(TheDate, "mm-dd-yyyy&quot

HOWEVER, if your users are entering a date as mm/dd/yyyy and their system is set as dd/mm/yyyy, then of course it will not work.


Now, with your change, you are passing the literal month name which can be interpeted....but only on systems set to a country with English language.
This will fail on other systems as mentioned in #1.
Which also shows that it is being passed wrong.