Access SQL- Date comparison problem

[Bob the ASP Builder]

I use an Access-database where one column stores a date originating from
this code:

Indate = FormatDateTime(date(),vbgeneraldate)

I then want to be able to show postings from the database being i.e.
smaller than current date.
So I use the current date from a code same as the above code and want to
compare with dates in the database.
The column in the database is in Date/time format and a posting from
today will read 2004-01-31

In my process page code I do this

Dim Tday

Tday = FormatDateTime(date(),vbgeneraldate)

SQL = "SELECT * FROM Ads WHERE Indate<" & Tday & " ORDER BY ID"

Resulting in no postings found

Whereas this

SQL = "SELECT * FROM Ads WHERE Indate>" & Tday & " ORDER BY ID"

returns every post in the database whether larger or smaller doesnt
matter

It seems the data reading is interpreted differently to how it presents
itself.

How can I set the SQL-statement to compare the dates correctly?


Bob the ASP Builder

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Bob the ASP Builder wrote:

> I use an Access-database where one column stores a date originating
> from this code:
>
> Indate = FormatDateTime(date(),vbgeneraldate)

Totally unnecessary, but carry on.

>
> I then want to be able to show postings from the database being i.e.
> smaller than current date.
> So I use the current date from a code same as the above code and want
> to compare with dates in the database.
> The column in the database is in Date/time format and a posting from
> today will read 2004-01-31

You do realize that this has nothing to do with how the datetime value is
stored ... or do you?

Date/times in Access are stored as Double numbers, with the whole number
portion representing the number of days since the seed date, and the decimal
portion representing the time of day (.0 = midnight, .5 = noon)

>
> In my process page code I do this
>
> Dim Tday
>
> Tday = FormatDateTime(date(),vbgeneraldate)
>
> SQL = "SELECT * FROM Ads WHERE Indate<" & Tday & " ORDER BY ID"
>

Jet requires that date literals be delimited by hash marks (#):

SQL = "SELECT * FROM Ads WHERE Indate>#" & Tday & "# ORDER BY ID"

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob the ASP Builder]

THANKS HEAPS!!

Yes I realised the difference in stored value and presented value - but
my tutorial was too incomplete to teach me the #-keys - IT ALL WORKS now
- thank You

Bob the ASP Builder

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!