Help with DCOUNT Function in Module

[qcjustin]

I am trying to adjust my DCOUNT function to also use Country field as a criteria to determine if a date is a Holiday.
I have a table (Holidays) with 2 fields on it. HolDate and Country.

In my query run, the field for Country is called CountryOn.

This runs, but it does not identify a date as a holiday even though its in the table.
The matching problem is in Country.
I would like it to count the number of instances where the date AND country match, since different countries have different holidays.
lCountry is set during the input query for my OTHER public function.
Can i define a variable in a function, and have the other public function reference it?
CountryOn must be a dynamic variable, as there are queries with many different names these functions run from.

Right now I have a query for each country.
The main function references IsHoliday function.

Main Function Call
Public Function NetWorkMinutes(rdteStart As Date, rdteEnd As Date, lCountry As String) As Long

Holiday Function
Public Function IsHoliday(rdteDate As Date) As Boolean
Dim lCountry As String
On Error GoTo IsHoliday_Error

Select Case DCount("*", "Holidays", "HolDate = " & DateSQL(rdteDate) _
& "Country" = lCountry)
Case Is > 0
IsHoliday = True
Case Else
IsHoliday = False
End Select




Exit_Procedure:


On Error GoTo 0
Exit Function


IsHoliday_Error:


If Err.Number = 3078 Then
IsHoliday = False
Resume Exit_Procedure
Else
Err.Raise Err.Number, Err.Source, Err.Description
End If

[Rawb]

As long as the lCountry variable is declared as public then yes, you can access it from any Function in the database.

Perhaps a better option though, would be to pass the lCountry into your IsHoliday() function as an argument. Assuming it's set in the parent function, that shouldn't be too difficult.

[qcjustin]

Since I am not setting the arguments for IsHoliday(the NetWorkMinutes function calls this function/variables), is this still the best way to do it?

I have modified my IsHoliday line to be : Public Function IsHoliday(rdteDate As Date, Optional lCountry As String) As Boolean

If I leave out optional, it says "Compile Error(Argument not optional) for another line:

If IsHoliday(rdteStart) Then

I have added it as a normal required variable, and also added it to each statement. If IsHoliday(rdteStart, lCountry)

It still does not count it as a holiday.

[qcjustin]

Here is an attachment of a test database for this.
I have printed the values for Country and Date to a message box, and they look to be correct.

The Response time for the single entry on there should change from 9 hours down to 1 hour, if it works right.

Thanks much

[qcjustin]

This has to be a pretty simple thing missing.

[qcjustin]

My latest change is adjusting my DCOUNT line to this.

Since lCountry is a text string, i used:

Select Case DCount("*", "Holidays", "HolDate = " & DateSQL(rdteDate) & " And [Country] Like '& lCountry & '")

This looks right to me, but DOES NOT WORK.

Any help would be appreciated.

[qcjustin]

Bump... need an answer

[pbaldy]

The syntax would be

DCount("*", "Holidays", "HolDate = " & DateSQL(rdteDate) & " And [Country] Like '" & lCountry & "'")

unless you wanted a wildcard in there. That assumes the date function returns an appropriate value complete with #.

[qcjustin]

Worked like a charm, just as simple as some quotes misplaced. Thanks a bunch.

[pbaldy]

Happy to help!