Consulting

Results 1 to 5 of 5

Thread: Suppressing #Error returned as a query result

  1. #1
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location

    Suppressing #Error returned as a query result

    I have a query that evaluates a record column "Due date" and returns:

    1. The week of of the year index and ..
    2. The date of Monday of that week:

    So if I entered July 4, 2024, I get the returned values 27 and Jul 1

    However, in some record instances, the "Due date" column is not filled in. When I attempt to query under this condition

    Name Date Due
    Mary July 4, 2024
    Bob

    I get
    27 Jul 1
    #Error

    These are the two field statements:
    WeekIndex: DatePart("ww",[DueDate])
    WeekBeginDate: Format(DateAdd("d",-Weekday([DueDate])+2,[DueDate]),"mmm dd")
    So it appears if the DueDate in the record is Null then the WeekIndex statement ignore the Null value and returns a Null value but in the case of the WeekBeginDate an error occurs.

    I have worked around this problem using:

    WeekBeginDate: IIf(IsDate([DueDate]),Format(DateAdd("d",-Weekday([DueDate])+2,[DueDate]),"mmm dd"),"")
    Is there a better\more efficient way to suppress that #Error returned if the DueDate field is Null. I know there is a Nz function but unsure how to employ it.

    Thank you.
    Last edited by Aussiebear; 07-04-2024 at 01:06 PM.
    Greg

    Visit my website: http://gregmaxey.com

  2. #2
    VBAX Regular xps350's Avatar
    Joined
    Jul 2022
    Posts
    13
    Location
    Try IFF(NOT IsNull(DueDate),...............)
    Groeten,

    Peter

  3. #3
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    372
    Location
    The function in Access is IIf, not IFF.

    Error is triggered by DateAdd function getting Null value in first date. Try: -
    Weekday(Nz([DueDate],Date()))+2
    The IIf() should not be needed if Nz() is used.

    I don't allow empty string in fields. I would return Null instead.
    Last edited by Aussiebear; 07-04-2024 at 01:06 PM.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    another alternative:

    WeekBeginDate: Format(IIf(IsNull([DueDate]),Null,DateAdd("d",-Weekday([DueDate])+2,[DueDate])),"mmm dd")

  5. #5
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    This works:
    WeekBeginDate: DateAdd("d",Nz(-Weekday([DueDate]))+2,[DueDate])
    Thanks for all the input and help.
    Last edited by Aussiebear; 07-04-2024 at 01:07 PM.
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

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