Set Number Field = Null or Zero Length 1

[PatrioticTX]

In the process of saving data to a recordset from VB6, I need to store a blank in a Number (Single) field of an Access table. I do not want to store a '0' value, it must be completely blank. When I try to do this, I get a Data Type Conversion Error. I have tried setting the recordset field to both vbNullString and Null using VB, but neither work.

If I am using Access directly, all I have to do is clear the field and it works. It also works when I run an Access Update Query and set the field value to null.

Executing an update SQL from VB is too inefficient. How can I do this in VB? Thank you for your help!

 

[Sypher2]

Hmmm... Have you tried the double quote?

recordset field = ""

 

[PatrioticTX]

Yes. That didn't work either. Sometimes VB can really be a pain.

 

[SuperComputerRobot]

Try this mate

recordset field = Empty

Empty:
Variant is uninitialized. Value is 0 for numeric variables or a zero-length string ("&quot for string variables.


-------------------------------------------------------
Visual Source Programming

http://visualsourceprogramming.vze.com/

 

[PatrioticTX]

If I used the Empty variable, I would have to change all my data types to
variants. Unfortunately, that is not an option. The variable must remain a

single

data type.

Further elaboration:
A field on form frmXYZ called [FIELD_ABC] has nothing in it (it is blank).
I am using a recordset to update all fields on the form, one being
[FIELD_ABC]. A generic example of the code is:

Rs.Edit
Rs!MySingle = Me!FIELD_ABC
Rs.Update

When I run the code to update the recordset, Error 3421 (Data Type
Conversion Error) occurs. As expected, it will save any

single

number I enter, but it will not store an empty space. Access easily does
it... I haven't been able to get VB6 to do it. Please help!

 

[MrEGuest]

Try inserting a statement like this:

Rs!MySingle = IIf(Me!FIELD_ABC.Text = "", Null, Me!FIELD_ABC.Text)

I've had no problem assigning VB's Null keyword to Access fields that are of type Number->Single.

Hope this helps!
Josh

 

[PatrioticTX]

As I mentioned in my first post, I tried to set the field equal to Null. I was unsuccessful a few days ago. I tried what you said and it worked great. I believe my error was occuring because I used a function call to assign Null to the variable if it saw that the textbox was equal to vbNullString. I had set the function output data type to Single instead of variant. I have since decided put the code inline (without IIf) to improve efficiency. Thank you for your help.