Invalid Syntax (1 Viewer)

[JithuAccess]

Hello Guys,

I want to find Total Decision made in a week in my Report and this is my 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:

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

Thanks

 

[The_Doc_Man]

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]

Plus your table does not have a starting square bracket? [

 

[JithuAccess]

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

 

[JithuAccess]

Plus you table does not have a starting square bracket? [

Sorry. I fixed this. It works Perfect. Thanks a lot for your Help

 

[The_Doc_Man]

Great! Glad we could help.

 

[JithuAccess]

Great! Glad we could help.

Thank You

 

[arnelgp]

youre welcome