By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,950 Members | 1,150 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,950 IT Pros & Developers. It's quick & easy.

Find records with date field before today

P: n/a
!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.
Jul 26 '05 #1
Share this Question
Share on Google+
16 Replies


P: n/a
sql = "SELECT * FROM StateLicences WHERE Exp < DATE()"

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

"!TG" <27********@southwestfunding.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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.

Jul 26 '05 #2

P: n/a
!TG wrote:
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;"

--
Curt Christianson
site: http://www.darkfalz.com
blog: http://blog.darkfalz.com
Jul 26 '05 #3

P: n/a
> "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...
Jul 26 '05 #4

P: n/a
Aaron Bertrand [SQL Server MVP] wrote:
"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...


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

--
Curt Christianson
site: http://www.darkfalz.com
blog: http://blog.darkfalz.com
Jul 26 '05 #5

P: n/a
!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?
Jul 26 '05 #6

P: n/a
!TG
Aaron Bertrand [SQL Server MVP] wrote:
sql = "SELECT * FROM StateLicences WHERE Exp < DATE()"

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

"!TG" <27********@southwestfunding.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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.
Jul 26 '05 #7

P: n/a
!TG
Curt_C [MVP] wrote:
!TG wrote:
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.
Jul 26 '05 #8

P: n/a
!TG
Aaron Bertrand [SQL Server MVP] wrote:
"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...

That gave me:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 2.
Jul 26 '05 #9

P: n/a
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" <27********@southwestfunding.com> wrote in message
news:ul**************@tk2msftngp13.phx.gbl...
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?

Jul 26 '05 #10

P: n/a
!TG
Aaron Bertrand [SQL Server MVP] wrote:
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.
Jul 26 '05 #11

P: n/a
!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?
--
Curt Christianson
site: http://www.darkfalz.com
blog: http://blog.darkfalz.com
Jul 26 '05 #12

P: n/a
> 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
Jul 26 '05 #13

P: n/a
!TG
Curt_C [MVP] wrote:
!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;
Jul 26 '05 #14

P: n/a
!TG
Aaron Bertrand [SQL Server MVP] wrote:
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


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
Jul 26 '05 #15

P: n/a
!TG wrote:
Curt_C [MVP] wrote:
!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.

--
Curt Christianson
site: http://www.darkfalz.com
blog: http://blog.darkfalz.com
Jul 26 '05 #16

P: n/a
!TG
Curt_C [MVP] wrote:
!TG wrote:
Curt_C [MVP] wrote:
!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.

Jul 26 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.