Convert a DSum to Vba/Recordset (1 Viewer)

raziel3

Registered User.
Local time
Yesterday, 20:12
Joined
Oct 5, 2017
Messages
141
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)

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

Well-known member
Local time
Yesterday, 20:12
Joined
Apr 9, 2015
Messages
4,102
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

AWF VIP
Local time
Yesterday, 17:12
Joined
Apr 13, 2010
Messages
1,173
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:
Code:
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

Registered User.
Local time
Yesterday, 20:12
Joined
Oct 5, 2017
Messages
141
I am using Access 2016 and DAO is not recognized. What is the alternative?
 

bastanu

AWF VIP
Local time
Yesterday, 17:12
Joined
Apr 13, 2010
Messages
1,173
Just add a reference to dao library.
 

raziel3

Registered User.
Local time
Yesterday, 20:12
Joined
Oct 5, 2017
Messages
141
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?

Code:
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"
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:12
Joined
Feb 19, 2002
Messages
36,296
The query will be interpreted by the query engine NOT VBA so you need to force VBA to substitute actual values for your variables when you build the SQL String.

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

Put a stop on the line of code following this statement and print strYTDGross to the debug window to make sure the statement is correct. If you are still having trouble, copy and paste the string from the debug window into the query builder in SQL view and run it there. Sometimes you get better error messages.

When you open the query, you should refer to the columns by name rather than index. It makes the code easier to read.

I also noticed that strPeriodEnd is defined as a string. It should be defined as a date.
 

raziel3

Registered User.
Local time
Yesterday, 20:12
Joined
Oct 5, 2017
Messages
141
Code:
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

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

This query works.

BOY(PEnd) is this: -
Code:
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??
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:12
Joined
Feb 19, 2002
Messages
36,296
You need to print strYTDG so we can see the query that is being executed.
 

raziel3

Registered User.
Local time
Yesterday, 20:12
Joined
Oct 5, 2017
Messages
141
Ok I got it. I'm such a noob.
Code:
With CurrentDb.OpenRecordset(strYTDG)
        If Not (.BOF And .EOF) Then
            strYTDG = .Fields("YTDG")  'referenced the sql
        End If
End With

was supposed to be
Code:
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

I’m here to help
Staff member
Local time
Yesterday, 17:12
Joined
Oct 29, 2018
Messages
18,972
Hi. Pardon me for jumping in, but it looks like you could have just done a DLookup().
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:12
Joined
Sep 21, 2011
Messages
10,544
TBH I would not be creating strings and then assigning numeric values to them. I would be creating separate variables for each?
 

raziel3

Registered User.
Local time
Yesterday, 20:12
Joined
Oct 5, 2017
Messages
141
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.
 

Users who are viewing this thread

Top Bottom