2003 Append Query won't work with blanks. 1

[danilof]

I have a simple Append query, previously working in '97 that now I've converted to 2003, won't work. If any record now has a field that contains a blank ("") (they're text fields), and they all do, the record won't be appended. The 'help' implies that this should only be the case if the field I'm trying to write a 'null' to is the primary key, and that isn't the case. Can't find the solution on Technet. Any help appreciated.

Query in VBA is (tried it as an actual query as well):
strSQL = "INSERT INTO TblData SELECT tbl" & strLCN & "Updated.* FROM tbl" & strLCN & "Updated" & _
" ORDER BY Stocknumber"
DoCmd.RunSQL strSQL
(sorry if variables confuse the picture)

 

[Remou]

Have you checked that TblData allows Zero Length Strings? Unless it was deliberately set up for ZLS, it will not allow this. It may allow Nulls, this also depends on the table set-up.

 

[danilof]

Doh!

Isn't it always the simple things that catch you out (sadly not!). I was obviously too focussed on other potential issues.

You are dead right now I've set allow zero length to 'yes' it's all hunky dory.

Many thanks - there's a lesson there.