1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

form formula in Access 2003

Discussion in 'Business Applications' started by jamesb2, Oct 16, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. jamesb2

    jamesb2 Thread Starter

    Joined:
    Dec 8, 2007
    Messages:
    124
    I have a form in an access database. This form tells us when our employees have taken their year tests and when the next one is due. This testing is yearly. I have a form that will show:
    1) the name of the employee
    2) when the test was last taken
    3) when the next test should be taken
    4) if their testing status is "expired" due to missing a test date

    the problem is that 4th colum is not caculating exipired correctly. We have employees that have taken their test this year and colum 3 does state the correct next test for next year yet, they are showing as "expired" as if they had missed this years test. Here is the formula for the "status" colum:

    =IIf([next test]<Now(),"EXPIRED!",Null)


    Any advice or help would be appriciated....
     
  2. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Just a guess here, but you may have a date formatting problem. Now() function shows date/time, what format is your next test date?

    You may want to ensure that you are comparing like date formats.

    MBN
     
  3. jamesb2

    jamesb2 Thread Starter

    Joined:
    Dec 8, 2007
    Messages:
    124
    Just a guess here, but you may have a date formatting problem. Now() function shows date/time, what format is your next test date? MM/YY

    You may want to ensure that you are comparing like date formats. Please elaborate
     
  4. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Well, what is being displayed in the "next test" field? You say it is "MM/YY" but your if statement is comparing, for instance, 12/09 to Now() which would be something like 10/16/2008 10:44:15

    Try playing with the Now() format to make it match your "next test" field: something like this (and this is strictly off the top of my head), =IIf([next test]<format(Now(), "MM/YY"),"EXPIRED!",Null)

    Let me know.

    MBN
     
  5. jamesb2

    jamesb2 Thread Starter

    Joined:
    Dec 8, 2007
    Messages:
    124
    the code you sent me removed the "expired" column from the form :)
     
  6. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Are you saying it worked? If yes, then great!

    MBN

    PS: If so, then you may want to mark this as Solved.
     
  7. jamesb2

    jamesb2 Thread Starter

    Joined:
    Dec 8, 2007
    Messages:
    124
    what I'm saying is the column was removed completely from the form. That's not good LOL.
     
  8. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Seems unlikely?! - Very odd.

    btw. What's the difference between iif and if? Tried googling it, but no real answers.
     
  9. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    No, it is not likely, actually. Pasting in or editing the =iif statement wouldn't remove the column entirely.

    Are you aware that all I edited was wrapping your Now() in a formatting function? Where do you get the "next test" field value? Is it derived or manually input? If derived, what is the code for it?

    The issue remains, I think you are evaluating unlike date types and therefore not getting an apples-to-apples comparison.

    To answer MRdNK, the "if" is used in the VBA side, the =iif is used in the source of the field's properties, right in the form/report..... =iif(Condition, WhatToDoIfTrue, WhatToDoIfFalse).

    MBN
     
  10. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Ah Okay - thanks.
     
  11. jamesb2

    jamesb2 Thread Starter

    Joined:
    Dec 8, 2007
    Messages:
    124
    the next test field is generated based on +1 year from the test taken date entered manually at the testing center.
    "The issue remains, I think you are evaluating unlike date types and therefore not getting an apples-to-apples comparison." How can I verify this?
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/759744

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice