As the title says I'm having trouble in my Vehicle Maintenance routine where
I will have a a monthly maintenance based on the last maintenance date.
If the vehicle has reach 1 month since last maintenance it will show in a
datagrid. Also it should select those vehicle that reach a certain mileage
even it doesn't reach the monthly routine and will reset the counter for 1
month. Sorry but I don't know where to start.

The tables are:
OdometerBefore - mileage I'm talking about
OdometerAfter - mileage I'm talking about

Recommended Answers

All 4 Replies


You need to think about the two separate cases you have just outlined. You have to forgive me but I'm not 100% sure of Access syntax

Case 1 - one month or more since last serviced, the sql would be something like: WHERE (LastServiced <= DATEADD(Month, -1, GETDATE())

Case 2 - Mileage trigger, I'm not sure if you want a set of predefined triggers i.e. 20,000 30,000 40,000 etc or something like every 10000 miles : WHERE(Mileage IN (20000, 30000, 40000, 50000)) or WHERE(Mileage % 10000 = 0)

When you have settled on exactly what you want then you would combine them with an OR operator (You would also add a DISTINCT clause to the Query if you did not want a vehicle that satisfied both clauses appearing twice) e.g. :

WHERE (LastServiced = DATEADD(Month,-1, GETDATE())) 
(Mileage % 10000 = 0)

Thanks for the reply. It's ok I can manage the access syntax and yes I want for the vehicles to have aa oil change for every 10,000 mileage from their previous maintenance not the predefined one. Question where did you get the
LastServiced = DATEADD(Month,-1, GETDATE())?
and what does the % sign do in
(Mileage % 10000 = 0)?
Sorry my knowledge on some SQL syntax are a bit fuzzy


Sorry again it's my lack of knowledge on Access syntax...These would work in SQL server

Basically DATEADD function adds a given date interval to a date. In this case I'm adding -1 Month to the current date (GETDATE is a SQL Server function to get the current Date) in otherwords, I'm getting last months date (remember if your in January the last month was the December before).

OK now to Mileage % 10000 =0 this is the equivalent of the VB Mileage Mod 10000 =0 in otherwords the modulus of mileage/ 10000 =0 i.e. the Mileage is an exact multiple of 10000. I just realised as I type this, I should also have put AND Mileage > 0 in as 0 divided by any number will always give zero.

So what you want is something like:

WHERE (LastServiced = DATEADD(Month,-1, GETDATE())) 
(Mileage % 10000 = 0 AND  Mileage > 0)

That will get all vehicles that were last serviced 1 month ago or that have a Mileage that is a multiple of 10,000

% is modulus in sql. I'll Remember that. By the way problem solved not on the project I was refering in my first post but on my other project thanks anyway

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.