trouble with single datatype where null is a legitimate value

[ktchan]

I am using disconnected ADO recordset to populate a custom object and an unbound form.

In the table, I am storing a number of related field values in single data type sngTask1 to sngTask15. Valid data are Null, 0, 0.5, 1

Using custom class clsTask to Get and Let the values for the table:

Dim Private aryTaskVal() As Variant

Public Property Get Task() As Variant
    Task = aryTaskVal
End Property

Public Property Let Task(Values As Variant)
    aryTaskVal = Values
End Property

To populate the object from recordset, I use

For intCounter = intLbound to intUbound
    .
'strFieldName strore values of "sngTask1" to "sngTask15" when go through the For loop
    .
    ary(intCounter) = rstTask(strFieldName)
Next intCounter

Then assign value to custom object by
objTask.Task = ary 'where objTask is an instance of clsTask

To populate the object from the unbound form
I use similar loop concept

ary(intCounter) = Forms("frmTask" ).Controls(strC trlName)

then

objTask.Task = ary

When adding new record to the table, tblTask, I use SQL statement

strSQL = "INSERT INTO tblTask (sngTask1, sngTask2) Values("
strSQL = strSQL & objTask.Task(1) & ", " & objTask.Task(2) )

It works fine until objTask.Task(1) and objTask.Task(2) become Null
The INSERT INTO statement in run time become
"INSERT INTO tblTask(sngTask 1, sngTask2) Values(, ,))" and Syntax Error of INSERT INTO statement popped up.

How can I make the INSERT INTO statement work in this situation?

Thanks
Kin

[ADezii]

I am using disconnected ADO recordset to populate a custom object and an unbound form.

In the table, I am storing a number of related field values in single data type sngTask1 to sngTask15. Valid data are Null, 0, 0.5, 1

Using custom class clsTask to Get and Let the values for the table:
Dim Private aryTaskVal() As Variant

Public Property Get Task() As Variant
Task = aryTaskVal
End Property

Public Property Let Task(Values As Variant)
aryTaskVal = Values
End Property

To populate the object from recordset, I use
For intCounter = intLbound to intUbound
.
'strFieldName strore values of "sngTask1" to "sngTask15" when go through the For loop
.
ary(intCounter) = rstTask(strFiel dName)
Next intCounter

Then assign value to custom object by
objTask.Task = ary 'where objTask is an instance of clsTask

To populate the object from the unbound form
I use similar loop concept

ary(intCounter) = Forms("frmTask" ).Controls(strC trlName)

then

objTask.Task = ary

When adding new record to the table, tblTask, I use SQL statement

strSQL = "INSERT INTO tblTask (sngTask1, sngTask2) Values("
strSQL = strSQL & objTask.Task(1) & ", " & objTask.Task(2) )

It works fine until objTask.Task(1) and objTask.Task(2) become Null
The INSERT INTO statement in run time become
"INSERT INTO tblTask(sngTask 1, sngTask2) Values(, ,))" and Syntax Error of INSERT INTO statement popped up.

How can I make the INSERT INTO statement work in this situation?

Thanks
Kin

Is the Required Property of any of the Fields in tblTask set to Yes? This would not allow the entry of NULLs into these Fields.

[ktchan]

No, the field is not required, sorry I did not mention that. Also, If I Hard Code something like "INSERT INTO tblTask (sngTask1, sngTask2) Values( Null, Null) Then it work and inserted a record with Nothing in the fields of sngTask1 and sngTask2.

Thanks
Kin

[ADezii]

No, the field is not required, sorry I did not mention that. Also, If I Hard Code something like "INSERT INTO tblTask (sngTask1, sngTask2) Values( Null, Null) Then it work and inserted a record with Nothing in the fields of sngTask1 and sngTask2.

Thanks
Kin

Just for curiosity try converting any NULL Values to something less sinister by using the Nz() Function as in:

strSQL = "INSERT INTO tblTask (sngTask1, sngTask2) Values("
strSQL = strSQL & Nz(objTask.Task(1)) & ", " & Nz(objTask.Task(2))

[ktchan]

it did give me a value 0 but then I cannot differentiate between a Null or a legitimate 0.

I have tried
strSQL = strSQL & Nz(objTask.Task (1), "Null") ...
but it still give me
INSERT INTO tblTask (sngTask1, sngTask2) Values(, ,)

What else I can try?
Thanks
Kin.

[ADezii]

it did give me a value 0 but then I cannot differentiate between a Null or a legitimate 0.

I have tried
strSQL = strSQL & Nz(objTask.Task (1), "Null") ...
but it still give me
INSERT INTO tblTask (sngTask1, sngTask2) Values(, ,)

What else I can try?
Thanks
Kin.

As a Temporary Fix until you get to the root of the problem, you can always convert NULL Values to something Unique by using the 2nd Argument to the Nz() Function. In the Statement below NULLs will be converted to 0.99 in order to differentiate them from 0. Again, just a band aid.

strSQL = "INSERT INTO tblTask (sngTask1, sngTask2) Values("
strSQL = strSQL & Nz(objTask.Task(1), 0.99) & ", " & Nz(objTask.Task(2), 0.99)

[ktchan]

As a Temporary Fix until you get to the root of the problem, you can always convert NULL Values to something Unique by using the 2nd Argument to the Nz() Function. In the Statement below NULLs will be converted to 0.99 in order to differentiate them from 0. Again, just a band aid.

strSQL = "INSERT INTO tblTask (sngTask1, sngTask2) Values("
strSQL = strSQL & Nz(objTask.Task(1), 0.99) & ", " & Nz(objTask.Task(2), 0.99)

Will do that for the time being. When populating my object from the unbound form, if the text box in the form is empty, put 0.99 in the corresponding property. When clearing my object, set all properties of the array to 0.99. When populating the text boxes in the unbound form, if the property of the array is 0.99, set the text boxes to "", When inserting into the table, add the Nz with 2nd argument set to 0.99. Are there any other things I need to change to get it all right?

Thanks
Kin.

[ADezii]

Will do that for the time being. When populating my object from the unbound form, if the text box in the form is empty, put 0.99 in the corresponding property. When clearing my object, set all properties of the array to 0.99. When populating the text boxes in the unbound form, if the property of the array is 0.99, set the text boxes to "", When inserting into the table, add the Nz with 2nd argument set to 0.99. Are there any other things I need to change to get it all right?

Thanks
Kin.

That should do it until you find out why NULLs will not be accepted.