«

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.

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

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



"!TG" <> wrote in message
news:%...
>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.

!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

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

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

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?

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" <> wrote in message
news:ul$...
> 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] wrote:
> sql = "SELECT * FROM StateLicences WHERE Exp < DATE()"
>
> I assume Exp should have a better column name...
>
>
>
> "!TG" <> wrote in message
> news:%...
>
>>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.

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.

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.