Missing something?

[Guest]

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

 

[Douglas J. Steele]

You're missing a single quote between " and And. However, the original
answer appears to be missing one closing parenthesis as well: there are 4
opening, and only 3 closing. Try tacking ,0) at the end of that.

 

[Guest]

Thanks for replying so quickly Doug. I added the ' and the ,0) but I am
still getting the error...

Microsoft Visual Basic
Compile error:
Expected: list separator or )

and it is still highlighting the ' " AND " section

Here is what I have now:

Nz(Dlookup("OdometeReading","Transitions","FleetNumber='"& ComboBox&"'AND"&
"DateOfPurchase="& Format(Dmax("DateOfPurchase","Transactions",
"FleetNumber='"&ComboBox&'""), "\#mm\/dd\/yyyy\#")),0
)

--
Tim
TnT Computer Services LLC
Wisconsin

You're missing a single quote between " and And. However, the original
answer appears to be missing one closing parenthesis as well: there are 4
opening, and only 3 closing. Try tacking ,0) at the end of that.

 

[Douglas J Steele]

Try putting a spaces around the keyword AND.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for replying so quickly Doug. I added the ' and the ,0) but I am
still getting the error...

Microsoft Visual Basic
Compile error:
Expected: list separator or )

and it is still highlighting the ' " AND " section

Here is what I have now:

Nz(Dlookup("OdometeReading","Transitions","FleetNumber='"& ComboBox&"'AND"&
"DateOfPurchase="& Format(Dmax("DateOfPurchase","Transactions",
"FleetNumber='"&ComboBox&'""), "\#mm\/dd\/yyyy\#")),0
)

 

[Guest]

Sorry... it is still giving me that error. It is identifying the ' that I
have just before the " AND ".

Maybe I should just try a different code and start from scratch. I may have
the right idea, but just the wrong words or something.

 

[Guest]

Doug... excuse my ignorance please, but what is the difference between the '
and " in the coding formulas? If I knew this I may be able to better figure
out the problem.

THanks.

 

[Douglas J Steele]

If the field being compared to is Text, you must surround the value with
quotes. Access doesn't care whether it's a single quote or a double quote
(within reason)

If the field being compared to is Numeric, you don't want the quotes.

 

[Guest]

Try this:


Nz(Dlookup("OdometeReading","Transitions","FleetNumber='"& ComboBox & "'AND"
& "DateOfPurchase=" & Format(Dmax("DateOfPurchase","Transactions",
"FleetNumber='"& ComboBox & '"), "\#mm\/dd\/yyyy\#")),0)

Thanks for replying so quickly Doug. I added the ' and the ,0) but I am
still getting the error...

Microsoft Visual Basic
Compile error:
Expected: list separator or )

and it is still highlighting the ' " AND " section

Here is what I have now:

Nz(Dlookup("OdometeReading","Transitions","FleetNumber='"& ComboBox&"'AND"&
"DateOfPurchase="& Format(Dmax("DateOfPurchase","Transactions",
"FleetNumber='"&ComboBox&'""), "\#mm\/dd\/yyyy\#")),0
)

 

[Douglas J Steele]

No, Dennis: you're missing necessary spaces around AND, and your quotes are
wrong at the end.

 

[Guest]

I thought I got all those. He has LOTS of missing spaces. Oh well, my bad...

;^)

 

[Guest]

Maybe this...

Nz(Dlookup("OdometeReading","Transitions","FleetNumber='" & ComboBox &
"'AND" & "DateOfPurchase=" & Format(Dmax("DateOfPurchase","Transactions",
"FleetNumber='" & ComboBox & "'"), "\#mm\/dd\/yyyy\#")),0)

 

[Douglas J Steele]

Still missing those spaces...

Nz(Dlookup("OdometeReading","Transitions","FleetNumber='" & ComboBox &
"' AND DateOfPurchase=" & Format(Dmax("DateOfPurchase","Transactions",
"FleetNumber='" & ComboBox & "'"), "\#mm\/dd\/yyyy\#")),0)

 

[Guest]

Forest. Trees. Duh....

Still missing those spaces...

Nz(Dlookup("OdometeReading","Transitions","FleetNumber='" & ComboBox &
"' AND DateOfPurchase=" & Format(Dmax("DateOfPurchase","Transactions",
"FleetNumber='" & ComboBox & "'"), "\#mm\/dd\/yyyy\#")),0)

 

[Guest]

OK. I got the formula to enter without errors by using this formula...
Nz (DLookup("OdometeReading", "Transitions", "FleetNumber='" & ComboBox & "'
AND DateOfPurchase=" & Format(DMax("DateOfPurchase", "Transactions",
"FleetNumber='" & ComboBox & "'"), "\#mm\/dd\/yyyy\#")))

I think it is working, but now I just realized that it is not being told
where to put the end results. I have an UNBOUND field called LastFill that I
want this information to be placed in. Is that a seperate entry?

By the way... the way that I have the information being entered into this
form is
1. Store code where purchased. (not used in this formula)
2. FleetNumber of the vehicle
3. Type of Transaction (not used in this formula. Just info for accounting)
4. DateOfPurchase
5. TimeOfPurchase. It is at this point that I have the formula placed in
the "AFTER UPDATE" event line.

At this time I was trying to get the LastFill box to have the information
gathered by the lookup formula entered.

Should I put the formula in the LastFill box instead?