INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

date variable in SQL - driving me mad!

date variable in SQL - driving me mad!

(OP)
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

 

RE: date variable in SQL - driving me mad!


Quote:

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

Quote:

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: date variable in SQL - driving me mad!

(OP)
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

 

RE: date variable in SQL - driving me mad!

(OP)
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") & "#)" & _

RE: date variable in SQL - driving me mad!



Quote:

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: date variable in SQL - driving me mad!

"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

RE: date variable in SQL - driving me mad!

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

 

RE: date variable in SQL - driving me mad!

>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.

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close