Date Query - Access 07

[Maggic]

I have a field "DateEntered" defined as a "date/time" data type and want to
prompt for input criteria to extract data based on this field.

Basically I want the user to be able to just enter an "offset" for which
data is selected. For example ... I can run a query with the selection
criteria hard coded to "date()-1" to select yesterday's data, but when I
change the selection criteria to "date()&[Enter offset]" to prompt for the
offset, I have a problem. It prompts for the offset, but then gives the
error "Data type mismatch in criteria expression". Access seems to check
the parameter input before appending it to the partial criteria already
there i.e. I think it is telling me that I must enter a "proper" date as a
parameter.

Is there a way round this? Some help would be much appreciated by this
Access newbie .. thanks in advance ...

Maggic

 

[Fredg]

I have a field "DateEntered" defined as a "date/time" data type and want to
prompt for input criteria to extract data based on this field.

Basically I want the user to be able to just enter an "offset" for which
data is selected. For example ... I can run a query with the selection
criteria hard coded to "date()-1" to select yesterday's data, but when I
change the selection criteria to "date()&[Enter offset]" to prompt for the
offset, I have a problem. It prompts for the offset, but then gives the
error "Data type mismatch in criteria expression". Access seems to check
the parameter input before appending it to the partial criteria already
there i.e. I think it is telling me that I must enter a "proper" date as a
parameter.

Is there a way round this? Some help would be much appreciated by this
Access newbie .. thanks in advance ...

Maggic

Try
Date()-[How many days?]

Enter 7 to get exactly Date()-7
Use the > to get all records newer than that number of days.

Date()-[How many days?]

 

[Maggic]

Thanks to both Wayne and Fred for your replies.. ... of course both your
suggestions work perfectly

Really appreciate the help.

Maggic

I tried this to make sure:

Date()-[Enter Offset]

--
Wayne Morgan
Microsoft Access MVP

I have a field "DateEntered" defined as a "date/time" data type and want to
prompt for input criteria to extract data based on this field.

Basically I want the user to be able to just enter an "offset" for which
data is selected. For example ... I can run a query with the selection
criteria hard coded to "date()-1" to select yesterday's data, but when I
change the selection criteria to "date()&[Enter offset]" to prompt for the
offset, I have a problem. It prompts for the offset, but then gives the
error "Data type mismatch in criteria expression". Access seems to check
the parameter input before appending it to the partial criteria already
there i.e. I think it is telling me that I must enter a "proper" date as a
parameter.

Is there a way round this? Some help would be much appreciated by this
Access newbie .. thanks in advance ...

Maggic