Cannot get Between...and criteria to run

[shelter]

I am running Access 2003. In the query criteria under the DateReceived
field, I entered Between [Enter the beginning date:] And Date(). I get
the following error message: The Microsoft Jet Database Engine does not
recognize '[enter the beginning date:]' as a valid field name or
expression.

I am trying to return records between a date the user enters and today.
This is really odd because the above expression works fine in Access97.
What am I missing here?

Thanks.

 

[Jeff Boyce]

You may need to select the <Query> menu item and <Parameters> and enter
(exactly - copy/paste works well) the prompt and data type there.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michel Walsh]

Hi,

You can also try to add a CDate around the parameter:

BETWEEN CDate( [Enter the beginning date:] ) AND Date()


If you run the query from the user interface, you should be prompted for the
parameter.


*If* you run the query from your code, you will get an error since your VBA
code does not prompt the end user but assumes the parameter are already
supplied (if you use CurrentDb). In other words, if you have a saved query
with parameters, you can use something like:

Dim q as queryDef : Set q=CurrentDb.Querydefs("savedQueryNameHere")
Dim param as DAO.Parameters
For each param in q.Parameters
param.Value = ... ' supply the value for 'each' parameter
Next param
Dim rst As DAO.Recordset
Set rst=q.OpenRecordset( ... options here ... )






Hoping it may help,
Vanderghast, Access MVP