Return 0 when value is Null

[0 1]

In the following code, how can I return 0 if EncTypeID is Null?

For example, when VehTypeID is Null, I'd like sSubForm = 0 rather than = rst("VehicleFrm").

I tried using the Nz function put couldn't figure it out.

CODE:

Set dbs = CurrentDb()
strSql = "SELECT VehicleFrm FROM tblVehicleTypes Where VehTypeID=" & VehTypeIDF.Value
Set rst = dbs.OpenRecordset(strSql)
sSubForm = rst("VehicleFrm")

Thank you.

 

[John W. Vinson]

Set dbs = CurrentDb()
strSql = "SELECT VehicleFrm FROM tblVehicleTypes Where VehTypeID=" & VehTypeIDF.Value
Set rst = dbs.OpenRecordset(strSql)
sSubForm = rst("VehicleFrm")

I'd just use an IF block:

Set dbs = CurrentDb()
If IsNull(Me!VehTypeIDF) Then
sSubForm = 0
Else
strSql = "SELECT VehicleFrm FROM tblVehicleTypes Where VehTypeID=" & _
Me!VehTypeIDF
Set rst = dbs.OpenRecordset(strSql)
sSubForm = rst("VehicleFrm")
rst.Close <if you're not closing it after some other code>
End If
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com