The Dreaded NULL assign 0

[WineSnob]

I am using a form to provide arguments to code to fill a table. How can I assign 0(zero) to a field where it is null else use the value in the field.
Here is the field I am trying to assign.
cDIOverB1 is the variable

![Desired Income Override] = IIf(cDIOverB1 = "", 0, cDIOverB1)

 

[pr2-eugin]

Sometimes "" and Null are not exactly the same.. So instead.. try the following..

![Desired Income Override] = IIf(Len(cDIOverB1 & "")=0, 0, cDIOverB1)

 

[Alansidman]

Look at using the NZ function in any calculations you have that involve Queries or forms.

Here is a link.

http://www.techonthenet.com/access/functions/advanced/nz.php

 

[WineSnob]

Sometimes "" and Null are not exactly the same.. So instead.. try the following..

![Desired Income Override] = IIf(Len(cDIOverB1 & "")=0, 0, cDIOverB1)

This didn't work.
Not sure where to put Nz.

 

[Alansidman]

You would use the NZ in any calculation you have that fails because Access can not run a calculation on any field that is null. You would not have to update any field, instead using the NZ allows you to run the calculation telling Access to treat the null as something else. In your case a zero.

My question to you: Why else do you need a null to be replaced with a zero.

 

[mdlueck]

@WineSnob: Working with Access / VBA, I have become painfully aware of VBA supporting "different values of NULL". From my research into the issue, it appears three different values in fact.

@Others: Reasons for needing to handle NULL is that database tables are able to support NULL, simple form field controls may be empty (aka NULL), yet other than the Variant variable type, variables are unable to deal with NULL. For this reason, I assign a "NULL indicator value" for those variables and if the VBA code detects that value then VBA knows to take the logic path when setting that field's value to make sure it is set to the value which will result in NULL being the value in the DB table. Sometimes I need to support 0, so I choose another number (-1 usually in those cases). Other times 0 is not needed, so I map 0 to NULL in the VBA code.

Finally I have found useful this bit of shared code which is able to safely read form text controls ALWAYS. I was stress testing code and found bazaar situations such as "field starts out empty and is tried to be read" vs "field starts out empty, type some characters into the field, then delete/empty the field" will result in two different NULL states. One crashes Nz(), however my shared code is able to handle both UI conditions correctly.

'Generic API to safely read a Form Text Box / field control
Function uiutils_ReadFormTextBox(ByRef CtrlPointer As Object, ByVal varDefaultValue As Variant) As Variant
  On Error GoTo Err_uiutils_ReadFormTextBox

  If Len(Nz(CtrlPointer.Value, vbNullString)) = 0 Then
    uiutils_ReadFormTextBox = varDefaultValue
  Else
    uiutils_ReadFormTextBox = CtrlPointer.Value
  End If

Exit_uiutils_ReadFormTextBox:
  Exit Function

Err_uiutils_ReadFormTextBox:
  Call errorhandler_MsgBox("Module: modshared_uiutils, Function: uiutils_ReadFormTextBox()")
  uiutils_ReadFormTextBox = varDefaultValue
  Resume Exit_uiutils_ReadFormTextBox

End Function

Sample usage:

  Me.rev = uiutils_ReadFormTextBox(MePointer.fldrev, vbNullString)
  Me.ver = uiutils_ReadFormTextBox(MePointer.fldver, "-")
  Me.eau = uiutils_ReadFormTextBox(MePointer.fldeau, 0)
  Me.faver = uiutils_ReadFormTextBox(MePointer.fldfaver, -1)
  Me.faappdflg = uiutils_ReadFormTextBox(MePointer.fldfaappdflg, False)

 

[WineSnob]

Thanks Alan and Michael. I found a work around for my situation.
I'll study the code from Michael at more length.