I've got an existing report that is dependent on an extremely large daisy chain of queries that I built a number of years ago when I was just learning Access and building a new database at that point.
I'm trying to recreate the same report as I need to change the "work week" from what it was when I built the original report to now. Originally Sunday-Saturday now Monday-Sunday.
In any case i've done some digging today and boy am i NOT impressed with the mess i left myself years ago!
The report is comparing a running 3 year comparison "YTD" and i'm trying to just put a text string function in each data field on the report so I can reduce the number of queries if any at all.
I've written some Dcount functions all of which work separately; however when I attempt to put the criteria into one string, i'm getting errors. Can anyone tell me what i'm doing wrong?
Onto the Function: The attempt is to count the number of records in "MainDataTbl" that is between 1/1/2022 a date going back a year based on a date plugged into a Form Field AND the case number starts with BS.
=DCount("*","MainDataTbl","[Casedate] Between DateSerial(Year(Date())-1,1,1)" & "And DateAdd("yyyy",-1,[Forms]![weekqry]![YTDEnd]" & "And [MainDataTbl]![CaseNumber] Like 'BS*'")
When I attempt to run the report, i get an error that says "the expression you entered contains invalid syntax. You may have entered an operand without an operator.
Where have I gone wrong?
PS: This works when I enter dates into the function rather than the date functions so i suspect something is wrong there. Someone please help!
I'm trying to recreate the same report as I need to change the "work week" from what it was when I built the original report to now. Originally Sunday-Saturday now Monday-Sunday.
In any case i've done some digging today and boy am i NOT impressed with the mess i left myself years ago!
The report is comparing a running 3 year comparison "YTD" and i'm trying to just put a text string function in each data field on the report so I can reduce the number of queries if any at all.
I've written some Dcount functions all of which work separately; however when I attempt to put the criteria into one string, i'm getting errors. Can anyone tell me what i'm doing wrong?
Onto the Function: The attempt is to count the number of records in "MainDataTbl" that is between 1/1/2022 a date going back a year based on a date plugged into a Form Field AND the case number starts with BS.
=DCount("*","MainDataTbl","[Casedate] Between DateSerial(Year(Date())-1,1,1)" & "And DateAdd("yyyy",-1,[Forms]![weekqry]![YTDEnd]" & "And [MainDataTbl]![CaseNumber] Like 'BS*'")
When I attempt to run the report, i get an error that says "the expression you entered contains invalid syntax. You may have entered an operand without an operator.
Where have I gone wrong?
PS: This works when I enter dates into the function rather than the date functions so i suspect something is wrong there. Someone please help!
