Querying all jobs between two dates

  • Thread starter Thread starter PaulHilgeman
  • Start date Start date
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
 
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>
 
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".
 
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).
 
This worked perfectly,

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

Thanks!!!
 
Back
Top