Calculate 28 days of mileage

[foshizzle]

The user needs 28 days worth of mileage. I would like to make this a report eventually. I also want the user to pick/enter the begin date on a form to get the calculated 28 days worth of mileage. IE the first day of the 28 that he needs in case he runs it on a later date..

My plan was to have a form where the user inputs the current mileage daily (as they do now on paper.)

My table has the following fields: (I may be short or have too many.)

• VehicleNum --> Primary Key
• PreviousDate --> Needs to be yesterday's date field
• PreviousMileage ->Needs to be yesterday's date value CurrentMileage
• CurrentDate --> Populated by user input field
• CurrentMileage --> User inputs each day onto a form
• Total --> Not sure how the best way to calculate:?? CurrentMileage - Previous Mileage I assume? Then let a query count 28 days worth?

I am not very good at access anymore so please be specific. If you dont understand I will try and clarify better; just ask. Im not sure how to formulate or come up with accurate data

Thanks

 

[plog]

You shouldn't store calculated fields, nor redundant data. So the way I see it you only need these fields:

VehicleNum, MileageDate, Milage

Those are the only fields you need to do what you want. To get the data you want, I would create a sort of 'running total query'. Search this forum for that term and you will find methods on how to implement this. Now, your running total won't be month to date, or year to date or cumulative, it will instead be within the last 28 days of the Milage date.

Search the forum, and give it a shot; if you get stuck post back here what you have and where you are stuck.

 

[Cronk]

If you have a mileage figure for every day, you could sum the top 28 records to get the total distance, if the daily records are the mileage for each day.

 

[foshizzle]

Ok. I looked at a few searches as well as the above site. I KNOW I'm making this way too complicated for myself. Sorry.

I get the part about the table now. On the query, I believe I will be getting user input date for the 28 days from below code? (Just bc I want the user to put it in)

Between [Forms]![NAV-Mileage]![txtBeginSearch] And [Forms]![NAV-Mileage]![txtEndSearch]

But I am still unsure about the expression for DSUM. Looking at others, I think its something like this but im sure Im off somehow..

Query:
VehicleNum (Show, Group By)
MileageDate (Show)
Mileage (Sum)

