# Error Returned for Date

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Jlayne
    New Member
    • Mar 2009
    • 4

    #1

    # Error Returned for Date

    I am joining two queries. I have the following logic to deal with "null" dates, with the hope that it will return a null value rather than #Error. To my dismay it does not seem to work.

    Here is the formula I am using:
    Requisitioning Date: IIf(Requisition .[Requisitioning Date] Is Null,Null,Requi sition.[Requisitioning Date])

    Here is the result:
    Requisition ID
    PR4289 10/21/2008
    PR6271 #Error
    PR2816 10/06/2008

    Any assistence is appreciated.
    jason
  • Dan2kx
    Contributor
    • Oct 2007
    • 365

    #2
    Code:
    IIf(Is Null(Requisition.[Requisitioning Date]),Null,Requisition.[Requisitioning Date])
    i believe the correct syntax would be (above) but i havent tested.

    but can i ask, if you want the value to display null would it not already if it was null? if so then i guess that would not work! you would need to display something else in your 'true' field,

    eg, 0 or maybe '' (empty single quote (x2))

    if this does not satisfy then you will have to wait for an expert

    Comment

    • RuralGuy
      Recognized Expert Contributor
      • Oct 2006
      • 375

      #3
      Your code is equivalent to just returning the field.

      Comment

      • Stewart Ross
        Recognized Expert Moderator Specialist
        • Feb 2008
        • 2545

        #4
        Hi Jason.

        The #Error indicates a problem with either the name of a field or the value fed to a function.

        Your original #Error message most likely results from conflicting names for the same field - you cannot have a calculated field named the same as any of the fields it is referring to within it (Requisitioning Date in this case), although I see that you have qualified the name with the tablename concerned (Requisition).

        However, as RuralGuy says, your IIF is doing no more than returning the current value for the date from Requisitioning Date, or Null if it is null. This is what a direct reference to the same field would do without using an IIF, so your IIF is not helping at all.

        Could you post the SQL for the query in which you are referring to this field, so we can see what you are trying to achieve, along with perhaps some examples to help us visualise the values more clearly?

        -Stewart

        Comment

        • Jlayne
          New Member
          • Mar 2009
          • 4

          #5
          # Error Returned for Date

          Thank you. This has been helpful and my problem is resolved.

          My original query used subqueries as its datasource. To correct my problem I changed my subqueries to "Make Table" queries, then used the newly created tables as my datasource. Simply using Tables rather than Queries as my source records corrected the problem. Is there any reason why this would make a difference?

          As for the logic: I was linking invoice data and requisition data. There can be zero to many invoices for each requistion. I should have explained further that requistion date was only one of the date fields I was capturing. The other was a "Control Date" which i wanted to equal the Requisition Date, if it existed, otherwise it would be the Invoice date.

          Thanks again for your help.
          jason

          Comment

          • RuralGuy
            Recognized Expert Contributor
            • Oct 2006
            • 375

            #6
            Glad to hear you got it sorted.

            Comment

            Working...