Convert a DSum to Vba/Recordset

[raziel3]

Good day all,

I have a parameter query(qyrYTD) that returns the Year-to-Date Gross of an employee once the user inputs the Employee Name(EName) and the Ending Payroll Period(PE)

SELECT Employees.EName, Sum(qryPayroll.GROSS) AS SGROSS, Sum(qryPayroll.ENIS) AS SENIS, Sum(qryPayroll.HS) AS SHS, Sum(qryPayroll.PAYE) AS SPAYE

FROM qryPayroll INNER JOIN Employees ON qryPayroll.EID = Employees.EID

WHERE (((qryPayroll.PE) Between DateSerial(Year([Enter Date]),1,1) And [Enter Date]))

GROUP BY Employees.EName

HAVING (((Employees.EName)=[Enter Name]));

I have used DSums/Dlookups in the past and they tend to crash my database so I am looking for a way to move away from that. How can I use the Recordset function on the query mentioned above? I want to return the YTD Gross on a report field once the Employee Name matches.

 

[Ranman256]

don't use Dsums and Dlookups in a query. The QUERY is the dsum/lookup.
Make Q1 do the sum, then Q2 uses Q1 to grab other data.

Dlookups in a query is a table joined to another table.

 

[bastanu]

Would be easier to create a small pop-up form to enter the parameters (date and employee name).
Anyway, here is what I do for parameter queries in VBA:

Dim qdf as Dao.Querydef, prm as Parameter,rst as Dao.recordset
Dim curYTD as Currency

set qdf=CurrentDb.QueryDefs("qryYTD")

For each prm in qdf.parameters
   prm.Value=Eval(prm.Name)
next prm

Set rst=qdf.OpenRecorset

curYTD=rst("SGROSS") 'now use this variable in your report

Cheers,
Vlad

 

[raziel3]

I am using Access 2016 and DAO is not recognized. What is the alternative?

 

[bastanu]

Just add a reference to dao library.

 

[raziel3]

Just add a reference to dao library.

I'm still having some problems, I decided to go a different direction. Can you walk me through it?

Public Function YTDGross(ByVal inputPE As Variant, inputEName As Variant) As Double

Dim strPeriodEnd As String
Dim strYTDGross As String

strPeriodEnd = "#" & inputPE & "#"

strYTDGross = "SELECT Sum([SGROSS]) as YTDG FROM qryYTD WHERE [PE] Between DateSerial(Year(strPeriodEnd ),1,1) And strPeriodEnd" _
                          & " And [EName] = inputEName" _
                          & " Group BY [EName];"

Debug.Print strYTDGross
Debug.Print strPeriodEnd
Debug.Print inputEName

With CurrentDb.OpenRecordset(strYTDGross)
    If Not (.BOF And .EOF) Then
           YTDGross = .Fields(1)
    End If
End With

End Function

I am trying to call the function in an unbounded field in my report and I am getting a error Runtime error 3061 "Too few parameters. Expected 5"

 

[raziel3]

Public Function fncYTDG(EmpN As Integer, PEnd As Date) As Double

strYTDG = "SELECT Sum([SGROSS]) As YTDG FROM qryYTD2 WHERE [PE] Between #" & BOY(PEnd) & "# And #" & PEnd & "#" _
                & " And [EID]=" & EmpN
           
Debug.Print BOY(PEnd)

    With CurrentDb.OpenRecordset(strYTDG)
        If Not (.BOF And .EOF) Then
            strYTDG = .Fields("YTDG")
        End If
    End With

End Function

This is returning 0

SELECT Sum([SGROSS]) As YTDG
FROM qryYTD2
WHERE [PE] Between BOY(PEnd) And PEnd And [EID]=EmpN;

This query works.

BOY(PEnd) is this: -

Public Function BOY(EnterDate As Date) As Date

    Dim YBeg As Date
    Dim NM As Date

    NM = DateAdd("m", 1, EnterDate)
    YBeg = Format(DateSerial(Year(NM), 1, "1"), "m/d/yyYY")
       
    BOY = YBeg

End Function

What am I doing wrong??

 

[raziel3]

Ok I got it. I'm such a noob.

With CurrentDb.OpenRecordset(strYTDG)
        If Not (.BOF And .EOF) Then
            strYTDG = .Fields("YTDG")  'referenced the sql
        End If
End With

was supposed to be

With CurrentDb.OpenRecordset(strYTDG)
        If Not (.BOF And .EOF) Then
            fncYTDG = .Fields("YTDG")  'needed to assign the function
        End If
End With

Thanks @Pat Hartman, can I trouble you a little?

What's the difference between what I was trying to do before (using the Recordset Method) and this way, passing the query through VBA. Is one way better than the other and how can I do this using Recordset and parameters?

 

[theDBguy]

Hi. Pardon me for jumping in, but it looks like you could have just done a DLookup().

 

[Gasman]

TBH I would not be creating strings and then assigning numeric values to them. I would be creating separate variables for each?

 

[raziel3]

Hi. Pardon me for jumping in, but it looks like you could have just done a DLookup().

I have problems with domain aggregates. Most of the time, the query freezes and crashes access.

TBH I would not be creating strings and then assigning numeric values to them. I would be creating separate variables for each?

Yeah, @Pat Hartman mentioned that before.
I've fixed it.

But I really want to learn how to use Recordset and parameters. I know a little about using Recordset to add/edit/delete. but I want to know how to use @bastanu code.