Access XP - Code to searh records in a table that has not got focus

[ozinm]

Hi All,
I'm trying to create a function that returns true if a supplied date is during a State holiday e.g. during Easter.

I've got a table called StateHolidays.
It lists each holiday with a From & To date.

After trawling through Access's help and leaching some example code I came up with this:

Function IsStateHoliday(InputDate As Date) As Boolean
Dim dbs database
Dim rst As Recordset
dbs = CurrentDb
Set rst = dbs.OpenRecordset("StateHolidays")
rst.Index = "StateHolidayID"
rst.MoveFirst
rst.Filter = "StateHolidayFrom >= #" & InputDate & "# and StateHolidayTo <= #" & InputDate & "#"
IsStateHoliday = rst.EOF
rst.Close
End Function

Unfortunately when I execute it access doesn't recognise the type 'Database' although the help tells me that CurrentDb returns this type.

Anyway - whatever - I can't get this to work. Could someone suggest either a better way of doing this or point out what is wrong with my code.

Thanks in advance for any and all help

TTFN

Marc

 

[WayneRyan]

Marc,

It doesn't recognize database because of a Reference problem.
Search this forum for references and that should fix you up.

Alternatively, you could use a DLookUp Function, there are many
examples here.

Wayne

 

[ozinm]

How good is that !!!
It's amasing what little command pass you by when you teach yourself.

In case anyone wants to know this was the final working code:


Function IsStateHoliday(InputDate As Date) As Boolean
IsStateHoliday = Not IsNull(DLookup("[StateHolidayID]", "StateHolidays", _
"[StateHolidayFrom] <= #" & InputDate & "# AND [StateHolidayTo] >= #" & InputDate & "#"))
End Function


Thanks for your help!