PLEASE HELP!!! Select records query

[CAT]

I am trying to select/filter records in a query to show
those whose anticipated date and or mileage of next
scheduled maintenance is due. I have filtered for the
date by saying "<=Date()" in the "next scheduled date
field.

I have a "next scheduled mileage" field, and a Maximum
Odometer field in this query.

How can I add criteria to this query so that records whose
maximum (current) odometer reading is greater than or
equal to the next scheduled mileage?

Please help. thanks, cat

 

[John Vinson]

I am trying to select/filter records in a query to show
those whose anticipated date and or mileage of next
scheduled maintenance is due. I have filtered for the
date by saying "<=Date()" in the "next scheduled date
field.

I have a "next scheduled mileage" field, and a Maximum
Odometer field in this query.

How can I add criteria to this query so that records whose
maximum (current) odometer reading is greater than or
equal to the next scheduled mileage?

Just put a criterion of

= [tablename].[Next scheduled milage]

on the odometer reading field. The brackets are needed, otherwise
Access will think you're providing a text string.

 

[CAT]

Just tried it - says it's too complicated. here's some
more information: The "next mileage" field is a
calculated field within this query. Therefore, I didn't
put the table name and field name in the criteria line -
only the field name. The Odometer field is set to group =
max, since I want only the highest odometer reading.

Does this help, or only make more questions? I'll be
happy to send you my e-mail address if that would help.
thanks, CAT

-----Original Message-----

I am trying to select/filter records in a query to show
those whose anticipated date and or mileage of next
scheduled maintenance is due. I have filtered for the
date by saying "<=Date()" in the "next scheduled date
field.

I have a "next scheduled mileage" field, and a Maximum
Odometer field in this query.

How can I add criteria to this query so that records whose
maximum (current) odometer reading is greater than or
equal to the next scheduled mileage?

Just put a criterion of

= [tablename].[Next scheduled milage]

on the odometer reading field. The brackets are needed, otherwise
Access will think you're providing a text string.


.

 

[John Vinson]

Just tried it - says it's too complicated. here's some
more information: The "next mileage" field is a
calculated field within this query. Therefore, I didn't
put the table name and field name in the criteria line -
only the field name. The Odometer field is set to group =
max, since I want only the highest odometer reading.

Please post the SQL of your query. You indeed cannot use a calculated
field in a further calculation - you must recapitulate the expression.
I.e. you may need to use Max([milage]) or some such rather than the
fieldname.

Does this help, or only make more questions? I'll be
happy to send you my e-mail address if that would help.
thanks, CAT

Only for paying clients... sorry!