Null Value

  • Thread starter Thread starter John V
  • Start date Start date
J

John V

I have a question regarding Null values in a table. In my table there will
be a field that contains several blanks. When I run my query I would like
all of the empty fields in this record replaced with a text value. What's
the best way to do this - preferrably without having to use VBA?

Thank you!
 
Run this 1st - just to check
Result: IIf(IsNull([tablename]![FieldName]),"Some text")

If the results are what you want
Run a update querry - put IsNull in the critiera row and
"SomeText" in the UpdateTo row

good luck
 
Nz() function, perhaps.

--
HTH
Van T. Dinh
MVP (Access)



Wayne-I-M said:
Run this 1st - just to check
Result: IIf(IsNull([tablename]![FieldName]),"Some text")

If the results are what you want
Run a update querry - put IsNull in the critiera row and
"SomeText" in the UpdateTo row

good luck

--
Wayne
Manchester, England.



John V said:
I have a question regarding Null values in a table. In my table there
will
be a field that contains several blanks. When I run my query I would
like
all of the empty fields in this record replaced with a text value.
What's
the best way to do this - preferrably without having to use VBA?

Thank you!
Click to expand...
Click to expand...
 
Depending on how you set up the Text Field in the Table (the Properties
"Required" and "AllowZeroLength"), you may have both Null and zero-length
String as values of this Field and both Null / ZLS appear the same to us.

To trap for both, use the condition:

Len([YourField] & "") = 0

Thus the SQL String of your Update Query should be something like:

UPDATE [YourTable]
SET [YourField] = "{NewValueHere}"
WHERE Len([YourField] & "") = 0
 
Back
Top