Call back data from 1 day before

[Anugerah]

Hi, I'm still quite new in access.

So, I already have a query that contain vehicle milage recorded per day on each record.
I want to create a new field containing milage recorded from 1 day before each record so I could have milage usage data.
Please help me providing the solution for this issue.

Thank you

[pbaldy]

Check out:

http://allenbrowne.com/subquery-01.html#AnotherRecord

[alansidman]

Attached is an older .mdb practice db that I have kept to demonstrate what you are looking for.

[Anugerah]

This is my query.
So I couldn't use ID because all the equipment is mixed here.
I need formula for another field that look up the hourmeter 1 day prior to maintenance date for each equipment

[pbaldy]

The ID is probably equivalent to your inventory number. It makes sure your getting the previous reading for the correct item.

[Anugerah]

The ID is probably equivalent to your inventory number. It makes sure your getting the previous reading for the correct item.

No, the ID isn't equivalent with inventory number.
So I want to call again the hourmeter record 1 day before each record, and of course from the record with the same inventory number

FYI.

The inventory number here is mixed up so does the ID

[pbaldy]

Use your inventory number in place of the address ID in the link. Attach your db here if you're not understanding.

[alansidman]

Why not a record ID to your table?

[Anugerah]

I even tried to make strings from date and prior date like this



This is the formula I use for Prior HM

PriorHM: DLookUp("[Hourmeter]";"Hourmeter Usage";"Inventory Number=" & [Inventory Number] And "Prior Date String=" & [Maint Date String])

But, still can't get it right

[isladogs]

You don't appear to have used Allen Browne's approach (meter reading example) which involves a subquery

You haven't said how you created the additional fields
If you have a routine to do that automatically using a function or query, that may be acceptable, but not if you have to enter the records manually

Anyway, your expression fails as both field in the lookup are text strings so you need text delimiters

PriorHM: DLookUp("Hourmeter";"Hourmeter Usage";"Inventory Number='" & [Inventory Number] & "' And Prior Date String='" & [Maint Date String] "'")

Suggest you don't name text fields with Number as part of the field name!

[pbaldy]

PriorHM: DLookUp("Hourmeter";"Hourmeter Usage";"Inventory Number='" & [Inventory Number] & "' And Prior Date String='" & [Maint Date String] * "'")

You missed brackets on objects with inadvisable spaces in the names.

[isladogs]

You missed brackets on objects with inadvisable spaces in the names.

Hi Paul

Oops
You are of course right but for some reason you didn't post the corrected version which is (I hope):

PriorHM: DLookUp("Hourmeter";"[Hourmeter Usage]";"[Inventory Number] = '" & [Inventory Number] & "' And [Prior Date String] = '" & [Maint Date String] "'")

In my defence I just copied the OP's code & added text delimiters .....
Just the 3 errors! Having a bad hair day here!

[pbaldy]

for some reason you didn't post the corrected version

I was on a mobile device, too tedious (or lazy) to edit code.

[isladogs]

But luckily not too 'lazy' to point out the errors. Thanks again

[Anugerah]

Thank you for all your help. I don't know why but the code I copied from here doesn't work well.
Finally I tried some modification and this is the code that works for me.

PriorHM: DLookUp("[Hourmeter]";"[Hourmeter Usage]";"[Inventory Number] = '" & [Inventory Number] & "' AND [Maint Date String]= '" & [Prior Date String] & "'")