T
Tim from TNTCSLLC
I have a database that tracks Fleet Trucks gas and maintenance usage. I am
trying to get it to give an instant reading of MPG when you enter the most
current gas purchase. The key information that I have identified is as
follows.
Table: Transactions
FleetNumber: THis is the number of the truck that is being tracked. It is a
combobox on the form.
DateOfPurchase: This is the Date of the Gas purchase.
TimeOfPurchase: This is the Time of the purchase.
OdometeReading: THis is the Odometer reading at the time of purchase.
LastFill: This is an unbound field on the form that is supposed to be
prefilled with the last Odometer reading based on the Fleet Number , Date of
Purchase and Time of Purchase.
What I am trying to do is get the form to lookup the highest Odometer
reading of a truck that is chosen from the FleetNumber comboBox on the form
and based on the information entered in the DateOfPurchase and TimeOfPurchase
fields, enter the highest Odometer reading in to the LastFill field on the
form.
If I can get that to work I can get it to give me the Current MPG for that
tank of gas.
I am trying to adapt the formula I found here written by "Doug Steele" (I
think) in his response to Nick who was trying to do something similar.
Here is his original Code:
Nz(Dlookup("StartTime","Schedule", _
"Name='" & ComboBox& "' AND " & _
"Datefield = " & _
Format(Dmax("DateColumn", "Schedule", _
"Name='" & ComboBox & "'"), _
"\#mm\/dd\/yyyy\#"))
Here is what I tried:
Nz(Dlookup("OdometeReading","Transitions","FleetNumber='"& ComboBox&"AND"&
"DateOfPurchase="& Format(Dmax("DateOfPurchase","Transactions",
"FleetNumber='"&ComboBox&'""), "\#mm\/dd\/yyyy\#"))
I am sure that I have goofed here, as I keep getting the following error
when I click off this line.
Microsoft Visual Basic
Compile error:
Expected: list separator or )
and the item that is highlighted is the '"AND" section.
I think that I am not reading the coding correctly. There are several times
that there are ' and " used next to each other and I can't tell which is
which. There is also the chance that I have totally goofed up the codes and
that what I am trying is totally different that what I have found to try.
Any ideas?
Thanks
trying to get it to give an instant reading of MPG when you enter the most
current gas purchase. The key information that I have identified is as
follows.
Table: Transactions
FleetNumber: THis is the number of the truck that is being tracked. It is a
combobox on the form.
DateOfPurchase: This is the Date of the Gas purchase.
TimeOfPurchase: This is the Time of the purchase.
OdometeReading: THis is the Odometer reading at the time of purchase.
LastFill: This is an unbound field on the form that is supposed to be
prefilled with the last Odometer reading based on the Fleet Number , Date of
Purchase and Time of Purchase.
What I am trying to do is get the form to lookup the highest Odometer
reading of a truck that is chosen from the FleetNumber comboBox on the form
and based on the information entered in the DateOfPurchase and TimeOfPurchase
fields, enter the highest Odometer reading in to the LastFill field on the
form.
If I can get that to work I can get it to give me the Current MPG for that
tank of gas.
I am trying to adapt the formula I found here written by "Doug Steele" (I
think) in his response to Nick who was trying to do something similar.
Here is his original Code:
Nz(Dlookup("StartTime","Schedule", _
"Name='" & ComboBox& "' AND " & _
"Datefield = " & _
Format(Dmax("DateColumn", "Schedule", _
"Name='" & ComboBox & "'"), _
"\#mm\/dd\/yyyy\#"))
Here is what I tried:
Nz(Dlookup("OdometeReading","Transitions","FleetNumber='"& ComboBox&"AND"&
"DateOfPurchase="& Format(Dmax("DateOfPurchase","Transactions",
"FleetNumber='"&ComboBox&'""), "\#mm\/dd\/yyyy\#"))
I am sure that I have goofed here, as I keep getting the following error
when I click off this line.
Microsoft Visual Basic
Compile error:
Expected: list separator or )
and the item that is highlighted is the '"AND" section.
I think that I am not reading the coding correctly. There are several times
that there are ' and " used next to each other and I can't tell which is
which. There is also the chance that I have totally goofed up the codes and
that what I am trying is totally different that what I have found to try.
Any ideas?
Thanks