vehicle fuel consuption

[Daniel2000]

have designed a small access database to monitor fuel consuption. each time we issue a fuel voucher, we record vehicle number plus mileage (meter reading) at start and end and database automatically calculates no of km.

as of now, users have to enter the meter reading at start and meter reading at end manually using a form.



what i want is when i choose the car number from my form, then meter reading at start should capture the last meter reading at end (prior to today's entry date) as the default meter reading at start. this will be a locked properties and users will not have input access.

[Bob Fitz]

Perhaps you could use the DMax() function with criteria to return the mileage.

[Daniel2000]

Bob, This is what I have been trying (DMax) . Issue is the criteria. Will need criteria to return DMax value where car number on form matches against car number in table AND where entry date on form is less than entry date in table.

Example, if I were to review a past record, then DMax should be DMax value for the prior date on the from.

[Bob Fitz]

Perhaps something like:
=DMax("YourMileageField","TableName","[CarNumberFieldName] = Me.CarNumberControlNameOnForm AND [DateFieldNameInTable] < Date())

[Daniel2000]

Thank you very much Bob.

Still not too clear. I am attaching small database whereby you can see the form and table. Grateful if you can us

[Bob Fitz]

Would be please to look at the db but I use A2003 so any thing posted would need to be converted to A2003 mdb format first.
Alternatively, can you tell us what you're not clear about.

[Daniel2000]

Bob, thanks.

Formula mentioned by you is :

=DMax("YourMileageField = Stop","TableName = April 13","[CarNumberFieldName=Car] = Me.CarNumberControlNameOnForm=Car AND [DateFieldNameInTable=Date] < Date())

converted as

=DMax("Stop","April 13","[Car] = Me.Car AND [Date] < Date())

Is that correct?

Doc10.zip

[Bob Fitz]

"April 13" seems like an unusual name for a table, but if that is correct it may need to be enclosed in square brackets because it has a space in the name. So, maybe:
=DMax("Stop","[April 13]","[Car] = Me.Car AND [Date] < Date())
Also, "Date" is a reserved word in Access so this is poor choice of field name. You may need to change this to something else. Perhaps "RecordedDate". Your expression would then be:
=DMax("Stop","[April 13]","[Car] = Me.Car AND [RecordedDate] < Date())

[Daniel2000]

Hi Bob,

I stil cannot crack this out. I have renamed both the table and date and still not working. It is giving error. I have tried the criteria on both form and in a query but still not getting the correct result. I have checked my criteria over 20 times already and seems ok.

Can I use an after update or after insert event procedure? I have used a before insert procedure to calculate next ful voucher no and it is working fine. What do u suggest?

[Bob Fitz]

Perhaps I could help if you post a copy of the db in A2003 mdb format.

[Daniel2000]

Database10.zip

Bob,

Thanks for your help. Really appreciated it. I am attaching copy of DB in A2003.

[Bob Fitz]

Daniel2000

Sorry for the delay in making a reply.

Please see the attached db which I THINK will give the result you want but without using the expression we had been looking at.
I have made changes to the Row Source property of the “Car” combo box and added some code to its After Update property. If this is not what you want or you have questions about how it has been done, please post back.

[ssanfu]

PMFJI,

I started a milage/fuel usage mdb about 2000. Don't laugh... it was a first attempt.
I did some updating and converted it to 2010.

I know it is not what you are trying to do, but it is close. I only save the ending odometer reading and calculate mileage and miles/gal.
I don't use lookup FIELDS in tables. Unless you are working directly in the table, setting up a lookup field is a waste of time.

Anyway, maybe this will help......

[Daniel2000]

Hi Bob,

Have just checked the database you send. It is doing part of the tasks but not ALL. First thing is that if I entered the wrong car number and goes back to changed it, the startmeterreading keeps showing the stop data from the other car number which was wrongly inserted in the car field. It does not change.

Secondly, when I entered a new record on the form, it is NOT saving in the April 13 table and thus it keeps showing the MAX startmeterreading from the old records.

Please check and advise.

[Bob Fitz]

Hi Bob,

Have just checked the database you send. It is doing part of the tasks but not ALL. First thing is that if I entered the wrong car number and goes back to changed it, the startmeterreading keeps showing the stop data from the other car number which was wrongly inserted in the car field. It does not change.

Yes, I see what you mean. Try the attached db.

Secondly, when I entered a new record on the form, it is NOT saving in the April 13 table and thus it keeps showing the MAX startmeterreading from the old records.

As far as I can see, the only data NOT being saved is the calculation between the Start and Stop fields (No of km). This is calculated data, so it should NOT be saved, rather calculated whenever and wherever it is required.