1. Registered User
Join Date
Feb 2005
Posts
3

Hy guys i was wondering if it is possible to increase a data field which is an integer in access by a valuee 1 for everyday, then run a query which divides that value by another certain value per day, without anyy steep vb coding, cheers guys.

2. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
Your question is kind of vague...

Maybe you could use the DateDiff function to determine the number to add
to the value. That way, you wouldn't have to change the value.

Say the value in field3 was 274, and it was put there on Jan 23, 2005
On Feb 18, 2005, if you had added 1 to the 274 every day, it would now be 300. You could just use a formula like this in your query:
field3+DateDiff(d,#1/23/2005#,#2/18/2005#)

3. Registered User
Join Date
Feb 2005
Posts
3
What i'm trying to do is track how long a car has been in the db for in days, so i was thinking of a field which would track the products life in days updating everyday, then i would get someone to run a query everyday run which checks if the car life when divided by aprox 6 months is an integer. Any better way of doing this??? cheers

4. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
you should not store your calculated days age in a table - it is madness to update the table daily and then query it as you propose.

you can get days age with a query anytime you want it assuming your table knows when the thing went into the db with a field dateIn.
SELECT datediff("d", dateIn, Now()) AS daysOld FROM myTable;

cars life in days divided by approx six months = integer ??
what are you trying to do?

SELECT int(datediff("m", dateIn, Now())/6) AS mosOld FROM myTable;

but note that datediff("m".... considers only the month. i.e.
1st Jan to 28th Feb = 1 month
31st Jan to 28th Feb = 1 month

if you want completed "approx 6 mos" blocks, try:
SELECT int(datediff("d", dateIn, Now())/183) AS blox6mo FROM myTable;

izy

5. Registered User
Join Date
Feb 2005
Posts
3
Hi izy the reason why i wanted cars life in days divided by approx six months = integer is because it would mean 6 months had gone by which meant it was time for that car to have a maintenance check. And i suggested aprox date because i thought it would be easier to use a constant then deriving 6 months from a calender whose months had variable days, cheers izy.

6. Registered User
Join Date
Feb 2005
Posts
19
Personally I'd just look for a date difference =>180 (or whatever other period is required) and, if true, carry out some related action.

7. Registered User
Join Date
Feb 2005
Location
The Hague, Netherlands
Posts
95
I'd be carefull to run some sort of continuous code, could cost you some CPU-power you dont want.

Does the system start up every morning or so or does it keep on running at all times (server)? If you start your system everyday you could do a check (like some others in this thread suggest) at startup by putting your code in the Form_Load section, that way a list would be generated every morning.

8. Registered User
Join Date
Sep 2004
Location
Tampa, FL
Posts
520
Noctos, as Izyrider stated it is poor practice to store a calculated/derived value in your DB. Once you have the date in that should suffice. Your post stated that you intended running a query daily, have that query determine when the check needs to be.

That said why not add a field that stores when the last maintanance check was performed. Run your datediff from that field (day or months wichever you prefer.) Now you know not only that a check is due but if t was performed.

My 2 cents worth.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•