IIF "Add-On"

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?

Try

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Sparky Arbuckle" wrote in message
ups.com...
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?

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.

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?

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.

--
George Nicholson

Remove 'Junk' from return address.


"Sparky Arbuckle" wrote in message
oups.com...
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.

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