Limiting Search to last 30 Days

[Elvaar]

Two questions...

First off the simple one: I', trying to create a query that will return all records from the last 30 calendar days. Someing like:

Between Date() And Date() - 30

I know the syntax doesn't work that way, but thats the general idea of what i'm trying to do.


Secondly:

In another query im trying to limit records by a datatype from 1 field. In that field(which is linked to a table through a combo box) there are approximately 100 different selections. I want to be able to have the user select from that list of data more than one selection.

In other words, limit the records by say 5 differnt possibilities all from 1 field. Now what i have done since these selections all remain constant is build a Form with a list box so the user can highlight the selections he wants the records limited to(this works great with a combo box if you link the result of the combo box to the criteria section in your query)

My problem however us that how can you link this to a query if the user is making multiple selections from a list box,


Thanks in Advance

Bill

 

[Jon K]

First question:
If what you want is the past 30 days (i.e. excluding today), the criteria is:-
between Date()-30 and Date()-1
--------------------------

Second question:
To use a multiple-selection list box for the criteria, you must use VBA code.

The attached demo DB shows how to use a list box on a form for the user to select multiple data types. The list box is populated from the text field DataType. When the Retrieve button is clicked, a query qryDataType is created and run.

The code is contained in the On Click Event of the command button.

It was written in Access 97 so DAO was used. If you write the code in Access 2000, you must make a reference to the Microsoft DAO 3.6 Object Library (when the code window is open, choose menu Tools, References...)

Hope you can adapt it to suit your needs.


Note:-

If a list box is populated from a numeric field, the line to loop through the list box selections is:-
sCriteria = sCriteria & "," & lstDataType.ItemData(varItem)

If a list box is populated from a date/time field, the line is:-
sCriteria = sCriteria & ",#" & lstDataType.ItemData(varItem) & "#"