SQL Date problems

[Carpie]

I have a form (search.asp) that passes a date to a date query in results.asp. Below is my SQL statement:

SELECT * FROM Savings WHERE [Sdate] >= #::idate::#

Note that everything works fine if I substitute a date (1/1/04) into the SQL statement in place of ::idate::. With ::idate:: in the SQL statement, I can't leave the Custom Query window as it considers it an error. The details are (notice it references #1#?):

Server error: Unable to retrieve schema information from the query:

SELECT * FROM Savings WHERE [Sdate] >= #1#

against a database using the connection string

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/BSCPfp.mdb.

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.

 

[Kevin Spencer]

Hi Carpie,

According to your error message, the value of "idate" - which would be
referring to a form field or QueryString parameter - is "1" - which is not a
date value:

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query

expression '[Sdate] >= #1#'.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

I have a form (search.asp) that passes a date to a date query in

results.asp. Below is my SQL statement:

SELECT * FROM Savings WHERE [Sdate] >= #::idate::#

Note that everything works fine if I substitute a date (1/1/04) into the

SQL statement in place of ::idate::. With ::idate:: in the SQL statement, I
can't leave the Custom Query window as it considers it an error. The
details are (notice it references #1#?):

Server error: Unable to retrieve schema information from the query:

SELECT * FROM Savings WHERE [Sdate] >= #1#

against a database using the connection string

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/BSCPfp.mdb.

The following error message comes from the database driver software; it

may appear in a different language depending on how the driver is
configured.

-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '[Sdate] >= #1#'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)

 

[Carpie]

I agree but... how does it determine ANY value before the form is filled out? I'm just trying to design the results page to accept the #idate# (I swear I'll put a date into the field once I fill it out!).

The field properties on the search form do not have any pre determined value placed in it. In fact, just for kicks and grins I put 1/1/03 into as a default value but still get the #1# in the results.asp query.

 

[Kevin Spencer]

I agree but... how does it determine ANY value before the form is filled
out? I'm just trying to design the results page to accept the #idate# (I
swear I'll put a date into the field once I fill it out!).

I have no idea, Carpie. I'm not looking at your code, only hearing what you
have to say about it, and looking at the data you have given us, which
indicates that a form field or QueryString value named "idate" has a value
of "1" which is being inserted into your query.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

I agree but... how does it determine ANY value before the form is filled

out? I'm just trying to design the results page to accept the #idate# (I
swear I'll put a date into the field once I fill it out!).

The field properties on the search form do not have any pre determined

value placed in it. In fact, just for kicks and grins I put 1/1/03 into as
a default value but still get the #1# in the results.asp query.