HELP! Date Criteria From Form

[Galen]

I am using ACCESS 2000 on Windows 2000 platform.

This sounds so simple but I can't get it to work.

I have a for named "frmControlPanel"
on this form is a textbox called "txtDate" where the user
eners a date value.

I have a table (tblActivity) of data with 2 fields DATE (a
date field) and ACTIVITY (a text field). There may be
several ACTIVITY records for each DATE value.


What I need a query to do is give me all records with a
date that falls between the date on frmControlPanel-31 and
the date on frmControlPanel-3.

I created a simple query referencing tblACTIVITY. It pulls
in both fields Date and Activity.

Here is what I wrote as a criteria under the DATE field on
my query to select the records I wanted:

=[forms]![frmControlPanel].[txtDate]-31 and <=[forms]!

[frmControlPanel].[txtDate]-3

When I run the query, I get a message saying that the
query is either incorrect or too complex to be evaluated.

FYI.. I made certain that the form was open and a valid
date was entered. I can even reference the date field in
another query just fine, so I don't think it is a problem
with the form.

I'm stumped!

 

[Guest]

I have had this happen, too.

What I found to fix is in your query, select Query,
Parameters from the toolbar.

List your same criteria for your field in the Parameter
sections and choose DATE/Time as Data Type.

I am not sure why this needs to be done, but it worked for
me.

Hope this works for you.

-----Original Message-----
I am using ACCESS 2000 on Windows 2000 platform.

This sounds so simple but I can't get it to work.

I have a for named "frmControlPanel"
on this form is a textbox called "txtDate" where the user
eners a date value.

I have a table (tblActivity) of data with 2 fields DATE (a
date field) and ACTIVITY (a text field). There may be
several ACTIVITY records for each DATE value.


What I need a query to do is give me all records with a
date that falls between the date on frmControlPanel-31 and
the date on frmControlPanel-3.

I created a simple query referencing tblACTIVITY. It pulls
in both fields Date and Activity.

Here is what I wrote as a criteria under the DATE field on
my query to select the records I wanted:

=[forms]![frmControlPanel].[txtDate]-31 and <=[forms]!

[frmControlPanel].[txtDate]-3

When I run the query, I get a message saying that the
query is either incorrect or too complex to be evaluated.

FYI.. I made certain that the form was open and a valid
date was entered. I can even reference the date field in
another query just fine, so I don't think it is a problem
with the form.

I'm stumped!
.

 

[fredg]

I am using ACCESS 2000 on Windows 2000 platform.

This sounds so simple but I can't get it to work.

I have a for named "frmControlPanel"
on this form is a textbox called "txtDate" where the user
eners a date value.

I have a table (tblActivity) of data with 2 fields DATE (a
date field) and ACTIVITY (a text field). There may be
several ACTIVITY records for each DATE value.

What I need a query to do is give me all records with a
date that falls between the date on frmControlPanel-31 and
the date on frmControlPanel-3.

I created a simple query referencing tblACTIVITY. It pulls
in both fields Date and Activity.

Here is what I wrote as a criteria under the DATE field on
my query to select the records I wanted:

=[forms]![frmControlPanel].[txtDate]-31 and <=[forms]!

[frmControlPanel].[txtDate]-3

When I run the query, I get a message saying that the
query is either incorrect or too complex to be evaluated.

FYI.. I made certain that the form was open and a valid
date was entered. I can even reference the date field in
another query just fine, so I don't think it is a problem
with the form.

I'm stumped!

Before you go any further with this, if you really do have a field
named 'Date' I would strongly suggest you change it to something else,
perhaps 'txtDate', SalesDate', etc.
Date is a reserved word in Access and VBA.
It should not be used as a field name.

Read the appropriate KnowledgeBase article for your version of Access.
109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

In Design View,
Click on Query + Parameter
Write
[forms]![frmControlPanel].[txtDate]
in the left panel, and
Date/Time
in the right panel.

But change that [Date] field first!!!!
Sooner or later you'll get a conflict.