Date field in DB

[Stuart Palmer]

I am trying to compare the current date() with the date in an access db to
pull out only records where the date is later than the todays date (so
records where the date hasn't passed)

Then I do this:-

strSQL = "select * from tblEvents where enddate > " & date() & " order by
startdate"

it pulls me out every record even ones with the dat in the past.

When I response.write the date() and objRS("enddate") to the screen the
dates are correct/same format and the date() is older than the
objRS("enddate") . So why is it pulling it out of the DB.

I know a way to do it would be to pull all records out and do an if
statement, but if I have, for example, 100 events 98 of which were expired,
then it would be a waste of processing time to do the if and loop when I
could pull them out from the DB from the start.

Any ideas how I can et round this please?

Cheers for anyones help.

Stu

 

[Ray at]

If you did a:

response.write strSQL
response.End

You'd probably get:

select * from tblEvents where enddate > 7/31/2003 order by startdate

So, what does that say? It says:

Select * from tblEvents where EndDate is greater than (7 divided by 31
divided by 2003).
(At least that's what I believe is happening.)

In Access, you need to delimit dates with the # sign, I believe. So, try:

strSQL = "select * from tblEvents where enddate > #" & date() & "# order by
startdate"

Alternatively, you can use DATE() in your SQL query itself:

Ray at work

 

[Andrew J Durstewitz]

Good observation Ray. I don't think I would have caught that... )

-Andrew

If you did a:

response.write strSQL
response.End

You'd probably get:

select * from tblEvents where enddate > 7/31/2003 >order by startdate

So, what does that say? It says:

Select * from tblEvents where EndDate is greater >than (7 divided by 31
divided by 2003).
(At least that's what I believe is happening.)

In Access, you need to delimit dates with the # >sign, I believe. So, try:

strSQL = "select * from tblEvents where enddate > >#" & date() & "# order by
startdate"

Alternatively, you can use DATE() in your SQL >query itself:

Ray at work

* * * Sent via DevBuilder http://www.devbuilder.org * * *
Developer Resources for High End Developers.

 

[Ray at]

Getting screwed by something one time is to enough to make you remember. ;]

Ray at work

 

[Bob Barrows]

In addition to Ray's suggestion, you can do this, instead:
strSQL = "select * from tblEvents where enddate > date()" & _
" order by startdate"

JetSQL can a use the VBA date() function "internally". There is no need to
pass the current date to the query.

HTH,
Bob Barrows

PS. You should avoid "Select *" (selstar for short) in production code since
it virtually guarantees that you will be pulling unnecessary data across the
wire. Explicitly list the desired fields in your select statement.

 

[Ray at]

Hey, that was my second suggestion, man!

Ray at work

 

[Bob Barrows]

Oh yeah!! Come on - reread your message: that was easy to miss wasn't it?