Mileage and Fuel MPG

[sps2]

I'm doing pretty well so far, for guessing and reading allot but I
can't figure this out.

I created a DB to track multiple vehicles and their fuel stops but
can't figure out how to tell the code to look up the previous mileage
of a specific vehicle using...

=DLookUp("[Mileage]","Fuel","[FuelID]=" & [FuelID]-1)

Obviously it just grabs the previous mileage that was entered
regardless which vehicle I am on.

There is one table for vehicles and one table for fuel linked using
[VehicleID]

I want it to lookup the mileage from the pervious record of the vehicle
that I am currently on.

Hope that makes cents.

 

[Allen Browne]

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us

 

[Arvin Meyer [MVP]]

Try:

=DLookUp("[Mileage]","Fuel","[FuelID]=" & [FuelID]-1 & " AND [VehichleID] =
" & VehichleID)

 

[sps2]

Thank you very much for you response.

I love this thought pattern but if you switch back and forth between
vehicles you only get a result on the second entry of the same vehicle,
IF you enter the two entries one right after the other.

I'm guessing the DLookUp doesn't see the vhicle ID i am on so it pulls
no data until the second entry.

I'm starting to think I may need to run a query some how and then the
dLookUp on that. Sound like fun!

Or I guess I could just create a seperate table for each vehicle, but
that would require me to create a new table each time we recived a new
vehicle. boo.

Try:

=DLookUp("[Mileage]","Fuel","[FuelID]=" & [FuelID]-1 & " AND [VehichleID] = " & VehichleID)

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I'm doing pretty well so far, for guessing and reading allot but I
can't figure this out.

I created a DB to track multiple vehicles and their fuel stops but
can't figure out how to tell the code to look up the previous mileage
of a specific vehicle using...

=DLookUp("[Mileage]","Fuel","[FuelID]=" & [FuelID]-1)

Obviously it just grabs the previous mileage that was entered
regardless which vehicle I am on.

There is one table for vehicles and one table for fuel linked using
[VehicleID]

I want it to lookup the mileage from the pervious record of the vehicle
that I am currently on.

Hope that makes cents.

 

[sps2]

Actualy I think what is happening is when I add fuel data to one
vehicle and then move to the next vehicle the dlookup is working just
fine but it takes the fuelID and subrtacts 1 but there is no fuelID
with that autonumber using that vehicleID becase that autonumber
belongs to the other vehicleID so it returns nothing.

VehicleID FuleID Mileage
1 55 41566
1 56 41589
1 57 41678
2 58 978795
1 59 41698
1 60 41745

any ideas?

Thank you very much for you response.

I love this thought pattern but if you switch back and forth between
vehicles you only get a result on the second entry of the same vehicle,
IF you enter the two entries one right after the other.

I'm guessing the DLookUp doesn't see the vhicle ID i am on so it pulls
no data until the second entry.

I'm starting to think I may need to run a query some how and then the
dLookUp on that. Sound like fun!

Or I guess I could just create a seperate table for each vehicle, but
that would require me to create a new table each time we recived a new
vehicle. boo.

Try:

=DLookUp("[Mileage]","Fuel","[FuelID]=" & [FuelID]-1 & " AND [VehichleID] = " & VehichleID)

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I'm doing pretty well so far, for guessing and reading allot but I
can't figure this out.

I created a DB to track multiple vehicles and their fuel stops but
can't figure out how to tell the code to look up the previous mileage
of a specific vehicle using...

=DLookUp("[Mileage]","Fuel","[FuelID]=" & [FuelID]-1)

Obviously it just grabs the previous mileage that was entered
regardless which vehicle I am on.

There is one table for vehicles and one table for fuel linked using
[VehicleID]

I want it to lookup the mileage from the pervious record of the vehicle
that I am currently on.

Hope that makes cents.

 

[Arvin Meyer [MVP]]

I'm starting to think I may need to run a query some how and then the

dLookUp on that. Sound like fun!

I believe that's what you need to do. Try using the TOP predicate:

Select TOP 2 FuelID Where VehicleID = & Forms!FormName!txtVehicleID

or:


Select TOP 2 FuelID Where VehicleID = [Please enter VehicleID]
--
Arvin Meyer, MCP, MVP
Microsoft Access
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thank you very much for you response.

I love this thought pattern but if you switch back and forth between
vehicles you only get a result on the second entry of the same vehicle,
IF you enter the two entries one right after the other.

I'm guessing the DLookUp doesn't see the vhicle ID i am on so it pulls
no data until the second entry.

I'm starting to think I may need to run a query some how and then the
dLookUp on that. Sound like fun!

Or I guess I could just create a seperate table for each vehicle, but
that would require me to create a new table each time we recived a new
vehicle. boo.

Try:

=DLookUp("[Mileage]","Fuel","[FuelID]=" & [FuelID]-1 & " AND [VehichleID]
= " & VehichleID)

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I'm doing pretty well so far, for guessing and reading allot but I
can't figure this out.

I created a DB to track multiple vehicles and their fuel stops but
can't figure out how to tell the code to look up the previous mileage
of a specific vehicle using...

=DLookUp("[Mileage]","Fuel","[FuelID]=" & [FuelID]-1)

Obviously it just grabs the previous mileage that was entered
regardless which vehicle I am on.

There is one table for vehicles and one table for fuel linked using
[VehicleID]

I want it to lookup the mileage from the pervious record of the vehicle
that I am currently on.

Hope that makes cents.