Reference value of record in a module

[Ports]

Hi,


Last month some of you were kind enough to help me with some date genreation.

Public Function fncInWeekDay(ByVal dte As Date, strWeekDay As String) As Boolean
    Const holiday_tbl As String = "tblHolidays"
    Const holiday_field As String = "HolidayDate"
    Dim lngCount As Long
    fncInWeekDay = (InStr(1, strWeekDay, Weekday(dte, 2) & "") > 0)
    If fncInWeekDay Then
        lngCount = DCount("1", holiday_tbl, holiday_field & "=#" & Format(dte, "mm\/dd\/yyyy") & "#")
        fncInWeekDay = fncInWeekDay And (lngCount = 0)
    End If
End Function

This function uses a table 'tblHolidays' to exclude any days that are holidays (basically when students are not in). The query the function is being used has one field where the name of the organisation is specified. Now, the problem is that some organisations have different holiday dates. I've created another table (tblHolidays2) which is identical in structure to the one already being used below but has slightly different holiday days.


I'm thinking to modify the code below to something as follows (meta code):

If [myquery]![fieldProvider]="non_standard_provider" Then
    Const holiday_tbl As String = "tblHolidays2"
Else
    Const holiday_tbl As String = "tblHolidays2
Endif

How do I actually access the fieldProvider value in the same record and would the above idea work? Is changing a const ok?

 

[isladogs]

You shouldn't create multiple tables. Instead add an extra field to the tblHolidays table to indicate which provider the holiday applies to. If the same date is used for several providers, you will have several records. You then use a query to filter the dates for each provider

 

[arnelgp]

just modify the function.
you then call it:

fncInWeekDay([dateField], "theWeekDayName", [fieldProvider]) As someName

Public Function fncInWeekDay(ByVal dte As Date, strWeekDay As String, Optional Provider As Variant=Null) As Boolean
    Const holiday_field As String = "HolidayDate"
    Dim holiday_tbl As String
    Dim lngCount As Long
    holiday_tbl = "tblHolidays"
    If (Provider & "") = "non_standard_provider" Then holiday_tbl = "tblHolidays2"
    fncInWeekDay = (InStr(1, strWeekDay, Weekday(dte, 2) & "") > 0)
    If fncInWeekDay Then
        lngCount = DCount("1", holiday_tbl, holiday_field & "=#" & Format(dte, "mm\/dd\/yyyy") & "#")
        fncInWeekDay = fncInWeekDay And (lngCount = 0)
    End If
End Function

 

[Ports]

Thank you. Will try it.