Missing something?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

--
Tim
TnT Computer Services LLC
Wisconsin
.

Relevant Pages

  • Re: Dont read if you dislike long posts.
    ... gas meter, then both pipes go up in the ceiling. ... Also wrote down the current reading, ... on the bill that says "if you get a huge bill, ...
    (alt.sysadmin.recovery)
  • Re: Highlander release date
    ... Am I reading this thread right? ... TeamB members are jumping ship? ... purchase every "gambled" with company money. ... WinFX runtime on Win-XP machines should allow you to use the new Vista UI ...
    (borland.public.delphi.non-technical)
  • Re: GPS for Ultralights
    ... >>Looking to purchase a GPS for Ultralight Flying. ... The Barometric Altimeter in the 76CS works very well when calibrated ... 1/100th of on Inch of their reading, ... making the Barometric Pressure reading much more accurate. ...
    (sci.geo.satellite-nav)
  • Re: Massive Natural Gas Explosion "Flips" a House
    ... "(Meter readings show a larger than usual amount of gas flowing ... In a slow leak, gas ... would have migrated to other parts of the house, ... The reading on June 2 indicated that *sometime ...
    (alt.home.repair)
  • Re: What shaft?
    ... And this amazing purchase will be what exactly? ... I don't recall ... reading a definitive statement. ...
    (uk.rec.motorcycles)