date variable in SQL - driving me mad!

[orangeseatbelt]

ok, I've got a straightforward SQL statment with a where clause

The where clause is as follows:

"(r_date > #" & Startdate & "#) and " & _
"(r_date < #" & db_valid.Fields("r_date") & "#) and " & _

StartDate contains 12/09/2010
and db_valid.Fields("r_date") contains 19/09/2010

the statement works but it's showing dates that are BEFORE the startDate - it just seems to be ignoring the first date check and only using the second. If I put the date I KNOW is in startDate into the sql string it works perfectly well and I get the results I should!

I've tried declaring StartDate as a date, as a string. I've tried converting it with cDate before I use it, I've checked that it is a date with isDate.... I've replaced the # with single quotes, I've removed the #...

What the hell is the problem!? driving me to distraction

 

[SkipVought]

StartDate contains 12/09/2010

GREAT! so that means, to Bill Gates and Co, that #StartDate# converts to December 9 2010.

and db_valid.Fields("r_date") contains 19/09/2010

GREAT! so that means, to Bill Gates and Co, that #db_valid.Fields("r_date")# converts to TILT!!!! There's no month 19!!!!!!

You would be better off structuring your dates as yyyy/mm/dd.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[orangeseatbelt]

yessss I know there's no month 19. I'm British, my time zones are set British, my dates are set to British - that's why when I run isDate() against startDate it doesn't return an error because the format is dd/mm/yy. Ditto if I run day(startDate), or month(STartDate) - it returns the correct results.

and before anyone says that's the error - it's not. Because if I ammend my where statement to read:

"(r_date > #12/09/2010#) and " & _
"(r_date < #" & db_valid.Fields("r_date") & "#) and " & _

it returns the correct results. The problem is that SQL won't accept the StartDate var. Does anyone know why?

thanks

 

[orangeseatbelt]

finally...

Ok, so even though r_date is held in dd/mm/yy format, that is automatically converted to the required MM/dd/yy format... only that same conversion isn't applied to the startDate var. WHY!?

If I do the conversion - as below - then all is fine.

"(r_date > #" & Month(startdate) & "/" & Day(startdate) & "/" & Year(startdate) & "#) and " & _
"(r_date < #" & db_validRaceIDs.Fields("r_date") & "#)" & _

 

[SkipVought]

my time zones are set British

Does NOT matter!!!

Mr Gates is in Washington, USA.

Use an UNAMBIGUOUS date string structure!

dd/mm/yyyy is NOT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[George1111]

You would be better off structuring your dates as yyyy/mm/dd."

This is guaranteed to work

I believe the Date format for SQL Date Range Queries needs to be in USA Format (#MM/DD/YYYY#) Even if you are in UK, Australia, etc

Also the select Operation needs to use BETWEEN

Something like

..... BETWEEN #" & DateFrom & "# AND #" & DateTo

DateFrom and DateTo are in USA Format MM/DD/YYYY

 

[dpaulson]

We have the same problem in Canada also. This is how I would write it when I do anything with a database.

"(r_date > #" & Format(Startdate, "yyyy-MM-dd") & "#) and " & _


David Paulson

 

[strongm]

>r_date is held in dd/mm/yy format

It isn't. In Access, it is held in an unambiguous date format, a double representing the number of whole days since 30th Dec 1899, with the fractional part representing a decimal fraction of a day (i.e. the time).

Access by default displays this according to your regional settings. But display and internal representation are two different things.