Need help with this query

[jworegon]

The following query (which isn't working) works off of the MonthYear field in the table tblPayrollRecords, which is a date field (e.g., 1/1/2025). If the current month is January, it returns the GrossAvailable value for January. If it is any other month in the year, it returns the January value plus the sum of all the GrossAvailable values for the remainder of the year.



Here's the query that isn't working:
PayrollAmt2: IIf((DatePart("m",[MonthYear]))=1,
[GrossAvailable],
([GrossAvailable]+DSum("GrossAvailable","tblPayrollRecords","tblPay rollRecords.EmployeeID= “ & [EmployeeID] & “ And [MonthYear] >= #"
& DateSerial(Year(Date()),1,1) &
"# And [MonthYear] <= #"
& DateSerial(Year(Date()),DatePart("m",[MonthYear])-1,1) & "#")))

BUT, if I insert a specific EmployeeID value, as below, it works properly.

PayrollAmt2: IIf((DatePart("m",[MonthYear]))=1,
[GrossAvailable],
([GrossAvailable]+DSum("GrossAvailable","tblPayrollRecords","tblPay rollRecords.EmployeeID= 48 And [MonthYear] >= #"
& DateSerial(Year(Date()),1,1) &
"# And [MonthYear] <= #"
& DateSerial(Year(Date()),DatePart("m",[MonthYear])-1,1) & "#")))

So, I'm guessing there's something wrong in the syntax, though it's not throwing any errors.

Thanks for any help to get it working properly.

[Welshgasman]

Use the full forms reference for employeeID

[jworegon]

I've been away from Access for several years, so I'm not sure exactly how to do that. Sorry. The form that this query is in is named frmMonthlyCalculatorSubForm and the query provides the RecordSource for the subform. Would I be better off to put this formula in a separate textbox on the form, and not in the underlying form query? I apologize for my ignorance!

[Welshgasman]

When you say it does not work, what exactly do you mean?
You need to use the full form reference of the form where employeeid is located.
https://www.google.com/search?q=acce...d%20in%20query

[Welshgasman]

I would put the DSum() as a seperate field just as a test. That will allow you to use the full form reference in the Builder.
Then you can go into sql window and amend the syntax to suit, if those links do not help you.

[June7]

Your query has to reference a form that is used as a subform - that can get complicated as the full path reference must be through the subform container control. The container might have same name as the form it holds but I always name them different. So reference would be like:

Forms!MainformName.SubformContainerName.Form!EmployeeID

Yes, I suggest just do this calc in a textbox on the subform. Do not have to use full form path. The calc would work whether form is used as subform or not.


Domain aggregate functions can slow performance of query or form with large dataset.

[jojowhite]

on the Original Query you posted, you have a "slant" double qoute:

PayrollAmt2: IIf((DatePart("m",[MonthYear]))=1,
[GrossAvailable],
([GrossAvailable]+DSum("GrossAvailable","tblPayrollRecords","tblPay rollRecords.EmployeeID= “ & [EmployeeID] & “ And [MonthYear] >= #"
& DateSerial(Year(Date()),1,1) &
"# And [MonthYear] <= #"
& DateSerial(Year(Date()),DatePart("m",[MonthYear])-1,1) & "#")))

[Welshgasman]

on the Original Query you posted, you have a "slant" double qoute:

PayrollAmt2: IIf((DatePart("m",[MonthYear]))=1,
[GrossAvailable],
([GrossAvailable]+DSum("GrossAvailable","tblPayrollRecords","tblPay rollRecords.EmployeeID= “ & [EmployeeID] & “ And [MonthYear] >= #"
& DateSerial(Year(Date()),1,1) &
"# And [MonthYear] <= #"
& DateSerial(Year(Date()),DatePart("m",[MonthYear])-1,1) & "#")))

Good spot!