Post by UkFK » Thu, 07 Jun 2007 23:10:01

Can someone help me...I am working in design view in a query...I have some
blanks in some of my data and I would like to enter an If statement saying
that if the field is blank, then N/A. Thx


Post by UkFK » Thu, 07 Jun 2007 23:21:02

In Design View, I am trying to enter an IF statement to say, If the field is
blank, enter N/A

Can someone tell me how to do this. Thanks



Post by Joan Wil » Thu, 07 Jun 2007 23:44:31

Type an expression in the field row of an empty column:

Joan Wild
Microsoft Access MVP


Post by Keith Wilb » Fri, 08 Jun 2007 00:01:04

Iif(Nz([FieldName],"") = "","N/A",[FieldName])

The Nz function converts nulls to zero-length strings and the Iif function
looks for them and replaces them with N/A.



Post by Klatu » Fri, 08 Jun 2007 04:37:02

You can do this using a calculated field in your query. If you are using
the query builder, you give it a name followed by a colon, then an IIf
statement to do the work:

FooBah: IIf(Len(Nz([SomeField],"") = 0, "N/A", [SomeField)


Post by Klatu » Fri, 08 Jun 2007 08:02:47

According to the Access Developer's Handbook, (Litwin, Getz, Gunderloy), it
is faster to test for 0 length using the Len function than it is to test for
= "".
Just FYI

Dave Hargis - MVP Access


Post by Keith Wilb » Tue, 12 Jun 2007 16:36:00

Duly noted. Thanks Dave.