tranfer of record(s)

[arm1]

I am having issues transferring records using VB (actually, SQL) because of Null values within some of the record fields:

Lets say I have a record that has 5 fields. The first 2 are part of the primary key, the other three are just descriptive fields.

Within VB code, I create a string variable and to it I associate something like

"INSERT into <table> (list 5 fields) values (<v1>, <v2>, <v3>, <v4>, <v5>)"

The variables are taken from fields on the form, OR a coded, determined recordset.

The problem is that some of the fields MIGHT be Null. If they are, then when I use the string and execute the following:

DoCmd. RunSQL <sql string>

I get an error, because of the Null. If no Nulls exist, I don't get an error and it properly inserts the record.

Thanks for help on this,

- arm1

 

[rainman89]

look at the NZ function.. that might do it for you

 

[arm1]

... and how can I use the Nz funtion to copy the blanks?

Lets say the field is null... I also want to new record to have a null.

It seems in the INSERT statement, it won't accept the Null...

Nor will it accept "" or " " ???

Thanks for the help,

- arm1

 

[rainman89]

Does your table allow null values? I believe there is an option to allow nulls or not.

 

[arm1]

Yes... the table itself allows Null values (for fields that are NOT part of the primary key, of course).

The issue is in the VB code:

str1 = "INSERT into <table> (<field1> <field2> <field3>) values (" &x &", " &y &", " &z &")"

If y is Null... then executing

doCMD.RunSQL str1

gives me an error.

If field 2 is Null and I were to only use

str1 = "INSERT into <table> (<field1> <field3>) values (" &x &", " &z &")"

then it would execute properly

(even though field2 of the table is allowed to take Null values)

- arm1