Append to SQL table failing on data type

[tpcervelo]

I am trying to append records to a SQL table from an Access table. Not all fields will have data in every record...some fields may be blank. I get the following error when running the query. "You tried to assign the Null value to a variable that is not a Variant data type." By process of elimination the fields that are causing the error are of type (Varchar(nn), not null) in the SQL table. I have these fields defined as Text in the access table. Any idea on how I can get the records loaded for these type fields when thry are blank.
Any help is much appreciated.

[boblarson]

You must provide SOMETHING to SQL Server for that field if it is set to not allow nulls. I don't know your data to know what you could put there but that is the solution. Pass something to it to store in that field (those fields).

[tpcervelo]

Thanks for the pointer...it lead me to try two methods...both which seem to work. I could run an update query against my access table updating all null fields of that type to Space(1) or I could put IsNotNull in the criteria for those fields in the append query.