Querying all jobs between two dates

P

PaulHilgeman

I have a table that contains, among many, two fields, one for start
install date (StartInstallDate), one for end install date
(EndInstallDate).

How can I have a query that has a parameter DATE, that will display all
jobs occouring when DATE is Between StartInstallDate and
EndInstallDate.

Thanks,
Paul Hilgeman
 
J

Jeff Boyce

Paul

The "Between ... And ..." expression works when you are working with a
single field. In your case, you have two.

One approach would be to select both fields in your query, then use the
following selection criteria:

for your [StartInstallDate], use "<=Date()" (leave out the quotes and
this parenthetical remark)
for your [EndInstallDate], use ">=Date()" (leave out ...)

This would find records for which the [StartInstallDate] is the current date
or before, and the [EndInstallDate] falls on or after the current date.

Note that this will "crash" if your data is missing a date!

Regards

Jeff Boyce
<Office/Access MVP>
 
R

Rick B

An almost identical question was asked yesterday. Did you try searching
before posting a new thread?


It is in the queries newsgroup and the topic was "How do I make a query?"
The poster was "Jinny".
 
J

John Spencer

One method is to put the parameter in as a field and then use the fields as
criteria.

Field: [What Date?]
Criteria: BETWEEN [YourTableName].[StartInstallDate] and
[YourTableName].[EndInstallDate]

The problem with that is speed if you have a large number of records. Any
index on the fields will not be used (at least as far as I know).
 
P

PaulHilgeman

This worked perfectly,

I didnt realize that there was an AND relationship between columns.

Thanks!!!
 
Top