Help with recordset in a function

[zfind]

Hi all,

I've built a small(ish) function that I referred to in another thread, here. It currently uses Dlookup, which is simple but far too slow. I'm trying to convert it to use Recordsets and being completely new to their use, I've come up with the below.

Function get_invoiced_test(hosted, share_type, rev_share, host_share, pid, week, period, delay) As Currency
''*get invoicing based on weeks
 
 
Dim thisweek As Integer
Dim thisperiod As Integer
Dim DelayedAmount As Currency
Dim eco_gross As String
Dim eco_net As String
Dim DelayedWeek As Integer
'Dim year As Integer
Dim dbs As Database
Dim rstTest As Recordset
Dim strQuery As String
 
thisweek = Format(Date, "ww", 0, 0)
DelayedWeek = week - delay
 
Set dbs = CurrentDb
strQuery = "SELECT [Ecosystem Gross LE], [Ecosystem Net LE], [Ecosystem Gross Actual], [Ecosystem Net Actual] FROM tblRecords WHERE PartnersetID = " & pid & " AND WeekID = " & DelayedWeek & ""
 
Debug.Print strQuery
 
Set rstTest = dbs.OpenRecordset(strQuery)
 
Select Case thisweek
    Case 27 To 31
        thisperiod = 7
    Case 32 To 35
        thisperiod = 8
    Case 36 To 39
        thisperiod = 9
    Case 40 To 44
        thisperiod = 10
    Case 45 To 48
        thisperiod = 11
    Case 49 To 52
        thisperiod = 12
    Case Else
        thisperiod = 0
End Select
 
If period >= thisperiod Then
    eco_gross = "[Ecosystem Gross LE]"
    eco_net = "[Ecosystem Net LE]"
    Else
    eco_gross = "[Ecosystem Gross Actual]"
    eco_net = "[Ecosystem Net Actual]"
End If
 
If hosted = -1 Then
    If share_type = "Gross" Then
        ''' Gross, Hosted
        DelayedAmount = rstTest! & eco_gross
        get_invoiced = Nz(DelayedAmount, 0) - (Nz(DelayedAmount, 0) * Nz(rev_share, 0) * Nz(host_share, 0))
    Else
        '''Nett, Hosted
        DelayedAmount = rstTest! & eco_net
        get_invoiced = Nz(DelayedAmount, 0) - (Nz(DelayedAmount, 0) * Nz(rev_share, 0) * Nz(host_share, 0))
    End If
Else
    If share_type = "Gross" Then
        '''Gross, Not Hosted
        DelayedAmount = rstest! & eco_gross
        get_invoiced = Nz(DelayedAmount, 0) * Nz(rev_share, 0)
    Else
        '''Nett, Not Hosted
        DelayedAmount = rstest! & eco_net
        get_invoiced = Nz(DelayedAmount, 0) * Nz(rev_share, 0)
    End If
End If
 
End Function

Basically the only modifications were establishing new variables, opening the recordset as with SQL (including limiting it with use of the variables found in the function) and then trying to grab the values from the fields in the recordset to be used in formulae. However, when I run a query that uses the function I get:

Complie error: Type-declaration character does not match declared data type

With

rstTest!&eco_gross

highlighted. If I change this to

rstTest!eco_gross

I then get the error

Compile error:Function call on left hand side of assignment must return Variant or Object

with get_invoiced highlighted.

I know this is probably pretty simple but I am new to using recordsets, so be gentle If there are any other glaring problems with my code, please comment as I'm trying to improve the speed of the main query this runs in.

EDIT: I should mention that with that SQL query, only one record should ever be found with that combination of 'pid' and 'weekid'

EDIT EDIT: Just after I posted this I realised I had made some dumb mistakes - must return object or variant error was due to the function being renamed get_invoiced_test and me not reflecting this in the code. Secondly, I had misspelled rstTest as rstest in the code. After the above changes however, I still get the type-declaration character error.

 

[DCrake]

2 things to consider.

first I would implically delcare your recordset as DAO.Recordset

Dim RstTest DAO.Recordset

Next when referring to fields returned by a reocrdset you refer to them as follows

RstTest("Ecosystem Gross LE")

Also you should check for records being returned in your recordset before attempting to use it.

If Not RstTest.EOF Then

CodeMaster:

 

[zfind]

