Using Max function to show current mileage

[MitziUK]

I have a form which shows vehicle particulars, reg number, engine number,
cyclinder capacity, date purchase, date sorn, date sold, make (shown as a
combo box getting data from the make table), model,(shown as a combo box
getting data from that stores in model table), driver (shown as a combo box
getting data from the employee table).

On this form I want to show the current mileage for that partiular vehicle
currently being displayed. Registration Number is in Form Header.

Vehicle Mileage is saved as a separate table in my database and linked
through as a sub form.

Am I right in saying that I need to add a text box (from the form controls)
to the form and set the properties to show max mileage. but how do I use the
max function? to show the max mileage.

 

[Ed Robichaud]

One of the common solutions to displaying info on a form/report from other
than the form/report's recordsource, is to use a domain function expression
in a text box. Add a text box to your main form, make its control source
blank, then enter the expression:

DMax("MaxMileage","MyMileageTable","VehicleReg=Forms!myForm!VehicleReg")

This will compute and display the max mileage for each record/vehicle.

-Ed

 

[MitziUK]

Ed

I get #Error flash in each record. ANy idea why?

 

[Ed Robichaud]

Because the function is not calculating a value. In my example, you'll need
to substitute the name of your mileage field, mileage table, and lastly the
names of the ID field in your table and the control on your form (can be
invisible) that has the same/linked ID field.
-Ed

 

[MitziUK]

Ed

I cannot get this to work, still shows #Error. this is the expression I'm
using:

=DMax("Mileage","Mileage Details","REGISTRATION NUMBER=Forms!Company
Vehicles (Vans-Cars)!REGISTRATION NUMBER")

Where am I going wrong?

Here's my table structure:

Mileage Details Table:
REGISTRATION NUMBER (Text field)
Mileage (Number field)
Wk ending (Date/Time field)

The form is made up from fields taken from my Vans & Cars tables. the
Registration number on the form is a text field and is the Form Header

Yours banging head against brick wall

 

[MitziUK]

SORTED

This worked:

=DMax("[Mileage]","Mileage Details","[Registration Number] = '" &
[Registration Number] & "'")