How can I replace a null value with zero in a db table? 1

[rlatham]

Hello, I would appreciate any help.
What function or code do I need to put zeroes where there is a field is null? I tried UPDATE in a CFQUERY, WHERE the fields where null, also WHERE len(field)=0, none of these worked for me...
any suggestions?
thanks.

 

[KevinFSI]

I don't know how useful this will be to you, but I had to do the exact same thing with spreadsheet info I was importing from Excel. I tried and tried to do it with a query, but ended up doing it in Excel before importing it. I used VBA like so:

Columns("C:E").Select    
Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False

This works for me. I think the exact same code can be written as VBA for Access as well. Kevin
slanek@ssd.fsi.com

 

[KevinFSI]

I found this too. It is not my own:

Nz Function Example

The following example evaluates a control on a form and returns one of two strings based on the control's value. If the value of the control is Null, the procedure uses the Nz function to convert a Null value to a zero-length string.

Sub CheckValue()
    Dim frm As Form, ctl As Control
    Dim varResult As Variant

    ' Return Form object variable pointing to Orders form.
    Set frm = Forms!Orders
    ' Return Control object variable pointing to ShipRegion.
    Set ctl = frm!ShipRegion
    ' Choose result based on value of control.
    varResult = IIf(Nz(ctl.Value) = "", _
        "No value", "Value is " & ctl.Value)
    ' Display result.
    MsgBox varResult
End Sub

Kevin
slanek@ssd.fsi.com

 

[rlatham]

Thank you so much KevinFSI , I will try both of your suggestions.

 

[KevinFSI]

No sweat! If you find some other way of doing it please let me know too.
Thanks,
Kevin
slanek@ssd.fsi.com