query issue with #error

[dubsdj]

Hi there, I'm trying to write a query that will display "NA" if there is no End Time but if there is an end time then display it as normal. The problem I'm having is that if there is no End Time then I get an #ERROR appear in the record. Here is the query:

Expr1: IIf(IsError([End_Time]),"N/A",[End_Time])

 

[Mile-O]

Unless you've written a public function called IsError then that's why it's not working. There is no IsError function in Access. (At least not in Access 2002, which is what I use. I have no idea about later versions.)

 

[dubsdj]

Ok so if I have a null value.. I think its just a null value..

 

[Mile-O]

The IsNull() function.

 

[dubsdj]

I tried this:

Expr1: IIf(IsNULL([End_Time]),"N/A",[End_Time])


but still the field shows up as #error if the End Time is an empty value

 

[dubsdj]

oh and is that Manny from grim fandango ? I loved the game

 

[Mile-O]

Yes, it's Manny from Grim Fandango.

But, regarding your problem, are you able to knock up a quick example of your problem and upload it?

 

[dubsdj]

Expr1: IIf(IsNULL([End_Time]),"N/A",[End_Time])


Thats the statement I'm trying to run but it doesn't seem to work. Still an #Error value appears in the column.

Basically End_Time is just a couple of different strings mashed together. But the problem is that sometimes these strings are blank because they are optional and when it tries to display the value when its blank, then I recieve the #Error

 

[Mile-O]

Expr1: IIf(IsNULL([End_Time]),"N/A",[End_Time])

Basically End_Time is just a couple of different strings mashed together.

Ah, then that may be the problem. There's a difference between a Nullstring and Null. Null is nothing at all, whereas a Nullstring is "".

Try:

Expr1: IIf([End_Time]="","N/A",[End_Time])

And change Expr1 to something more understandable. It will help in the long run.

 

[dubsdj]

Thanks for your help, I'll give that a try

 

[dubsdj]

Hi that displayed a valid time if and end time was specified but if there was no end time it still comes up with #Error

 

[neileg]

Expr1: IIf(Nz([End_Time],"")="","N/A",[End_Time])

may work.

 

[Mile-O]

Oh, I'd forgotten all about the Nz() function. Cheers for the reminder, neileg.

 

[dubsdj]

No Joy

Now it doesn't even display the end time in Expr1 if a time was specified. Still just the #Error when end time is empty

 

[neileg]

Works fine for me.

 

[Mile-O]

Is it possible that your database is corrupt? Compact and Repair it.