How can I fix Append Queries?

[bkleve]

Hi all,


I have a database with 4 append queries. I import data to a data table, then the append query is written to pull anything from that table that has a null value in the specified column. Currently 1 of the 4 queries is not working but the code and for the one that is no longer working looks exactly like the other 3 that are working. I will add that all 4 were working until last week. The data is still importing to the correct table and the query is set to pull from that table but when I append it says there are 0 records. I have gone section by section to make sure the all fields are labeled the same as they are listed in the code. Any suggestions of what else to look at to correct this query? An example of the code is listed below,

INSERT INTO TableName (field names on this table that data will be imported into)
SELECT TableName.fieldname, TableName.fieldname
FROM TableName
WHERE (((TableName.fieldname) is Null));

[pbaldy]

It should also give a hint as to why it's going to append 0 records. Perhaps key violations? If so, something in the incoming data is violating something in the table. Examples could include duplicating a key field or an indexed combination, leaving a required field blank, etc.

[bkleve]

pBaldy Thank you for your reply, I have checked the data and the key field has not been duplicated (the data is only there once), and the field requiring the null value is null, etc. This is what the message says, "You are about to append 0 row(s). Once you click yes, you can't use the undo command..." (what the message should say minus the 0 rows to append, but gives me no indication of why there are 0 rows. My apologies I should have included that information in the original post.

[ssanfu]

It really helps to have the actual SQL....

Try changing the append query to a select query and see how many records are selected.

WHERE (((TableName.fieldname) is Null));

Are you sure the field ACTUALLY has NULL values?? Maybe they are empty strings.