using a query to select records

[Cheryl - MO]

I have a vehicle maintenance database. I have created a
query that contains the following fields:

Date of Service, Odometer reading at service, description,
next scheduled date of service, next scheduled odometer
reading for service, and the vehicle's current odometer
reading.

I would like to ask the query to return those records
where the next scheduled date of service is due, and/or
when service is due based on the current odometer reading
compared to the next scheduled reading. Can I do this by
writing criteria? How?

Thanks for your help!

 

[Bobby C.]

I have a vehicle maintenance database. I have created a
query that contains the following fields:

Date of Service, Odometer reading at service, description,
next scheduled date of service, next scheduled odometer
reading for service, and the vehicle's current odometer
reading.

I would like to ask the query to return those records
where the next scheduled date of service is due, and/or
when service is due based on the current odometer reading
compared to the next scheduled reading. Can I do this by
writing criteria? How?

Thanks for your help!

Cheryl,

The short answer is Yes... I write products like this all the time and you
can build queries to do this but it not trivial. Let me ask a couple of
questions and see if I can help you out.

1) How are the odometer readings formatted and what data type are they?

2) What date are you using to compare against the "next scheduled date of
service"? Today's date or the other date field you described?

3) Given the fields you described, are you wanting to reduce the number of
records to only those that are actually due? Basically what result are you
looking for?

Ok that was more than a couple but let's see if we can get you an answer...

 

[Bobby C.]

I have a vehicle maintenance database. I have created a
query that contains the following fields:

Date of Service, Odometer reading at service, description,
next scheduled date of service, next scheduled odometer
reading for service, and the vehicle's current odometer
reading.

I would like to ask the query to return those records
where the next scheduled date of service is due, and/or
when service is due based on the current odometer reading
compared to the next scheduled reading. Can I do this by
writing criteria? How?

Thanks for your help!

Cheryl,

Ignore my last question. I should have read your post a little closer...
Sorry ....

 

[Cheryl-MO]

Bobby - here's the answers I think!

1. I'm linking the odometer readings from an excel
table. They are formatted in Excel as "general". I have
the query give me the highest number, which comes out to
be the most recent odometer reading.

2. I have a lookup table for maintenance type, which I
built to include service interval by date and miles. The
query adds the appropriate # of miles and days to the day
the service was performed, and what the mileage was on
that day.

3. I think you said I already answered this, but yes, I
want to only show those records that are due for service,
either by day or by mileage. If necessary, we can run the
2 queries separately. Together would be fabulous, but I
think I can train the person who will be doing this to run
one for miles and another for days, if that is needed.
Thanks for your help! Cheryl - MO