Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using an IIF statement with an ADO Recordset? 1

Status
Not open for further replies.

mrdod

Technical User
Jun 12, 2006
103
US
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
 
You may try this:
Code:
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
 
That works PH thanks a bunch.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top