Function issue (1 Viewer)

[andy1968]

I've created a function that takes 2 dates and then should find the number of holidays between the 2 dates; the holidays are listed in a table.

Function Holidays(StartDate As Date, EndDate As Date) As Integer

Holidays = DCount("*", "tblHolidays", "([HolidayDate] <=  EndDate ) AND ([HolidayDate] >=  StartDate )")

End Function

I get a run-time error '2471':


The expression you entered as a query parameter produced this error: 'EndDate'


I've used the same code (without variables) on another form, and it worked fine.


Any ideas?

 

[nhorton79]

Away from computer at the moment so can’t test this but try escaping the string to include the date supplied i.e.

Holidays = DCount("*", "tblHolidays", "([HolidayDate] <= " & EndDate & ") AND ([HolidayDate] >= " & StartDate & ")")

If this doesn’t work you might also need to add hashes (#) to either side of your date before the double quote escaping I.e.

Holidays = DCount("*", "tblHolidays", "([HolidayDate] <= #" & EndDate & "#) AND ([HolidayDate] >= #" & StartDate & "#)")

Sent from my iPhone using Tapatalk

 

[theDBguy]

Hi Andy. Just curious, what is your Regional Settings?

 

[isladogs]

Or a slightly shorter variation

Holidays = DCount("*", "tblHolidays", "HolidayDate Between #" & StartDate & "# AND  #" & EndDate & "#")

But, as DBG hinted, if your dates aren't US format, you will need to format them as mm/dd/yyyy in VBA code

 

[andy1968]

Region is set to US.


Isladogs and nhorton79's first suggestions worked!


Thanks all for the help.