IIF "Add-On"

  • Thread starter Sparky Arbuckle
  • Start date
S

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?
 
S

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.
 
S

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?
 
G

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.
 
S

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
 
Top