Blanks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
RAJ said:
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
Click to expand...

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
 
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)
 
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
Keith Wilby said:
RAJ said:
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
Click to expand...

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
Click to expand...
 
Klatuu said:
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
Click to expand...

Duly noted. Thanks Dave.
 
Back
Top