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