Changes noted and made, thanks I've been working on it and have got it working well, it seems. I'm still working out bugs but I'm quite proud that I got this working with rSets so quickly, thanks to AP.co.uk and Google of course

Do I need to close the recordset at the end of the function?

 

[DCrake]

The only time you need to explicitally close a recordset is when the recordset returns data, ie the recordset is open.

Rs.Close

However you MUST always use the

Set Rs = Nothing to release the connection from memory and caching.

If you try to use Rs.Close on a Recorsetset that has no contents it will error.

CodeMaster:

 

[zfind]

I knew I had seen the = nothing before but never knew way will implement that. Thanks!

I've now got the function working more or less how I want. The problem is, it's still taking around 6 minutes to run this query. I was hoping that someone would be able to see if my code could be tweaked to make the calculations faster- it's far from perfect

Code as it stands:

Function get_invoiced(hosted, share_type, rev_share, host_share, pid, week, year, period, delay) As Currency
''*get invoicing based on weeks, using recordsets
 
Dim thisweek As Integer
Dim thisperiod As Integer
Dim DelayedGross As Currency
Dim DelayedNet As Currency
Dim DelayedWeek As Integer
Dim dbs As Database
Dim rstInvoiced As DAO.Recordset
Dim strQuery As String
Dim useLE As Boolean
 
thisweek = Format(Date, "ww", 0, 0)
 
DelayedWeek = week - delay
 
If year = 2008 And DelayedWeek < 27 Then DelayedWeek = 27
If year = 2009 And DelayedWeek <= 0 Then
    year = 2008
    TempWeek = 52 + DelayedWeek
    DelayedWeek = TempWeek
End If
 
Set dbs = CurrentDb
 
strQuery = "SELECT [Ecosystem Gross LE], [Ecosystem Net LE], [Ecosystem Gross Actual], [Ecosystem Net Actual] FROM tblRecords WHERE PartnersetID = " & pid & " AND WeekID = " & DelayedWeek & " AND Year = 2008"
 
Debug.Print strQuery
 
Set rstInvoiced = dbs.OpenRecordset(strQuery)
 
Select Case thisweek
    Case 27 To 31
        thisperiod = 7
    Case 32 To 35
        thisperiod = 8
    Case 36 To 39
        thisperiod = 9
    Case 40 To 44
        thisperiod = 10
    Case 45 To 48
        thisperiod = 11
    Case 49 To 52
        thisperiod = 12
    Case Else
        thisperiod = 0
End Select
 
If period >= thisperiod Then
    DelayedNet = Nz(rstInvoiced![Ecosystem Net LE], 0)
    DelayedGross = Nz(rstInvoiced![Ecosystem Gross LE], 0)
    Else
    DelayedNet = Nz(rstInvoiced![Ecosystem Net Actual], 0)
    DelayedGross = Nz(rstInvoiced![Ecosystem Gross Actual], 0)
End If
 
If hosted = -1 Then
    If share_type = "Gross" Then
        ''' Gross, Hosted
        get_invoiced = Nz(DelayedGross, 0) - (Nz(DelayedGross, 0) * Nz(rev_share, 0) * Nz(host_share, 0))
    Else
        '''Nett, Hosted
        get_invoiced = Nz(DelayedNet, 0) - (Nz(DelayedNet, 0) * Nz(rev_share, 0) * Nz(host_share, 0))
    End If
Else
    If share_type = "Gross" Then
        '''Gross, Not Hosted
        get_invoiced = Nz(DelayedGross, 0) * Nz(rev_share, 0)
    Else
        '''Nett, Not Hosted
        get_invoiced = Nz(DelayedNet, 0) * Nz(rev_share, 0)
    End If
End If
 
End Function

 

[DCrake]

Check that you have indexes on the fields you are filtering

 

[LPurvis]

I'd certainly agree about checking those fields for being indexed.
Also you're using the recordset defaults which will be returning a Dynaset - which is generally little different from Snapshot (especially in situations like this where you're selecting only limited fields) but it's worth trying to be more specific - especially since you're only reading data and so don't need an updatable recordset.

I'm not sure if what DCrake was saying earlier about closing recordsets was clear.
I'd imagine the intended message is that any open recordset needs closing. (That includes recordsets which contain no records - i.e. both EOF and BOF are true - as they're still open).

Cheers.