Avoiding #ERROR without using NZ()?

[Sketchin]

I have a field that calculates a date range from a record in my query.

Date: Format([TransactionDate],"yyyy") & "-" & Format([transactionDate],"yy")+1

My problem is that if TransactionDate is blank, it returns #ERROR. I am linking this query to Excel, which means I cannot use the NZ() function to return a null value. Is there any way to do this without NZ()?

 

[pbaldy]

Is the field not Null then? You can use an IIf() and test the length of the field with Len().

 

[Sketchin]

Is the field not Null then? You can use an IIf() and test the length of the field with Len().

The TransactionDate field is null in some records.

So it would look something like?:

Date: IIf(Len(Format([TransactionDate],"yyyy") & "-" & Format([transactionDate],"yy")+1)=0," ",(Format([TransactionDate],"yyyy") & "-" & Format([transactionDate],"yy")+1))

In the IIF statement, how do I tell it to be NULL instead of #ERROR?

 

[Sketchin]

Here is a picture of the query as it stands.

 

[pbaldy]

More like:

Date: IIf(Len([TransactionDate])=0," ",(Format([TransactionDate],"yyyy") & "-" & Format([transactionDate],"yy")+1))

 

[Sketchin]

Still doesn't work so I created a field called TransactionDateLength: Len([TransactionDate]) and it returns either a 9 or a null, but not a zero. Think that's the problem?

 

[Sketchin]

Alright, I created the TransactionDateLength field and used the LEN() function on that one, instead of using transactiondate and it worked.

Thanks for pointing me in the right direction

 

[pbaldy]

Then I'm curious why Nz didn't work. Try

Date: IIf(Len(Nz([TransactionDate], ""))=0," ",(Format([TransactionDate],"yyyy") & "-" & Format([transactionDate],"yy")+1))

Or I suppose you could reverse it and test for 9.

 

[Sketchin]

NZ works if you are viewing the query in Access, but not if you are linking it to Excel. Excel doesn't know what to do with NZ.

 

[pbaldy]

I'm looking at an Access query against a linked Excel file and it has Nz() in it. In any case, how about:

Date: IIf(Len([TransactionDate])=9, Format([TransactionDate],"yyyy") & "-" & Format([transactionDate],"yy")+1, "")

If I haven't goofed the parentheses.

 

[Sketchin]

Yes, that worked.

In my past experience, I haven't been able to get a query to show up in Excel if it is using functions that Excel doesn't understand.

 

[pbaldy]

I was looking at it in Access, so likely I misunderstood how you were using it.