ACCESS COPY AND PASTE

[PalJoey]

I am having a problem copying and pasting records into the same table.
When I copy a record that has a zero length string for a field Access
seems to convert it to NULL when pasted. One of the fields does not
allow NULLS so I cannot paste the record. I get the error "You tried
to assign the null value to a variable that is not a variant data
type". I know which field is causing the error b/c when I change it
from a zero length string to a value the record will paste. Anyways, I
know this field value is not NULL and it would seem if you copy a
record already in the table and paste it it should meet the
validations. Anyone else experience this? TIA!!

[jimfortune]

PalJoey wrote:

I am having a problem copying and pasting records into the same table. When I copy a record that has a zero length string for a field Access
seems to convert it to NULL when pasted. One of the fields does not
allow NULLS so I cannot paste the record. I get the error "You tried
to assign the null value to a variable that is not a variant data
type". I know which field is causing the error b/c when I change it
from a zero length string to a value the record will paste. Anyways, I know this field value is not NULL and it would seem if you copy a
record already in the table and paste it it should meet the
validations. Anyone else experience this? TIA!!

I'd use an update query to do this. If there's no pattern you can even
create an auxiliary Y/N field to check the records where you want
zero-length strings to be added and use -1 for the criterion of the Y/N
field. Also, I try not to store zero-length strings unless I really
have to. The Nz function can convert a Null into a zero-length string
on-the-fly.

James A. Fortune

American forces during WWII had tremendous resources and did not need
Critical Path Analysis as much as the British who were faced with
stretching more limited resources. -- Paul Palace