IsNull help! No recordset Error 3265

P

philip260

Hello All.

please see below code. I am trying to run this vb code that will
return results of a query. The query may or may not have recordsets.
When the query does not return a result the script gives me a runtime
error 3265 (item not found in this collection). I know where the query
holds up but i figure the code i have should take care of the null.
Usually the 4th field in the query (rst.fields(4) does not have any
value and does not even return a result and the code breaks there. is
there any way to set the value to go around this error?? Thanks in
advance and please let me know if you need more information!


Set rst = CurrentDb.OpenRecordset("qry_Date Function Crosstab JP",
dbOpenSnapshot)
rst.MoveFirst

JPOutstandingDateTotal1 = 0
JPOutstandingDateTotal2 = 0
JPOutstandingDateTotal3 = 0
JPOutstandingDateTotal4 = 0

Do Until rst.EOF

If rst.Fields(0) Like "UNKNOWN" Then
If Not IsNull(rst.Fields(1)) Then
JPOutstandingDateTotal1 = JPOutstandingDateTotal1 +
rst.Fields(1)
End If
If Not IsNull(rst.Fields(2)) Then
JPOutstandingDateTotal2 = JPOutstandingDateTotal2 +
rst.Fields(2)
End If
If Not IsNull(rst.Fields(3)) Then
JPOutstandingDateTotal3 = JPOutstandingDateTotal3 +
rst.Fields(3)
End If
If Not IsNull(rst.Fields(4)) Then
JPOutstandingDateTotal4 = JPOutstandingDateTotal4 +
rst.Fields(4)
End If
Else
'do nothing
End If

rst.MoveNext

Loop
 
S

Stefan Hoffmann

hi,

When the query does not return a result the script gives me a runtime
error 3265 (item not found in this collection).
Click to expand...
Set rst = CurrentDb.OpenRecordset("qry_Date Function Crosstab JP",
dbOpenSnapshot)
Click to expand...
If Not rst.BOF And Not rst.EOF Then
' records available
End If

mfG
--> stefan <--
 
D

Dirk Goldgar

Hello All.

please see below code. I am trying to run this vb code that will
return results of a query. The query may or may not have recordsets.
When the query does not return a result the script gives me a runtime
error 3265 (item not found in this collection). I know where the query
holds up but i figure the code i have should take care of the null.
Usually the 4th field in the query (rst.fields(4) does not have any
value and does not even return a result and the code breaks there. is
there any way to set the value to go around this error?? Thanks in
advance and please let me know if you need more information!


Set rst = CurrentDb.OpenRecordset("qry_Date Function Crosstab JP",
dbOpenSnapshot)
rst.MoveFirst

JPOutstandingDateTotal1 = 0
JPOutstandingDateTotal2 = 0
JPOutstandingDateTotal3 = 0
JPOutstandingDateTotal4 = 0

Do Until rst.EOF

If rst.Fields(0) Like "UNKNOWN" Then
If Not IsNull(rst.Fields(1)) Then
JPOutstandingDateTotal1 = JPOutstandingDateTotal1 +
rst.Fields(1)
End If
If Not IsNull(rst.Fields(2)) Then
JPOutstandingDateTotal2 = JPOutstandingDateTotal2 +
rst.Fields(2)
End If
If Not IsNull(rst.Fields(3)) Then
JPOutstandingDateTotal3 = JPOutstandingDateTotal3 +
rst.Fields(3)
End If
If Not IsNull(rst.Fields(4)) Then
JPOutstandingDateTotal4 = JPOutstandingDateTotal4 +
rst.Fields(4)
End If
Else
'do nothing
End If

rst.MoveNext

Loop
Click to expand...

You can approach this either of two ways. You could ensure that the
crosstab query always returns all five fields, by setting its Column
Headings property. Or, if you don't want to do that, you could check
the number of fields return in the recordset, via the recordset's Count
property. For example,

If rst.Fields.Count >= 5 Then
If Not IsNull(rst.Fields(4)) Then
JPOutstandingDateTotal4 = _
JPOutstandingDateTotal4 + rst.Fields(4)
End If
End If

Side note: drop the line ...
rst.MoveFirst
Click to expand...

.... from immediately after you open the recordset. It's unnecessary
(since you just opened the recordset), and will cause an error if the
recordset has no records.
 
J

Jason Lepack

Hello All.

please see below code. I am trying to run this vb code that will
return results of a query. The query may or may not have recordsets.
When the query does not return a result the script gives me a runtime
error 3265 (item not found in this collection). I know where the query
holds up but i figure the code i have should take care of the null.
Usually the 4th field in the query (rst.fields(4) does not have any
value and does not even return a result and the code breaks there. is
there any way to set the value to go around this error?? Thanks in
advance and please let me know if you need more information!

Set rst = CurrentDb.OpenRecordset("qry_Date Function Crosstab JP",
dbOpenSnapshot)
rst.MoveFirst

JPOutstandingDateTotal1 = 0
JPOutstandingDateTotal2 = 0
JPOutstandingDateTotal3 = 0
JPOutstandingDateTotal4 = 0

Do Until rst.EOF

If rst.Fields(0) Like "UNKNOWN" Then
If Not IsNull(rst.Fields(1)) Then
JPOutstandingDateTotal1 = JPOutstandingDateTotal1 +
rst.Fields(1)
End If
If Not IsNull(rst.Fields(2)) Then
JPOutstandingDateTotal2 = JPOutstandingDateTotal2 +
rst.Fields(2)
End If
If Not IsNull(rst.Fields(3)) Then
JPOutstandingDateTotal3 = JPOutstandingDateTotal3 +
rst.Fields(3)
End If
If Not IsNull(rst.Fields(4)) Then
JPOutstandingDateTotal4 = JPOutstandingDateTotal4 +
rst.Fields(4)
End If
Else
'do nothing
End If

rst.MoveNext

Loop
Click to expand...

I think your problem stems from your crosstab not always conatining a
rst.Fields(4), correct? This code will neaten it up a bit as well as
prevent running off the end. The array makes the data a little easier
on the eyes.

Private Sub Command0_Click()
Dim rst As Recordset
Dim JPOutstandingDateTotal(1 To 4) As Integer, i As Integer

Set rst = CurrentDb.OpenRecordset("qry_Date Function Crosstab JP",
dbOpenSnapshot)

If Not rst.RecordCount = 0 Then
Do While Not rst.EOF
If rst.Fields(0) Like "UNKNOWN" Then
For i = 1 To rst.Fields.Count - 1
If Not IsNull(rst.Fields(i)) Then
JPOutstandingDateTotal(i) =
JPOutstandingDateTotal(i) + rst.Fields(i)
End If
Next i
End If
rst.MoveNext
Loop
End If
End Sub
 
P

philip260

thanks for all the suggestions. I will try them from the top. the
easiest solution seemed to be to change the column headings property
to include 5 fields but i cant seem to find a way to do that. also
when:

If rst.Fields.Count >= 5 Then
If Not IsNull(rst.Fields(4)) Then
JPOutstandingDateTotal4 = _
JPOutstandingDateTotal4 + rst.Fields(4)
End If
End If

it gave me an overflow error. Any suggestions. I will test out the
rest. Thanks!!
 
D

Dirk Goldgar

thanks for all the suggestions. I will try them from the top. the
easiest solution seemed to be to change the column headings property
to include 5 fields but i cant seem to find a way to do that. also
when:

If rst.Fields.Count >= 5 Then
If Not IsNull(rst.Fields(4)) Then
JPOutstandingDateTotal4 = _
JPOutstandingDateTotal4 + rst.Fields(4)
End If
End If

it gave me an overflow error. Any suggestions. I will test out the
rest. Thanks!!
Click to expand...

How is JPOutstandingDateTotal4 defined? Is it possible that the total
of all values of rst.Fields(4) could exceed the maximum capacity of that
variable?
 
P

philip260

I think your problem stems from your crosstab not always conatining a
rst.Fields(4), correct? This code will neaten it up a bit as well as
prevent running off the end. The array makes the data a little easier
on the eyes.

Private Sub Command0_Click()
Dim rst As Recordset
Dim JPOutstandingDateTotal(1 To 4) As Integer, i As Integer

Set rst = CurrentDb.OpenRecordset("qry_Date Function Crosstab JP",
dbOpenSnapshot)

If Not rst.RecordCount = 0 Then
Do While Not rst.EOF
If rst.Fields(0) Like "UNKNOWN" Then
For i = 1 To rst.Fields.Count - 1
If Not IsNull(rst.Fields(i)) Then
JPOutstandingDateTotal(i) =
JPOutstandingDateTotal(i) + rst.Fields(i)
End If
Next i
End If
rst.MoveNext
Loop
End If
End Sub- Hide quoted text -

- Show quoted text -
Click to expand...

worked like a charm. thanks jason.
 
P

philip260

How is JPOutstandingDateTotal4 defined? Is it possible that the total
of all values of rst.Fields(4) could exceed the maximum capacity of that
variable?
Click to expand...

It was something funky with the loop. Its all fixed now. thanks for
your suggestions!
 
P

philip260

you're very welcome.- Hide quoted text -

- Show quoted text -
Click to expand...


Hello,

The code worked well with eliminating the Null Error but however now
im experiencing some weird sorting problem. I output the result of
JPOutstandingDateTotal to a textbox on an access report:
jpout1 through jpout4

however when the report is run and the text value is outputted the
order is switched from the result of the query itself. please see
below for code and error:

Set rst = CurrentDb.OpenRecordset("qry_Date Function Crosstab JP",
dbOpenSnapshot)
rst.MoveFirst



If Not rst.RecordCount = 0 Then
Do While Not rst.EOF
If rst.Fields(0) Like "UNKNOWN" Then
For i = 1 To rst.Fields.Count - 1
If Not IsNull(rst.Fields(i)) Then
JPOutstandingDateTotal(i) =
JPOutstandingDateTotal(i) + rst.Fields(i)
End If
Next i
End If
rst.MoveNext
Loop

jpout1.Value = JPOutstandingDateTotal(1)
jpout2.Value = JPOutstandingDateTotal(2)
jpout3.Value = JPOutstandingDateTotal(3)
jpout4.Value = JPOutstandingDateTotal(4)

End If

jpout2 value is actually jpout4
jpout3 value is actually jpout2
jpout4 value is actually jpout3

this sorting error only happens sometimes which i dont understand. Can
someone shed some light on this? Any help will be appreciated.

Thanks in advance
Phil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select record from recordset via vba. 3
Forms ControlSource with DAO 1
IIF(ISNULL(...),NULL,...) fires: invalid use of null (?????) 3
VB Loop Help 4
Pulling a cell in at the end of a row of data 6
Send an Query output with a Email using "sendObject" function 1
run-time error '3121' no current record 3
Compile Error: Variable Not Defined 6

Top