Using an IIF statement with an ADO Recordset? 1

[mrdod]

I am tring to look up/retrieve information information stored in an Access database. Everything is fine until the recordset returns a null value. I am trying to use an IIF statement but I keep getting an error saying "Either BOF or EOF is true or the current record has been deleted. Requested operation requires a current record." Here is my code. When I do this in Access I use "NZ" is there something similiar in Excel? Any help would be great!!

Right now I can get it to work using the On Error Resume Next statement but would really like to understand what I'm missing!

Thanks!!


For Each cell In MyRange
cell.Activate
strmodel = ActiveCell.Value
strSQL = "SELECT [Model Code] FROM tblnomenclature WHERE([Material]='" & strmodel & "');"
Set rst = cnt.Execute(strSQL)
Cells(r, 22) = IIf(IsNull(rst(0)), "", Right(rst(0), 3))
r = r + 1
Next cell

 

[PHV]

You may try this:

For Each cell In MyRange
  strmodel = cell.Value
  strSQL = "SELECT [Model Code] FROM tblnomenclature WHERE Material='" & strmodel & "'"
  Set rst = cnt.Execute(strSQL)
  If Not (rst.BOF Or rst.EOF) Then
    Cells(r, 22) = IIf(IsNull(rst(0)), "", Right(rst(0), 3))
    r = r + 1
  End If
  rst.Close
  Set rst = Nothing
Next cell

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrdod]

That works PH thanks a bunch.