Ms Access Code Error

Posted on 2013-10-14
Medium Priority
Last Modified: 2013-10-14
(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
Question by:Blueice13085
  • 2
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1000 total points
ID: 39572431
try this, copy and paste

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

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 39572476
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.

Author Comment

ID: 39572489
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                

tblDrivers has the following fields

tblFuelLog has the following fields

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
VehicleID = 1, StartOdo = 10
VehicleID = 2, StartOdo = 10

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

Author Comment

ID: 39572508
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!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
With the emergence of Office 365 as a superior email communication platform, many organizations have started switching over to it.  After migrating to Office 365, sometimes users, as well as organizations, will have to import PST files to Office 36…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question