Now for the DSUM expression field....
RunTot: Format(DSum("Mileage","[VehicleNum]")

Im sure thats too much..

 

[plog]

First, if every VehicleNum/MilelageDate permutation is unique in your table (you only input the mileage once per date per vehicle right?), there is no need to GROUP BY them. This shouldn't be an aggregate query, don't click the Sigma (aka Summation sign).

Second, don't format the RunTot. That's a presentation element, let's get this working first before we pretty it up.

Third, the syntax for your DSUM is incorrect. Check out the reference for DSUM (http://www.techonthenet.com/access/functions/domain/dsum.php). It takes 3 arguments: the first is the field you want to sum ("[Mileage]") the second is the datasource the field is in (you never mentioned your table name, but this is where it goes), the last argument is criteria and is the trickiest. To get started, let's start out with simple criteria--just the matching vehicleNum. So this would be your DSUM:

RunTot: DSUM("[Mileage]", "YourTableNameHere", "[VehicleNum]=" & VehicleNum)

The above assumes VechicleNum is numeric, if it is not then this will be the Dsum:

RunTot: DSUM("[Mileage]", "YourTableNameHere", "[VehicleNum]='" & VehicleNum & "'")

Run that it should give you the total mileage for your vehicle. Tell me what you get.

 

[foshizzle]

Looks good so far!
BTW, tablename is TBL-Mileage and VehicleNum is text so I used
RunTot: DSUM("[Mileage]", "TBL-Mileage", "[VehicleNum]='" & VehicleNum & "'")

Side Note.. I ran into a problem trying to add data for this test..
VehicleNum was the Primary Key for this table. It is also a lookup field based on values from another table. It wont let me change it bc its a PK to allow duplicates. So I added the default ID field and let it AUTONUMBER. Is that ok you think?

 

[plog]

Vehicle number shouldn't be the primary key for this table. It honestly doesn't need one, but adding an autonumber is fine--I wouldn't call it just 'ID', I would never use that in any table, instead prefix it with what the table is for (i.e. MileageID). Makes joining tables easier later on.

It is also a lookup field based on values from another table

What does that mean exactly. You should have another table called something like 'Vehicles' where you have a list of all the data associated with that vehicle. number

 

[foshizzle]

Yes, that is correct. Vehicles is another table that cosists solely of the VehicleNum. This is so the user is able to add/remove vehicles without calling myself... I added the new primary key and let it autonumber.

I am able to get everything working correctly now with regards to the expression in the query. RunTot: DSum("[Mileage]","TBL-Mileage","[VehicleNum]='" & [VehicleNum] & "'")

My query comes up with the correct total number. Now I just need to make it by dates inputed by user via the form. Either by begin and end date for the 28 days. or just one or other by telling it to sum 28 days worth..

 

[plog]

Good, I wanted to make sure we got that far before we put the 28 days limit on it. To do this, we need to edit the 3rd argument of your Dsum, the criteria part. Currently it is this:

"[VehicleNum]='" & [VehicleNum] & "'"

You need to add additional criteria to just include those records that occured within 28 days of the [MileageDate] field. So it should look like this:

"[VehicleNum]='" & [VehicleNum] & "' AND " & (" & [MileageDate] & " - [MileageDate]) >=0 AND (" & [MileageDate] & "-[MileageDate]<=28"

Try that for the criteria. I haven't tested it, so it might be off a little. See what you can do.

 

[foshizzle]

I keep getting an error "The expression you entered has an invalid string." I'm not really sure what all this is saying but here is what I have in there now....

RunTot: DSUM("[Mileage]", "TBL-Mileage", "[VehicleNum]='" & [VehicleNum] & "' AND " & (" & [MileageDate] & " - [MileageDate]) >=0 AND (" & [MileageDate] & "-[MileageDate]<=28")

 

[plog]

You have an extra quote after your 'AND'

 

[foshizzle]

I went through it. This one pulls data, but pulls everything. And the RunningTotal is the same for all vehicle numbers

RunTot: DSUM("[Mileage]", "TBL-Mileage", "[VehicleNum]=” & [VehicleNum] & " AND " & (" & [MileageDate] & " - [MileageDate]) >=0 AND (" & [MileageDate] & "-[MileageDate]<=28")


QRY-Mileage Vehicle Current Date Current Mileage RunTot 185 11/1/2013 2 21147 186 11/1/2013 1 21147 189 11/1/2013 1 21147 185 11/8/2013 2 21147 186 11/8/2013 8 21147 189 11/8/2013 9 21147 185 10/2/2013 9999 21147 149 11/28/2013 11125 21147

 

[plog]

Try this:

"[VehicleNum]='" & [VehicleNum] & "' AND (" & [MileageDate] & " - [MileageDate]) >=0 AND (" & [MileageDate] & "-[MileageDate])<=28"

 

[foshizzle]

I'm just not having any luck still. I just keep staring at the same problem.

I enter

RunTot: DSUM("[VehicleNum]='" & [VehicleNum] & "' AND (" & [MileageDate] & " - [MileageDate]) >=0 AND (" & [MileageDate] & "-[MileageDate])<=28")

The expression you entered has a function containing the wrong number of arguments.

 

[plog]

My last post was the criteria argument (3rd one) not the Field name argument (1st one) like you used it. An argument is data you pass a function.
DSum has 3 arguments:

DSum("[FieldName]", "TableName", "Criteria")

My last post was the Criteria argument.

"[VehicleNum]='" & [VehicleNum] & "' AND (" & [MileageDate] & " - [MileageDate]) >=0 AND (" & [MileageDate] & "-[MileageDate])<=28"

 

[foshizzle]

Ah, I see. Sorry, I dont remember much on Access..

I got further this time but receive an error..
Thanks for your patience

 

[plog]

Again, what i typed is the DSum criteria, not the criteria of the Query itself. Check out my previous post and the Dsum documentation: http://www.techonthenet.com/access/functions/domain/dsum.php

 

[RainLover]

Do we really need to Sum every day and show that value or do we just need a single total value.

If it is just one value that is required then why not subtract day one from day 29.

Just a thought, hope it helps.

 

[CazB]

Depends what is being stored as the mileage on each date? If it's the Odometer reading, then you should be able to just take the reading on the 'entered date' away from the reading 28 days after.....
But if it's how many miles driven since the last reading, then yes, they'll need to sum them

 

[foshizzle]

Oh man.. I think I understand a little better now.. I have:

RunTot: DSum("[Mileage]","TBL-Mileage",("[VehicleNum]='" & [VehicleNum] & "' AND (" & [MileageDate] & " - [MileageDate]) >=0 AND (" & [MileageDate] & "-[MileageDate])<=28"))

I now have no error and data, but I am still pulling in all the dates, even those older than 28 days..