Using NZ function & Allow Zero Length

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

I am trying to run an append query that selects all the
rows from one table and inserts them into another table
but changes any NULL values to a text string, e.g.
NO_NAME. The fields I am selecting from have Allow Zero
Length set to YES and when I try to use the NZ function, I
am getting rows rejected with a validation rule error. Is
there any way around this?
 
Hi,


A NULL value satisfies a data relation integrity, but "no_name" does
not, by default; either keep the NULL as it is (and format it when you
present it in a form or in a report, but as "DATA", keep it as a NULL),
either add "no_name" in the reference table.

In fact, NULL already requires some extra attention in queries, why add
even more extra work to handle another special case: "no_name". It seems
preferable to keep the NULL as data in the table until time is required to
"present" it to the end user.



Hoping it may help,
Vanderghast, Access MVP
 
Sounds as if you need to test for null or zero-length string in the source field.

UNTESTED SAMPLE SQL

INSERT INTO TargetTable (FieldPrimary, FieldNEW)
SELECT SourceTable.FieldPrimary,
IIF(Len(FieldOld & "") > 0,FieldOld,"No_Name")
FROM SourceTable

OR


INSERT INTO TargetTable (FieldPrimary, FieldNEW)
SELECT SourceTable.FieldPrimary,
IIF(FieldOld = "" OR FieldOld Is Null,"No_Name",FieldOld)
FROM SourceTable
 
Back
Top