Append query dropping a field

[Guest]

I have an append query that is dropping an entire field. It says it has "set
to null due to a type conversion." The query has 4 fields in it that match
the table names. The four fields are all number fields in the tables they
come from that are lookup fields that return a text value. Three of the
fields append just fine, but the fourth one does not. I've gone over and
over the original lookups in the tables they come from - they're all set the
same way. The SQL code is:

INSERT INTO ScheduleTbl_Test ( WorkInstr, Function, ProcessID, Manager )
SELECT WorkInstrTbl.WorkInstruction AS WorkInstr, ProcessTbl.Function,
WorkInstrTbl.Process AS ProcessID, FunctionTbl.Manager
FROM FunctionTbl RIGHT JOIN (ProcessTbl RIGHT JOIN WorkInstrTbl ON
ProcessTbl.ProcessID = WorkInstrTbl.Process) ON FunctionTbl.FunctionID =
ProcessTbl.Function
WHERE (((WorkInstrTbl.Process) Is Not Null));

The failure is on the WorkInstr field.

 

[Allen Browne]

You are certain that ScheduleTbl_Test.WorkInstr and
WorkInstrTbl.WorkInstruction are both Number type fields?

I take it that you have some kind of lookup on these fields (i.e. their
DisplayControl is a combo with the bound column hidden.) We all hate those
because it masks all kinds of problems, and Access behaves inconsistently
with these (i.e. the behavior in Access 2002 and later is different than
earlier versions.) You might try changing the Column Widths of the combo to
a non-zero value (lower pane of table design, on the Lookup tab) for both
fields.

But assuming they are both Number, and both have the same Field Size
property (lower pane in table design) such as "Long Integer", it should
work. Drop the alias, i.e. change the 2nd line of your SQL statement to:
SELECT WorkInstrTbl.WorkInstruction, ProcessTbl.Function,
Also, remove any Caption property on the fields in table design, and make
sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Then compact the database:
Tools | Database Utilities | Compact/Repair
There are some very weird problems associated with the Name AutoCorrect
misfeature, where JET can wrongly identify a field that has a Caption, or an
Alias, or has had a name change. The sequence above should avoid those bugs.

 

[Guest]

Thank you so much - I'll try all of your suggestions. I didn't realize the
Access behaved inconsistently on this feature. You're correct on all your
assumptions.