Using NZ function & Allow Zero Length

[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?

 

[Michel Walsh]

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

 

[john acocella]

Did you try playing with the isnull function in your query?

 

[John Spencer (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