Results 1 to 6 of 6
  1. #1
    Tazmaniac is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    19

    Iif statement giving error


    Hi

    I need some help please.
    I created a query with the expression DateDiff("d",[VAFDate],Now()) to work out the number of days between today and the date entered in the VAFDate field and it works perfect.

    I now need a new expression that "If [VAFStatus]=Filled" then it must work out the number of days between [VAFDate] & [VAFDateFilled] otherwise it must work out the number of days between today and [VAFDate]

    I tried the following formula but it gives me an error:

    IIf([VAFStatus]="Filled",DateDiff("d",[VAFDate],[VAFDateFilled]),DateDiff("d",[VAFDate],Now()))


    Don't now what I am doing wrong.

    Thank you in advanced!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is the error message?

    VAFStatus is a text field?

    VAFStatus seems redundant to VAFDateFilled field. If VAFDateFilled is Null then status is not filled.

    DateDiff("d", [VAFDate], IIf([VAFDateFilled] Is Null, [VAFDate], [VAFDateFilled]))

    or

    DateDiff("d", [VAFDate], Nz([VAFDateFilled], [VAFDate]))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Tazmaniac is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    19
    Thanks for the reply.

    It says something #error, sorry not infront of my pc right now.

    And the VAFStatus field is a combobox. Will this make a difference in the exspression?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I presume the combobox is bound to field named VAFStatus. What data type is that field? What is the combobox RowSource sql?

    Consider eliminating VAFStatus field and combobox as shown in example expressions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    And the VAFStatus field is a combobox. Will this make a difference in the exspression?
    If [VAFStatus] is a field on a form, and the Iif expression is in a query, then you are getting the error because the query has no idea what VAFStatus is or where to look for it.

    Include the form name in the [VAFStatus] expression like this:

    IIf(forms!formname![VAFStatus]="Filled",DateDiff("d",[VAFDate],[VAFDateFilled]),DateDiff("d",[VAFDate],Now()))
    Replace formname with the actual name of your form.

    You would have to do the same with the other fields if they are also on the form.

  6. #6
    Tazmaniac is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    19
    Thank you for the feedback! I managed to get it to work ☺

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 10-29-2014, 11:48 AM
  2. Replies: 2
    Last Post: 03-08-2014, 02:45 AM
  3. Form with VBA code giving Error
    By tgwacker in forum Access
    Replies: 1
    Last Post: 12-03-2013, 10:14 PM
  4. DCount on a Query giving #Name? error
    By Huddle in forum Access
    Replies: 9
    Last Post: 06-20-2012, 11:40 AM
  5. NoData() still giving me an error.
    By cowboy in forum Programming
    Replies: 3
    Last Post: 04-08-2010, 12:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums