Blanks

Blanks

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
 
 
 

Blanks

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

 
 
 

Blanks

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

Type an expression in the field row of an empty column:
Nz([YourField],"N/A")

--
Joan Wild
Microsoft Access MVP
 
 
 

Blanks

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.

Keith.
www.keithwilby.com
 
 
 

Blanks

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)
 
 
 

Blanks

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

Keith,
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
 
 
 

Blanks

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


Duly noted. Thanks Dave.