Append Query not populating one field? 1

[glxman]

I have an append query to populate a table from a couple of key reports in excel - it performs a few calculations and lookups during the import. Everything imports as it should but there is one field that does not get populated. The data shows on the query preview, but does not transfer to the table when executed. The field looks up on an open hidden form for a userID.

INSERT INTO tblKE5Ztemp(....[NetID-Imported], [Date/Time-Imported])
SELECT DISTINCT.....[Forms]![frmUSL]![txtNetID] AS [NetID-Imported], Now() AS [Date/Time-Imported]

The date/time comes in fine as well as all other data. It is only the NetID from the form that fails to import, but shows on the query preview. Any ideas?

Thanks
Richard

 

[Remou]

Do you get an import errors table? Could there be a difference in field types, text and numeric for example?

 

[glxman]

No import errors and it is just a text field - I just cannot work it out. I have another similar import that pulls the NetID info with no problems, except that does not calculate and lookup during the process. I have compared the SQL and table properties like for like and they are the same. It's just strange that I can see the data in the query preview, but it just will not append.

 

[Remou]

Is the form still open when you run the query? Does it work with a make-table query, rather than an append? This would show if the problem is with the the table or the query.

 

[glxman]

Form is open when running - I even have it visible now while I troubleshoot.

Interestingly it does fail as a make table with a type conversion failure deleting those records - the new table has that field property set to "Binary"

Why would it do this?

 

[Remou]

Is it possible that you have misspelt the name of the control?

 

[glxman]

Surely if I had I would not get the value returned in the query preview?

It appears that it reads it as a Binary value, tries to assign it to a text field in the destination table, and ultimately fails leaving it null. The fact that the make table query assigns the field as Binary indicates this to be the case.

 

[Remou]

I tested with a correct form name and an incorrect control name and a binary is exactly what I got, hence the question.

 

[glxman]

I have another append query doing this now aswell - reading the same control. I do get the type conversion failure warning with append and make table - I had warnings off before.

It is a pain as I really need to log this information.

 

[Remou]

Have you have a chance to check the name of the control?

 

[dhookom]

I would try set the data type of the control in the Query->Parameters.

Duane
Hook'D on Access
MS Access MVP

 

[glxman]

Duane, I thought it was only naughty crosstabs that required that old chestnut - problem solved - thanks very much!

Remou, thanks for your help - I bet you're kicking yourself aswell

 

[dhookom]

This was mostly an experienced guess on my part. Your results and error messages seemed to be pointing to not understanding a data type.

You might have been able to use something like:
Int([Forms]![frmUSL]![txtNetID])
or
CStr([Forms]![frmUSL]![txtNetID])

Just another WAG.


Duane
Hook'D on Access
MS Access MVP