# Error Returned for Date

[Jlayne]

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,Requisition.[Requisitioning Date])

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

Any assistence is appreciated.
jason

[Dan2kx]

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

[RuralGuy]

Your code is equivalent to just returning the field.

[Stewart Ross]

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

[Jlayne]

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

[RuralGuy]

Glad to hear you got it sorted.