Results 1 to 8 of 8

Thread: Wonderinf IF...

  1. #1
    Join Date
    Feb 2005
    Posts
    3

    Unanswered: Wonderinf IF...

    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. #2
    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#)
    Inspiration Through Fermentation

  3. #3
    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. #4
    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?

    how about:
    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
    currently using SS 2008R2

  5. #5
    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. #6
    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. #7
    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. #8
    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.
    Darasen

Posting Permissions

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