Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    175

    Unanswered: Using IF function in Access-in a table

    Hello.

    I am looking for a way to calculate an "if" function using Access in one of my tables.

    More specifically; I have a field that users will enter a date in which a driver license update is due. I also have a field that calculates today's date automatically. I want a field that can use the if function to determine if the update is due.

    For example: =if([specified field]>=today's date, "yes", "no")

    Here's what I tried to enter into the default value box....=IIf(Drivers License Expiration Date>=Today's Date,"Yes", "No")....to this end, I am receiving invalid sytax errors.

    I am self taught, so try not to use big words..lol

    Thanks in advance!

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    You're not specifying the name of the fields you're trying to use in your arithmatic.

    When you enter the properties of the fields, its name will be the top one (under the 'All' tab), titled: 'Name'...

    If your (text?)fields are displaying "Drivers License Expiration Date", then in your IIF statement you'll need to have:

    Code:
    =IIf(Drivers_License_Expiration_Date>=Today's Date,"Yes", "No")
    Failing that you'll need to change it to whatever your (text?)field is called.


    You also need to 'cast' (change its datatype) to be recognised as a date, instead of a string (some text), which it is being perceived to be at the moment. So you'll want to change it to:

    Code:
    =IIf(#Drivers_License_Expiration_Date#>=Today's Date,"Yes", "No")

    Also, you can use an Access built in Date() function to find todays date. So something like:

    Code:
    =IIf(#Drivers_License_Expiration_Date#>=Date(),"Yes", "No")

    Let me know how you get on... That should work fine, but feel free to ask if you have anymore queries.
    Looking for the perfect beer...

  3. #3
    Join Date
    Oct 2010
    Posts
    175
    I tried the second coding you suggested and am receiving an error message stating that the expression has an invalid date value.

  4. #4
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    The function won't work if any of the expiration date is blank. Use the Nz function with a default date to handle blank/nulls:

    =IIf(Nz(#Drivers_License_Expiration_Date#,#1/1/1900#)>=Date(),"Yes", "No")

  5. #5
    Join Date
    Oct 2010
    Posts
    175
    I don't understand. The expiration date will be found in a separate field.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In Access you're very limited in what you can enter for the default value in a table definition. As far as I know, you can't use a reference to another column of the same table or of another table, nor can you use any function you want: custom functions are forbidden and only a subset of built-in functions is allowed.
    Have a nice day!

  7. #7
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Sinndho makes a good point.

    I try to avoid the default value field like a mofo...

    You'll have to write some VBA in that case, which won't be really any different to what you've already been given, you'll just have to put it in an After Update or Change event for the 'Drivers_License_Expiration_Date' textbox field.
    Looking for the perfect beer...

Posting Permissions

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