Find records with date field before today - ASP / Active Server Pages

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

!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

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

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

!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

!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

!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

!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

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

!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

!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

> 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

!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

!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

Returning the Most Recent 2 records in a table based on Date Field

!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

!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

Similar topics

get/format date field

by: peashoe | last post by:

I have an asp page that uses a calendar.js (pop-up) file to add an exact date format in the text field (txtDDate). My problem is I need some javascript that sets an alert that does not allow them...

Javascript

by: Shaiguy | last post by:

I have a table containing the following fields: ProjectUpdateID (PrimaryKey) ProjectID UpdateDate I would like to create a Query in Ms Access 2000 which will return me the most recent 2...

Exporting Date Field

by: Fran Zablocki | last post by:

I have a process that exports an Access table to a comma-delimited text file. One of the fields that is exported shows the date it was exported, using the Date() function. In the Access table, the...

Find Records with Max Date

by: MostlyH2O | last post by:

Hi Folks, I have a query that joins 3 tables. One of the tables (SalaryData) has data where there may be duplicate records with different dates. Of those duplicate records, I want the query to...

phpmyadmin/MySQL - dynamic date field?

by: hph | last post by:

Please be gentle; I am a relative newbie for php/MySQL, and most of what I am doing is based on being self-taught using someone else's well-written code, and my own googling the answers to my...

PHP

compare a date field on form

by: sparks | last post by:

I am trying to find valid records in a table but I must compare a date field to a date stored in the table I can see where this is wrong (ok I guess its not text) "= """ & Me.date & """" so...

Converting date and time to just a date field

by: jacc14 | last post by:

Hi Hope there is someone out there that can help. I am sure this is an easy one although not easy to explain. I have a form which produces a report using a query. On the form I have a start and...

Query a date range using a date field from other table

by: nhkam | last post by:

I am using MS Access 2007 I have a transaction table which holds all records with posting date on each of them. I used a make table query to find out the max date hold in the transaction table and...

Cloud Servers without Credit Card and Email Registration: A Simpler Way to Get on the Cloud

by: CloudSolutions | last post by:

Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...

Access Europe: Command bars, the Access Shortcut Tool and a simple Audit Log - Wed 3 April

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

One-click Importing Excel Data into a*Database

by: ryjfgjl | last post by:

In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...

Microsoft Excel

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?