Monthly Routine Loop in MS Access

[jared.geli]

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:
DateFile
Plate
Driver
EmpID
OdometerBefore - mileage I'm talking about
OdometerAfter - mileage I'm talking about
Gas
CostofGas

[G_Waddell]

Hi,

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. :

SELECT DISTINCT * FROM Vehicles  
WHERE (LastServiced = DATEADD(Month,-1, GETDATE())) 
OR
(Mileage % 10000 = 0)

[jared.geli]

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

[G_Waddell]

Hi,

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:

SELECT DISTINCT * FROM Vehicles  
WHERE (LastServiced = DATEADD(Month,-1, GETDATE())) 
OR
(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

[jared.geli]

% 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