Problem with Variant data type

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I am getting an error message without a detailed explanation and can
find nothing in "Help" that will help.

When I run a query which is to append records to an existing table I
get the error message:

" You tried to assign a Null value to a variable that is not a variant
datatype."

The new data is imported from the work of others and includes 12,000
records with 15 fields each. The system has been running without a
problem for three years and is updated weekly.

Where do I look for this problem?

Thanks,

Robin Chapple
 
Robin, If you are using variables in your code to set the values of the
imported data to and that data has null values, the only variable that
excepts a null value is a variant. So in your dim statement of the
variable, if there's changes the imported data will have nulls make sure to
use the variant data type

Dim vMyVariable as Variant

You may not have been having a problem before, because maybe this value was
always provided. Check the data you are importing to see if there are now
blanks where there used to be values. Hope this helps!
 
Reggie,

Thanks for the reply.

This is a simple membership database that I need to consult for
membership details.

There is no code.

I use a delete query to empty the existing table,

I import a comma delimited text file, use a query to change "Field1"
into "Surname" for instance and then append to the existing table.

Where do I go now?

Robin
 
Normally I see this when I import a text file to a temp table and all these
fields are text. I then append data to my tables and the data types for
these fields may be different. for example if I try appending a10104 to a
date field when in fact it should be 010104. My suggestion, though somewhat
tedious, would be to have a look at the file you are importing to see if all
the values are in the right place and in the right order etc. Hope this
helps!
 
Back
Top