Append Query Won't Work

[Keith Willcocks]

This is silly, I have done this sort of thing many times in the far off past
but now cannot get it to work.

I have two tables tblSerialNos and tblMain. I want to append serial
numbers from field fldStrSerialNumber in tblMain to the same named field in
tblSerialNos but only where the field fldStrAssetNumber in tblMain is empty.
I have tried umpteen versions of this in queries but every time no records
are appended even though there are three that should be. Here is the Query
SQL from my latest effort if anyone will take pity on my brick wall that is
getting a hammering from my head.

INSERT INTO tblSerialNos (fldStrSerialNumber)
SELECT tblMain.fldStrSerialNumber
FROM tblSerialNos, tblMain
WHERE (([tblMain].[fldStrAssetNumber]=""));

I have tried WHERE ISNULL and LEN(Rtrim(Ltrim())) as well as ="" but nowt
seems to hack it.

 

[Guest]

Try this --
INSERT INTO tblSerialNos ( fldStrSerialNumber )
SELECT tblMain.fldStrSerialNumber
FROM tblMain LEFT JOIN tblSerialNos ON tblMain.fldStrSerialNumber =
tblSerialNos.fldStrSerialNumber
WHERE (((tblSerialNos.fldStrSerialNumber) Is Null));

 

[Keith Willcocks]

That's brilliant. You put me right back on track.
Many thanks.

Keith

Try this --
INSERT INTO tblSerialNos ( fldStrSerialNumber )
SELECT tblMain.fldStrSerialNumber
FROM tblMain LEFT JOIN tblSerialNos ON tblMain.fldStrSerialNumber =
tblSerialNos.fldStrSerialNumber
WHERE (((tblSerialNos.fldStrSerialNumber) Is Null));

--
KARL DEWEY
Build a little - Test a little

This is silly, I have done this sort of thing many times in the far off
past
but now cannot get it to work.

I have two tables tblSerialNos and tblMain. I want to append serial
numbers from field fldStrSerialNumber in tblMain to the same named field
in
tblSerialNos but only where the field fldStrAssetNumber in tblMain is
empty.
I have tried umpteen versions of this in queries but every time no
records
are appended even though there are three that should be. Here is the
Query
SQL from my latest effort if anyone will take pity on my brick wall that
is
getting a hammering from my head.

INSERT INTO tblSerialNos (fldStrSerialNumber)
SELECT tblMain.fldStrSerialNumber
FROM tblSerialNos, tblMain
WHERE (([tblMain].[fldStrAssetNumber]=""));

I have tried WHERE ISNULL and LEN(Rtrim(Ltrim())) as well as ="" but nowt
seems to hack it.