IIF "Add-On"

[Sparky Arbuckle]

So after reading documenation and Usenet help replies this morning I
have concluded that the IIf function in Access is not going to help
with my current situation. If I understand correctly, IIf is only used
when and only when there is a field that could be null. For instance,
if there are work phone, home phone, cell phone fields in a database
and someone only has a cell phone, etc... Let's say you have a product
in tblProducts and revisions in tblRevisions. The products table has a
RecordID that ties that table to tblRevisions.

Some products have Revisions and are listed in tblRevisions while
others do not. tblRevisions has a RevNumber field that is an integer. I
retrieve this data and then set a variable intRev = to:

ds.Tables("DataTable").Rows(0)("RevNumber")

Upon doing this I get an error at runtime that reads: Cast from type
'DBNull' to type 'Integer' is not valid.

So, I guess my question is this: Is it possible to create an instance
of RevNumber with a value of 0 while executing a query if that
RevNumber / 0 doesn't exist?

 

[Douglas J. Steele]

Try

Nz(ds.Tables("DataTable").Rows(0)("RevNumber"),0)

 

[Sparky Arbuckle]

Thanks Doug. I am actually using ASP.NET and believe that NZ is VB6
function. At least I know what to start looking into. Worst case
scenario I could create a .NET function that is equivalent to NZ.

 

[Sparky Arbuckle]

My bad. I read somewhere that it was a VB6 function. Maybe it is.
Anyway, how would I incorporate it into a SQL string? Or do I need to
create some VBA?

 

[George Nicholson]

Actually, NZ is an Access function (available within Form, Report and Query
design as well as VBA). If it were VB6, it would at least be available in
Excel VBA and it isn't.

 

[Sparky Arbuckle]

I fixed it!

Dim intRevNumber As Integer

If ds.Tables("DataTable").Rows(0)("RevNumber") Is DBNull.Value Then
intRevNumber = 0
Else
intRevNumber = ds.Tables("DataTable").Rows(0)("RevNumber")
End If

Return ds.Tables("DataTable").DefaultView