Ms Access Code Error

(Nz(DMin("OdoAtFillDate","tblFuelLog","DriverId="&[DriverId]&"OdoAtFillDate>" & [OdoAtFillDate] & " And VehicleNumber = " & Chr(34) & [VehicleNumber] & Chr(34)),0)-[OdoAtFillDate])

Open in new window



I have the above code not sure where it is wrong any help please this is in ref to Question http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28266018.html

Anyone who can figure out my problem on this http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28266018.html question would get point for both questions, there is more information on the question I posted the link for. there is also a sample DB on the above link
Blueice13085Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this, copy and paste

(Nz(DMin("OdoAtFillDate","tblFuelLog","DriverId= " & [DriverId] & " and OdoAtFillDate>" & [OdoAtFillDate] & " And VehicleNumber = " & Chr(34) & [VehicleNumber] & Chr(34)),0)-[OdoAtFillDate])
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
I like to take my criteria strings out of the domain functions, at least until they are tested.

Cannot open your stuff on my iPad, but assuming that DriverID is numeric, OdoAtFillDate is a date, and VehicleNumber is alphanumeric, I think this should do it.  Oh, this also assumes that the literal values you are using are fields that belong to the RecordSource of a form and are probably also control names for textboxes on said form.

Dim strCriteria as string
strCriteria = "DriverId= " & me.[DriverId] & " and " _
                  & "[OdoAtFillDate] > #" & me.[OdoAtFillDate] & "# And " _
                  & "[VehicleNumber] = " & Chr(34) & me.[VehicleNumber] & Chr(34)

Something = Nz(DMin("OdoAtFillDate","tblFuelLog",strCriteria),0)-[OdoAtFillDate]

Depending on what OdoAtFillDate is, Something will be a numeric value.  If you want to know the number of days between that min value > odoatfilldate and the value you entered you might want to try the DateDiff function:

DateDiff("d", me.odoatfilldate, Nz(DMin("OdoAtFillDate","tblFuelLog",strCriteria),0))

But if there are no records in your table where [OdoAtFillDate] is greater than the value you entered, then that domain function will return a zero with is equal to 30 Dec 1899, so you might not want that after all.
0
Blueice13085Author Commented:
Think my problem is not in that code, I am lost now lol here is what I am trying to get done.

I have 3 tables one tblVehicles, tblDrivers, and tblFuelLog

tblVehicles has following fields                
VehicleId
DriverID
FuelId
VehicleNumber
StartOdo

tblDrivers has the following fields
DriverId
VehicleId
FuelId
FirstName
LastName
AssignedVehicle


tblFuelLog has the following fields
FuelId
DriverId
VehicleID
VehicleNumber
Driver
FillDate
FillTime
QtyGallons
OdoAtFillDate
PriceAtPump


What I need a Query or code to do is the following

I need it to take the StartOdo from tblVehicles as the base miles to start. Then each time a Driver puts in there OdoAtFillDate and QtyGallons for VehicleNmber I want it to get Miles Driver Drove then MPG per driver per truck

i.e. Data
tblVehicles
VehicleID = 1, StartOdo = 10
VehicleID = 2, StartOdo = 10

tblFuelData
FuelId=1 VehicleId=1, DriverId=1,  OdoAtFillDate = 30, QtyGallons = 5, PriceAtPump=$20.00
FuelId=2 VehicleId=1, DriverId=2,  OdoAtFillDate = 20, QtyGallons = 5, PriceAtPump=$20.00
FuelId=3 VehicleId=1, DriverId=1,  OdoAtFillDate = 40, QtyGallons = 5, PriceAtPump=$20.00
FuelId=4 VehicleId=1, DriverId=2,  OdoAtFillDate = 50, QtyGallons = 5, PriceAtPump=$20.00
FuelId=5 VehicleId=2, DriverId=1,  OdoAtFillDate = 30, QtyGallons = 5, PriceAtPump=$20.00
FuelId=6 VehicleId=2, DriverId=2,  OdoAtFillDate = 20, QtyGallons = 5, PriceAtPump=$20.00
FuelId=7 VehicleId=2, DriverId=1,  OdoAtFillDate = 40, QtyGallons = 5, PriceAtPump=$20.00
FuelId=8 VehicleId=2, DriverId=2,  OdoAtFillDate = 50, QtyGallons = 5, PriceAtPump=$20.00

What I need Query to show
VehicleId=1, DriverId=1,  totMiles = 20, totGallons = 10, totPrice=$40.00, MPG=2
VehicleId=2, DriverId=1,  totMiles = 20, totGallons = 10, totPrice=$40.00, MPG=2
VehicleId=1, DriverId=2,  totMiles = 20, totGallons = 10, totPrice=$40.00, MPG=2
VehicleId=2, DriverId=2,  totMiles = 20, totGallons = 10, totPrice=$40.00, MPG=2

Hope this makes sense
0
Blueice13085Author Commented:
OdoAtFillDate is a Number field this is the miles off the Vehicle at the time of fill up, also when the StartOdo is entered into tblVehicles the fuel tank will be full!


Also Type error:

tblFuelData should be tblFuelLog

Sorry for the typo!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.