Find records with date field before today

[!TG]

I have a table with a date field.
All I want to do it get all the records with a date before today.
I tried the following:
"SELECT * FROM StateLicenses Where (Exp < #07/26/2005#) Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < 07/26/2005) Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < " & Now() & ") Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < #" & Now() & "#) Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < " & FormatDateTime(Now(),2) &
") Order By BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < #" & FormatDateTime(Now(),2) &
"#) Order By BranchNo,Satellite;"

and get errors such as:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.


The expected numbe is sometimes 1 and sometimes 3.
Please tell me what I am doing wrong.

 

[Aaron Bertrand [SQL Server MVP]]

sql = "SELECT * FROM StateLicences WHERE Exp < DATE()"

I assume Exp should have a better column name...

 

[Curt_C [MVP]]

I have a table with a date field.
All I want to do it get all the records with a date before today.
I tried the following:
"SELECT * FROM StateLicenses Where (Exp < #07/26/2005#) Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < 07/26/2005) Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < " & Now() & ") Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < #" & Now() & "#) Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < " & FormatDateTime(Now(),2) &
") Order By BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < #" & FormatDateTime(Now(),2) &
"#) Order By BranchNo,Satellite;"

and get errors such as:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.


The expected numbe is sometimes 1 and sometimes 3.
Please tell me what I am doing wrong.

"SELECT * FROM StateLicenses Where (Exp < '" & Now() & "') Order By
BranchNo,Satellite;"

 

[Aaron Bertrand [SQL Server MVP]]

"SELECT * FROM StateLicenses Where (Exp < '" & Now() & "') Order By

BranchNo,Satellite;"

Why do this outside of the database? Access knows what NOW() is. Plus, I
don't think single quote delimiters will work well, assuming Exp is a Date
column...

 

[Curt_C [MVP]]

Why do this outside of the database? Access knows what NOW() is. Plus, I
don't think single quote delimiters will work well, assuming Exp is a Date
column...

Till you said it I didn't realize it was Access. The reason I was
pushing it this way is that the DB may be in a different timezone then
the client, thinking the client wanted their time....guess it depends
what was used for the time when it was entered... hopefully it was
standardized with server time as you indicated though.....

 

[!TG]

Thanks for the replies.
EXP is type Date/Time in access database.
I also specified a format of short date after the problems began in a
wild stab at the dark.

I tried the below as recommended by Aaron
PSQL = "SELECT * FROM "
ThisTBL = PSQL & "StateLicenses Where (Exp < Date()) Order By
BranchNo,Satellite;"
PRS.Open ThisTBL, PDB
And got:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 2.

I tried the below as recommended by Curt_C
PSQL = "SELECT * FROM "
ThisTBL = PSQL & "StateLicenses Where (Exp < " & Now() & ")
Order By BranchNo,Satellite;"
PRS.Open ThisTBL, PDB
And got:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression '(Exp < 7/26/2005 2:48:25 PM)'.

Since that error was different I thought I might be on the right track
so I surrounded Now() with single quotes and pound signs, both of which
generated:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 2.

I tried the below as recommended by Aaron
PSQL = "SELECT * FROM "
ThisTBL = PSQL & "StateLicenses Where (Exp < Now()) Order By
BranchNo,Satellite;"
PRS.Open ThisTBL, PDB
And got:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 2.


I thought this query would be so simple and all the recommendations I
found on the internet were the same as yours...
I do appreciate the input, anything else?
Questions for me regarding my programming?

 

[Aaron Bertrand [SQL Server MVP]]

Is Exp a reserved word? Try the very first query but surround Exp with
[Exp]

Also try

set PRS = PDB.Execute(ThisTBL)

I also suggest using more standard names, they cause far less confusion.
Typically connection objects are named conn and recordset objects are named
rs or objRS. Just makes the code easier for others to follow...

A

 

[!TG]

sql = "SELECT * FROM StateLicences WHERE Exp < DATE()"

I assume Exp should have a better column name...

I have a table with a date field.
All I want to do it get all the records with a date before today.
I tried the following:
"SELECT * FROM StateLicenses Where (Exp < #07/26/2005#) Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < 07/26/2005) Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < " & Now() & ") Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < #" & Now() & "#) Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < " & FormatDateTime(Now(),2) & ")
Order By BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < #" & FormatDateTime(Now(),2) &
"#) Order By BranchNo,Satellite;"

and get errors such as:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.


The expected numbe is sometimes 1 and sometimes 3.
Please tell me what I am doing wrong.

