SQL date comparision

[tkm]

Hello Mates,
I am trying to do a date comparision in access with the following SQL.

select My_Interest_Fld,Title_Fld,First_Name_Fld,Last_Name_Fld,Dt_Tm_Fld from fbs_tbl where  Dt_Tm_Fld BETWEEN #01/04/200# AND #31/04/2009#

I am getting syntax error message. I at the moment don't have any clue on what is wrong with the syntax. Could anyone kindly help me with the syntax error please? Thank you.

 

[Rabbie]

This may be a typo but

BETWEEN #01/04/200# AND #31/04/2009#

is probaly in need of correction.

I have just tested something very similar and that worked fine. Try putting a ; at the end of your SQL string.

 

[tkm]

This may be a typo but is probaly in need of correction.

I have just tested something very similar and that worked fine. Try putting a ; at the end of your SQL string.

Thankx Rabbie for your reply. I sorted the typo and added a ; at the end, but still no success. My database column type is date/time and short date although I believe it has nothing to do with syntax error. It is making me mad...

 

[Rabbie]

Thankx Rabbie for your reply. I sorted the typo and added a ; at the end, but still no success. My database column type is date/time and short date although I believe it has nothing to do with syntax error. It is making me mad...

This is the SQL that worked for me

SELECT Agent, TotalSessions, fileDate
FROM AgentSessions
WHERE (((fileDate) Between #1/1/2009# And #31/1/2009#));

If you are still having problems try using the query design grid to build your query then you can look at the SQL to see what it has done.

 

[tkm]

This is the SQL that worked for me

SELECT Agent, TotalSessions, fileDate
FROM AgentSessions
WHERE (((fileDate) Between #1/1/2009# And #31/1/2009#));

If you are still having problems try using the query design grid to build your query then you can look at the SQL to see what it has done.

Right, let me work with design grid. I must be missing something. Thankx a lot.

 

[raskew]

#31/04/2009#

will cause an Invalid Date Value error (e.g. there are only 30 days in April)

Bob

 

[Rabbie]

will cause an Invalid Date Value error (e.g. there are only 30 days in April)

Bob

Well spotted

 

[Brianwarnock]

I always thought that in SQL you had to use American format.
#04/30/2009#

Brian

 

[Rabbie]

I always thought that in SQL you had to use American format.
#04/30/2009#

Brian

The query design grid seems to be able to convert it from UK format to US format if you get it wrong if the day field is greater than 12.

But yes you are correct it will interpret 1/2/2009 as 2nd january rather than 1st Feb

 

[Brianwarnock]

Ah! I get it , you can code it in UK format as long as it cannot be misinterpreted, so

#30/04/2009# is ok but
#01/04/2009# is not if you mean 1st April as it will give 4th of Jan.

Obvious really.

Brian

 

[tkm]

Ah! I get it , you can code it in UK format as long as it cannot be misinterpreted, so

#30/04/2009# is ok but
#01/04/2009# is not if you mean 1st April as it will give 4th of Jan.

Obvious really.

Brian

Hi Mates,
Thank you all for looking into it and providing the suggestions. The only way I can make it work is change my date field to text field in database and using the following query:

SELECT fbs_tbl.First_Name_Fld, fbs_tbl.Dt_Tm_Fld
FROM fbs_tbl
WHERE (((fbs_tbl.Dt_Tm_Fld) Between '04/01/2009' And '04/02/2009'));

I will stick with this strategy for the time being and later come back to see what is going wrong. Thank you again....

 

[Rabbie]

Happy to help

But be careful. Text dates do not always sort as you expect

 

[WIS]

Ah! I get it , you can code it in UK format as long as it cannot be misinterpreted, so

#30/04/2009# is ok but
#01/04/2009# is not if you mean 1st April as it will give 4th of Jan.

Obvious really.

Brian

Brian

Best way is to use the Format fn:

eg = Format(varDateBooking, "dd-mmm-yyyy")

Notice the 3 m's - this will force any date to UK date format - but if you use 2m's it doesn't seem to.

 

[namliam]

I will stick with this strategy for the time being and later come back to see what is going wrong. Thank you again....

No, please dont do this, this will cause you MUNDO problems in the future. Better to take the hit now of changing this text column to a date column before you run into yet more issues...

#30/04/2009# is ok but
#01/04/2009# is not if you mean 1st April as it will give 4th of Jan.

NOOOOOOOOOOOOOOOOOOOO!

If anything NEVER mess with your date formats and NEVER mix them. You WILL F-up and get horendous results. As well as make your coding IMPOSSIBLE to support by you and others long term. Though it will work, please dont do this!

Doing something with Dateserial or Format is the trick, both work equally well. But make sure to ALWAYS have a proper date in your SQL

 

[Brianwarnock]

Don't panic Namliam I never mix the formats, I always use UK in design grid and American in VBA and SQL, that's why I didn't know about the quirk.

Brian