SQL statement won't filter out dates?!

[raggajunglist]

Hi all, thought i had finished this one but apparantly not yet

Firstly can anyone see a problem with this statement?

SELECT Final.* INTO NewTable FROM Final WHERE LoginDate <> #02/07/2007#

It works fine with non date/time values..

It's probably something silly i know but any help is appreciated...

Thanks

Mike

 

[Fear Naught]

Firstly you need to ensure that the SQL is using the US date format. In your example the date will be 7 Feb 2007 (is that the date you really want). Also ensure that there is any data that meets that criteria to filter out. Also is your table showing just the date or does it include the time. If so then your filter will fail and you will need to find another method.

Hope this helps

 

[chergh]

Try:

SELECT Final.* INTO NewTable FROM Final WHERE CDate(LoginDate) <> #02/07/2007#

 

[raggajunglist]

FearNaught: I thought i was just matching what value was in the table so the query pulls the correct date?
In the table the data is just a date for logindate but there is also logintime and a logoutdate & time.

Chergh: If i use CDate it gives me a type mismatch error.

*********************************************************
On further inspection it gets even worse!
When i choose

1.LoginDate = #17/08/07# the query works fine, no probs there.

2.LoginDate <> #17/08/07 the query works fine.

!! HOWEVER !!

3.LoginDate = #02/02/07# no results
4.LoginDate <> #02/02/07 - brings through everything...

Also this is the same with any date beginning with a #0.....
Anyone have any ideas? I guess i could just set a custom format for those date but thats a bit annoying..

Thanks for reading

Mike

 

[chergh]

Try:

SELECT Final.* INTO NewTable FROM Final WHERE CDate(FORMAT(LoginDate,"dd/mm/yy")) <> #02/07/2007#

 

[neileg]

Two things you should know when using SQL with dates.
1) SQL is a US invention. It only works with dates in US format
2) Dates in Access are stored as decimal numbers. This is translated into a date by Access when the data is retrieved

I trend to use a different date format, 17-Aug-2007, which is unambiguous and mostly avoids any US/UK date confusion.

 

[raggajunglist]

Thanks for all the replies guys!!
Much appreciated

It works with no probs with the new formatting.
Can't say it's as nice to read but hey I won't even have to once it's finished so it's all good!

Thanks for the heads up on the SQL American formatting as well, definately useful to know!

Take it ezz

 

[raggajunglist]

omg, cannot believe it.
changed all the formatting to short date (17-Jul-07)
ALL dates are now in this format.

When i select the values it even comes up as 17-Jul-07 however when i build the SQL statement it comes through with 17/07/07 format and doesnt work!?

Why would this happen? I've set input masks so the values only display in short date format but still it changes it in the sql statement...

If more details are needed let me know

Thanks for reading

Mike

 

[ajetrumpet]

Mike, if you're not about to quit at this point, I have a few questions (they might be off topic, but they might lead to something)...

1) Are you trying to execute a make-table query based on criteria that is selected from the 3 form combo boxes??

2) Are you querying (with your form) a QUERY or a source table??

Explain more in depth what you mean by this....

When i select the values it even comes up as 17-Jul-07 however when i build the SQL statement it comes through with 17/07/07 format and doesnt work!?

I did not quite follow it....

SHORT DATE = mm/dd/yyyy

MEDIUM DATE = dd/month/yy

 

[raggajunglist]

quit? me? never

had to format the date inside the SQL statement and it works like a charm...

thanks for the reply but sorry to waste your time
Thanks

Mike

 

[Dennisk]

try formatting your date FORMAT(Yourdate,"mm/dd/yyyy")

If I use embedded sql in code and I want to compare to a date then I have to convert from English to USA format.