Writing Access table to data to a sequel table

[bscs1963]

We recently upgraded to Sequel Server 2005. On the last version, I would write data to a table. If the field was blank, I would use an if and isnull statement using double quotes in the field so sequel thought there was something there.

In the new version, it will no longer allow me to use the double quote for the field value. I tried just entering spaces within the quotes which did not work as well. I am using VBA to write the data, and I keep getting an error when I get to the field that used the double quotes.

Is there a different way to add the blank value without changing the field properties in sequel to allow nulls?

 

[Andrzejek]

Could you show the code you use?
"I keep getting an error" - what error?
How is the field defined in your SQL Server? Does it allow NULLs?

Have fun.

---- Andy

 

[bscs1963]

The field in the sequel table does not allow nulls. I am hoping not to change this as I am not sure if that would efeect the stored procedures.

Once the stored procedures are run, the information is placed into another sequel table whcih I can then edit and actually use the double quotes.

Here is the line:

rstemp2!ShipToAddr3 = IIf(IsNull(rstemp1!ShipAddress2), "", rstemp1!ShipAddress2)

 

[Andrzejek]

So, what error are you getting?

"I tried just entering spaces within the quotes which did not work as well." and what do you mean 'did not work'? Any errors? If so, what's the error?

Have fun.

---- Andy

 

[dhookom]

You could simplify your code like:

rstemp2!ShipToAddr3 = Nz(rstemp1!ShipAddress2, "")

or

rstemp2!ShipToAddr3 = rstemp1!ShipAddress2 & ""

Does your SQL table allow zero-length-strings?

Duane
Hook'D on Access
MS Access MVP