Returned
[Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 2.

 

[!TG]

I have a table with a date field.
All I want to do it get all the records with a date before today.
I tried the following:
"SELECT * FROM StateLicenses Where (Exp < #07/26/2005#) Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < 07/26/2005) Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < " & Now() & ") Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < #" & Now() & "#) Order By
BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < " & FormatDateTime(Now(),2)
& ") Order By BranchNo,Satellite;"
"SELECT * FROM StateLicenses Where (Exp < #" & FormatDateTime(Now(),2)
& "#) Order By BranchNo,Satellite;"

and get errors such as:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.


The expected numbe is sometimes 1 and sometimes 3.
Please tell me what I am doing wrong.

"SELECT * FROM StateLicenses Where (Exp < '" & Now() & "') Order By
BranchNo,Satellite;"

That gave me
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression '(Exp < 7/26/2005 2:48:25 PM)'.

Since that error was different I thought I might be on the right track
so I surrounded Now() with single quotes and pound signs, both of which
generated:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 2.

 

[!TG]

Why do this outside of the database? Access knows what NOW() is. Plus, I
don't think single quote delimiters will work well, assuming Exp is a Date
column...

That gave me:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 2.

 

[!TG]

Is Exp a reserved word? Try the very first query but surround Exp with
[Exp]

Also try

set PRS = PDB.Execute(ThisTBL)

I also suggest using more standard names, they cause far less confusion.
Typically connection objects are named conn and recordset objects are named
rs or objRS. Just makes the code easier for others to follow...

Same effect on all of the above.
I have a query looking for an exact date that works just fine.

 

[Curt_C [MVP]]

Thanks for the replies.
EXP is type Date/Time in access database.
I also specified a format of short date after the problems began in a
wild stab at the dark.

I tried the below as recommended by Aaron
PSQL = "SELECT * FROM "
ThisTBL = PSQL & "StateLicenses Where (Exp < Date()) Order By
BranchNo,Satellite;"
PRS.Open ThisTBL, PDB

Response.Write ThisTBL

What is the exact code/text its generating?

 

[Aaron Bertrand [SQL Server MVP]]

Same effect on all of the above.

I have a query looking for an exact date that works just fine.

What version of Access? What version of MDAC is on your server? What does
your connection string look like?

Can you show the exact syntax for the query that works, and the code around
it?

A

 

[!TG]

Response.Write ThisTBL

What is the exact code/text its generating?

I already have it setup and it generates what you'd expect
like
SELECT * FROM StateLicenses Where [Exp] < #7/26/2005# Order By
BranchNo,Satellite;
or
SELECT * FROM StateLicenses Where Exp < #7/26/2005# Order By
BranchNo,Satellite;

 

[!TG]

What version of Access? What version of MDAC is on your server? What does
your connection string look like?

Can you show the exact syntax for the query that works, and the code around
it?

A

File is in 2000 format.
Access driver version on the server says: 4.00.6200.00
MDAC 2.8

The Query from the one that works looks like:
SELECT * FROM StateLicenses WHERE Exp=#2/27/1981#;

And the code:
Set PRS = Server.CreateObject("ADODB.Recordset")
ThisTBL = PSQL & "StateLicenses WHERE
Exp=#2/27/1981#;"
Response.Write(ThisTBL)
PRS.Open ThisTBL, PDB

Everything else below and above is identical

 

[Curt_C [MVP]]

Response.Write ThisTBL

What is the exact code/text its generating?

I already have it setup and it generates what you'd expect
like
SELECT * FROM StateLicenses Where [Exp] < #7/26/2005# Order By
BranchNo,Satellite;
or
SELECT * FROM StateLicenses Where Exp < #7/26/2005# Order By
BranchNo,Satellite;

Just for a test, take off the WHERE clause and ORDER BY clause, does it
error out still? Just trying to determine if the error is the statement
or not, it looks fine so I'm thinking there is something else.

 

[!TG]

!TG wrote:

Thanks for the replies.
EXP is type Date/Time in access database.
I also specified a format of short date after the problems began in
a wild stab at the dark.

I tried the below as recommended by Aaron
PSQL = "SELECT * FROM "
ThisTBL = PSQL & "StateLicenses Where (Exp < Date()) Order By
BranchNo,Satellite;"
PRS.Open ThisTBL, PDB





Response.Write ThisTBL

What is the exact code/text its generating?

I already have it setup and it generates what you'd expect
like
SELECT * FROM StateLicenses Where [Exp] < #7/26/2005# Order By
BranchNo,Satellite;
or
SELECT * FROM StateLicenses Where Exp < #7/26/2005# Order By
BranchNo,Satellite;

Just for a test, take off the WHERE clause and ORDER BY clause, does it
error out still? Just trying to determine if the error is the statement
or not, it looks fine so I'm thinking there is something else.

No errors in that instance.
I went ahead and built a function that does what I need. It's quite
slow, but at least it works. I'm boggled as to why the query won't work
and thanks for the help, but you don't have to offer any more advice.

I do appreciate the attempt.