Overflow error in query

[munkee]

I use the following code to execute this query:

[code]Me.Text419 = RunTheSql("SELECT Sum(tblCosts.CostFig) AS CountOfID FROM tbllog INNER JOIN tblCosts ON tbllog.NCC_ID = tblCosts.NCC_ID WHERE (((tbllog.DeptRaisedBy)=" & Me.cmboDept & "));", "CountOfID")
CODE]

Note: query converted back from vba as I have been testing in normal querydef

Public Function RunTheSql(sqlstring As String, WhatToCount As String) As Integer
 
On Error GoTo jumpout
Dim rs As Dao.Recordset
Dim counter As Integer
 
 
Debug.Print sqlstring
Set rs = CurrentDb.OpenRecordset(sqlstring, dbOpenSnapshot)
If rs.RecordCount <> 0 Then
counter = rs(WhatToCount)
Else
counter = 0
End If
 
 
RunTheSql = counter
 
 
completedyo:
rs.Close
 
Set rs = Nothing
 
Exit Function
 
jumpout:
MsgBox Err.Description & " - " & Err.Number
Resume completedyo
 
End Function
 

When I get to the line:

counter = rs(WhatToCount)
 

I get the overflow error 6 message box.

I have no clue why as this works perfectly fine when I put the sql statement converted back in to a normal query.

Also the function works for all my other queries.

Any ideas?

[Mariostg]

Dim counter as Long instead of Integer. Your counter is likely larger than 32,767.

[munkee]

I thought the same but it still seems to be happening:

Public Function RunTheSql(sqlstring As String, WhatToCount As String) As Integer
 
On Error GoTo jumpout
Dim rs As Dao.Recordset
Dim counter As Long
 
 
Debug.Print sqlstring
Set rs = CurrentDb.OpenRecordset(sqlstring, dbOpenSnapshot)
If rs.RecordCount <> 0 Then
counter = rs(WhatToCount)
Else
counter = 0
End If
 
 
RunTheSql = counter
 
 
completedyo:
rs.Close
 
Set rs = Nothing
 
Exit Function
 
jumpout:
MsgBox Err.Description & " - " & Err.Number
Resume completedyo
 
End Function
 

[Mariostg]

Well, your function needs to return a Long as well...
did you debug.print(rs(WhatToCount)) to see what comes out?

[NeoPa]

Maybe I'm missing something. Does this function do anything the Domain Aggregate functions can't (EG. DSum(); DCount(); etc)?

[munkee]

NeoPa: Yes the function runs about 20 times on a "dashboard" page I have showing various figures from my database. DCount / Lookup etc was hitting performance hard.

However with that being said the fix suggested using "long" has cured half the problem.

I am now getting "Invalid use of null" errors when the function should return 0.

I have stepped through the code and eventhough the sql in a normal query would return a blank I am getting the .recordcount in this function returning 1 and then ofcourse it produces the invalid use of null when I get to:

counter = rs(WhatToCount)

When it should really return a .recordcount = 0 and jump to counter = 0

Current code:

Public Function RunTheSql(sqlstring As String, WhatToCount As String) As Long
 
On Error GoTo jumpout
Dim rs As Dao.Recordset
Dim counter As Long
 
 
Debug.Print sqlstring
Set rs = CurrentDb.OpenRecordset(sqlstring, dbOpenSnapshot)
If rs.RecordCount <> 0 Then
 
counter = rs(WhatToCount)
Else
counter = 0
End If
 
 
RunTheSql = counter
 
 
completedyo:
rs.Close
 
Set rs = Nothing
 
Exit Function
 
jumpout:
MsgBox Err.Description & " - " & Err.Number
Resume completedyo
 
End Function

Edit: Cured the issue by wrapping with nz:

counter = Nz(rs(WhatToCount), 0)

Weird why it was returning like this with a recordcount of 1 when there wasn't a visible record to be found though.

[Mariostg]

Yep, Integer, Long cannot return a Null Value.
You could use counter = nz(rs(WhatToCount),0).

you could also check that rs.BOF and rs.EOF are not false. That ensures you don't have an empty recordset.

[NeoPa]

Before .RecordCount is reliable in a Snapshot recordset it seems (See Help for RecordCount Property (DAO)) that the last record must first be read. In your case I expect simply testing for BOF and EOF might be a more reliable indicator.

I have a hard time believing that this function works more quickly than a properly invoked Domain Aggregate function. Not because the code isn't any good, but simply because VBA code is only semi-compiled, which in itself adds quite an overhead to executing code. This is not the case with properly compiled functions wiyhin the libraries. That said, it can be a bit tricky calling such functions for the example you give us, but like-for-like I would expect the VBA to execute more slowly.

Let us know if Mario's idea of checking for BOF & EOF instead of the RecordCount pays any dividends.

[munkee]

NeoPa,

I had around 10 of the domain functions set up initially for my simple queries but it is when they become more technical with multiple joins being required to drag in info that I have no clue where to start. That also made moving over to a function that would execute the sql uncompiled the easy option.

With that being said can you recommend any other methods for carrying out complex queries. I thought Domain functions were just a nice dressed up function native to access that would be carrying out queries with sql, making everything look a bit more userfriendly though?

I am simply calling all my functions from a submission button that grabs a date and a department name as part of the inputs to the sql to be executed.

I never really mess with using querydefs which I know are precompiled but I assume they might be an option. If there is some example code kicking around that anyone knows of regarding how to return a value from this I would appreciate it.

[dsatino]

From what I can tell, your query is only designed to return one record so you don't really need the recordcount. Just use rs.movefirst...if the recordset is empty it will error.

After that you have to test for null in 'whattocount' field.

Also i noticed that your calling it a count, but your basing it on a summed field with the word 'cost' in it. If there are decimal values in your field you'll get another error.

[NeoPa]

Generally, the best idea is to design the .RecordSource such that as much as possible of what you want to show on the form is included. That's not always possible of course.

Using Domain Aggregate functions, of course, requires saved record sources of some type. They won't work with SQL strings.

What did you find when you tried Mario's idea (as mentioned in the previous post)?

[NeoPa]

DSatino:
From what I can tell, your query is only designed to return one record so you don't really need the recordcount. Just use rs.movefirst...if the recordset is empty it will error.

Bear in mind that performance is an issue here. What you suggest adds an extra recordset processing step into the equation. Hence the suggestion to check .BOF & .EOF.

[dsatino]

True, but snapshot recordsets are fully loaded before you can use them so the extra step is measured in milliseconds.

Also, I'm guessing that performance issues are probably related to a lack of indexing on the JOIN or WHERE fields.

[NeoPa]

Performance issues can be related to many and various things. It's never a good idea to forget though, that small periods add up when repeated many times. I know it's not much, but why bother if you can simply check .EOF?

Personally, and this is just my opinion so should be treated as such, I don't much like using error handling to implement logic.