Avoiding null records in SQL 2

[SteveCarrier]

Hello,

When you have a recordset which contains null values is there a way for you to instruct the SQL to replace the null values with some other value, such as ""(an empty string?).

Right now I am using some isnull() tests in my code but it is very sloppy so I would like to improve it.

Thanks in advance.

Steve Carrier

 

[evalesthy]

Have you looked at the 'Nz' function in Help?

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

If you are trying to accomplish something else perhaps you can post a more detailed example.

 

[SteveCarrier]

I am actually looking to change the value of the null fields right in the SQL statement.

I may have, for example:
Select ProductID, ProductDesc, ProductSDesc, ProductMDesc from Product where ProductID = strProduct.

For some records ProductSDesc and/or ProductMDesc are null. This causes errors in my code unless I use an isnull() test. I wanted to avoid this by altering the SQL statement if possible.

Thanks alot.

Steve Carrier

 

[cmmrfrds]

some examples.

Select Nz(ProductID,0), Nz(ProductDesc,"my desc&quot, nz(ProductSDesc,"&quot, Nz(ProductMDesc," &quot from Product where ProductID = strProduct.

 

[SteveCarrier]

Thanks guys! Beautiful!

Steve