Blanks

[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

 

[Joan Wild]

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

 

[Keith Wilby]

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

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

 

[Klatuu]

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)

 

[Klatuu]

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

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

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

 

[Keith Wilby]

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

Duly noted. Thanks Dave.