Display records between dates?

[Targa]

How can I display records from an Access db that match the dates stored in
the record?

Ex: I have a database where the user enters auction info and the dates that
they what the auction to be displayed between (ie DisplayStartDate &
DisplayEndDate)

Should be something like this but I cant seem to get the syntax correct.
Also not sure how to reference TODAYSDATE in the query.

SELECT * FROM AUCTIONS WHERE ':isplayStartDate::' >= TODAYSDATE? AND
':isplayEndDate::' <= TODAYSDATE?)

Maybe Im going in to totally wrong direction.

Thanks for any help.

FP2002, IIS5, ACCESS, Date fields set to Short Date

Mike

 

[jon spivey]

Hi,
something like this
SELECT * FROM AUCTIONS
WHERE #:isplayStartDate::# BETWEEN Date() AND #:isplayEndDate::#

 

[Targa]

Wow - Thanks for the quick reply!

Still getting the following error:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in date
in query expression '## BETWEEN Date() AND ##'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default values for
all form fields that are used in the query.

Looks like its expecting values from a form but Im just loading the page
directly.
Any ideas where Im going wrong?

Thanks!

 

[Targa]

Hi Jon,
Sorry - I think I may not have explained this correctly.
The DisplayStart Date and DisplayEnd Date fields are actually in the db
record.

So the query needs to look at these fields, compare them to the current date
and display accordingly.

Thanks!
Mike

Wow - Thanks for the quick reply!

Still getting the following error:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in date
in query expression '## BETWEEN Date() AND ##'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default values for
all form fields that are used in the query.

Looks like its expecting values from a form but Im just loading the page
directly.
Any ideas where Im going wrong?

Thanks!

 

[jon spivey]

Now I've got it

SELECT * FROM Table
WHERE Date() BETWEEN DisplayStartDate AND DisplayEndDate

Jon

Hi Jon,
Sorry - I think I may not have explained this correctly.
The DisplayStart Date and DisplayEnd Date fields are actually in the
db record.

So the query needs to look at these fields, compare them to the
current date and display accordingly.

Thanks!
Mike

Wow - Thanks for the quick reply!

Still getting the following error:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error
in date in query expression '## BETWEEN Date() AND ##'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default
values for all form fields that are used in the query.

Looks like its expecting values from a form but Im just loading the
page directly.
Any ideas where Im going wrong?

Thanks!

Hi,
something like this
SELECT * FROM AUCTIONS
WHERE #:isplayStartDate::# BETWEEN Date() AND #:isplayEndDate::#

--
Jon
Microsoft MVP - FP

Targa wrote:
How can I display records from an Access db that match the dates
stored in the record?

Ex: I have a database where the user enters auction info and the
dates that they what the auction to be displayed between (ie
DisplayStartDate & DisplayEndDate)

Should be something like this but I cant seem to get the syntax
correct. Also not sure how to reference TODAYSDATE in the query.

SELECT * FROM AUCTIONS WHERE ':isplayStartDate::' >= TODAYSDATE?
AND ':isplayEndDate::' <= TODAYSDATE?)

Maybe Im going in to totally wrong direction.

Thanks for any help.

FP2002, IIS5, ACCESS, Date fields set to Short Date

Mike

 

[Targa]

YOU DA MAN!

Thanks!

Now I've got it

SELECT * FROM Table
WHERE Date() BETWEEN DisplayStartDate AND DisplayEndDate

Jon

Hi Jon,
Sorry - I think I may not have explained this correctly.
The DisplayStart Date and DisplayEnd Date fields are actually in the
db record.

So the query needs to look at these fields, compare them to the
current date and display accordingly.

Thanks!
Mike

Wow - Thanks for the quick reply!

Still getting the following error:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error
in date in query expression '## BETWEEN Date() AND ##'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default
values for all form fields that are used in the query.

Looks like its expecting values from a form but Im just loading the
page directly.
Any ideas where Im going wrong?

Thanks!


Hi,
something like this
SELECT * FROM AUCTIONS
WHERE #:isplayStartDate::# BETWEEN Date() AND #:isplayEndDate::#

--
Jon
Microsoft MVP - FP

Targa wrote:
How can I display records from an Access db that match the dates
stored in the record?

Ex: I have a database where the user enters auction info and the
dates that they what the auction to be displayed between (ie
DisplayStartDate & DisplayEndDate)

Should be something like this but I cant seem to get the syntax
correct. Also not sure how to reference TODAYSDATE in the query.

SELECT * FROM AUCTIONS WHERE ':isplayStartDate::' >= TODAYSDATE?
AND ':isplayEndDate::' <= TODAYSDATE?)

Maybe Im going in to totally wrong direction.

Thanks for any help.

FP2002, IIS5, ACCESS, Date fields set to Short Date

Mike