Invalid Syntax (1 Viewer)

JithuAccess

Member
Local time
Today, 02:06
Joined
Mar 3, 2020
Messages
215
Hello Guys,

I want to find Total Decision made in a week in my Report and this is my Code:

Code:
=DCount("strProgram","tblMaster Table]","strProgram='ABC-123' And datDecision Between DateAdd("d",-6-Weekday(Date(),2),Date()) And DateAdd("d",-2-Weekday(Date(),2),Date())"))

Here in the criteria of Date I want to find the data between 1 week prior to the current week and this would be dates between 26-Apr-2021 and 30-Apr-2021.

I am getting the following Error:


1620137685742.png


Could you please let me know what would be the correct Syntax?

Thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:06
Joined
Feb 28, 2001
Messages
19,649
I think your problem is quote-balancing in this criteria expression:

"strProgram='ABC-123' And datDecision Between DateAdd("d",-6-Weekday(Date(),2),Date()) And DateAdd("d",-2-Weekday(Date(),2),Date())"

What the DCount will probably see is

"strProgram='ABC-123' And datDecision Between DateAdd("

because of the double-quoting. In both of your DateAdd calls, change those double quotes to single quotes.

"strProgram='ABC-123' And datDecision Between DateAdd('d',-6-Weekday(Date(),2),Date()) And DateAdd('d',-2-Weekday(Date(),2),Date())"
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:06
Joined
Sep 21, 2011
Messages
8,024
Plus your table does not have a starting square bracket? [
 
Last edited:

JithuAccess

Member
Local time
Today, 02:06
Joined
Mar 3, 2020
Messages
215
I think your problem is quote-balancing in this criteria expression:

"strProgram='ABC-123' And datDecision Between DateAdd("d",-6-Weekday(Date(),2),Date()) And DateAdd("d",-2-Weekday(Date(),2),Date())"

What the DCount will probably see is

"strProgram='ABC-123' And datDecision Between DateAdd("

because of the double-quoting. In both of your DateAdd calls, change those double quotes to single quotes.

"strProgram='ABC-123' And datDecision Between DateAdd('d',-6-Weekday(Date(),2),Date()) And DateAdd('d',-2-Weekday(Date(),2),Date())"
Thanks a lot. It worked Perfect
 

Users who are viewing this thread

Top Bottom