DCount the number of holidays between 2 dates

[jtan]

Hi all,

Need your help on below problem.
Below tbl_Holidays for all holiday dates in Singapore.

HolidayDate	Remark
01-Jan-20	New Year Day
27-Jan-20	Chinese New Year Replacement
10-Apr-20	Good Friday
01-May-20	Labour Day
07-May-20	Vesak Day
25-May-20	Hari Raya Puasa Replacement
31-Jul-20	Hari Raya Haji
10-Aug-20	National Day Replacement
25-Dec-20	Christmas

and below query to count the number of holidays between 2 dates (ReceivedDate and InspectedDate)
NoOfHolidays: DCount("*","tbl_Holidays","[HolidayDate] BETWEEN #" & [ReceivedDate] & "# AND #" & [InspectedDate] & "#")




But, below query result (highlighted) is not correct. The correct NoOfHolidays is 1.

ReceivedDate	InspectedDate	NoOfHolidays
02-Apr-20	13-Apr-20	1
02-Apr-20	15-Apr-20	1
03-Apr-20	13-Apr-20	1
03-Apr-20	16-Apr-20	1
03-Apr-20	20-Apr-20	1
06-Apr-20	13-Apr-20	3
06-Apr-20	15-Apr-20	3
06-Apr-20	20-Apr-20	3
07-Apr-20	13-Apr-20	3
08-Apr-20	13-Apr-20	4
08-Apr-20	15-Apr-20	4
08-Apr-20	16-Apr-20	4
09-Apr-20	13-Apr-20	5
09-Apr-20	20-Apr-20	5

Can someone advise what is wrong ?
Appreciate your help

[June7]

Query syntax and DCount() work for me.

Issue might be with international date. Review http://allenbrowne.com/ser-36.html

[CJ_London]

it is because your dates need to be formatted as 'mm/dd/yyyy' (US style). At the moment it is interpreting 6th April as 4th June. The interpreter is good enough to know that there are only 12 months so interprets 13th April correctly

try

NoOfHolidays: DCount("*","tbl_Holidays","[HolidayDate] BETWEEN #" & format([ReceivedDate],"mm/dd/yyyy") & "# AND #" & format([InspectedDate],"mm/dd/yyyy") & "#")