Help with function

[Guest]

Hi All,

I'm trying to put together a function that will look at a date and determine
whether or not it's a "working day" (ie not a weekend or public holiday).
I've got a table (tblHolidays) with a field (HolDate) that contains a list
of public holidays. I want to compare the date (dtmTemp) with the values in
HolDate to see if it's a working day or not.

Here's the code I've come up with so far, borrowed and adapted from various
similar threads. However, I'm getting a "run-time error (3085) undefined
function 'dtmtemp' in expression".

This is undoubtedly an easy one for the experienced hands, but I wouldn't
fall into that category, more's the pity...



Public Function IsWorkingDay(dtmTemp As Date) As Boolean

Dim rsHolidays As Recordset

If Weekday(dtmTemp) = 7 Or Weekday(dtmTemp) = 1 Then IsWorkingDay = False

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HolDate] FROM tblHolidays
Where [HolDate] = dtmTemp()", dbOpenSnapshot)

If not rsHolidays.BOF And rsHolidays.EOF Then
IsWorkingDay = True
Else
IsWorkingDay = False
End If

rsHolidays.Close
Set rsHolidays = Nothing

End Function

 

[Guest]

Hi Mike,

Here is the culprit:

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HolDate] FROM tblHolidays
Where [HolDate] = dtmTemp()", dbOpenSnapshot)

dtmTemp() is not defined as a function... you need to reference your date
like this:

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HolDate] FROM tblHolidays
Where [HolDate] = #" & format(dtmTemp, "yyyy/mm/dd") & "#", dbOpenSnapshot)

Hope this helps.

Damian.

 

[Guest]

Thanks for the impressively speedy reply Damian.

I'm now getting a run-time error 13 Type Mismatch?

Mike

Hi Mike,

Here is the culprit:

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HolDate] FROM tblHolidays
Where [HolDate] = dtmTemp()", dbOpenSnapshot)

dtmTemp() is not defined as a function... you need to reference your date
like this:

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HolDate] FROM tblHolidays
Where [HolDate] = #" & format(dtmTemp, "yyyy/mm/dd") & "#", dbOpenSnapshot)

Hope this helps.

Damian.

Hi All,

I'm trying to put together a function that will look at a date and determine
whether or not it's a "working day" (ie not a weekend or public holiday).
I've got a table (tblHolidays) with a field (HolDate) that contains a list
of public holidays. I want to compare the date (dtmTemp) with the values in
HolDate to see if it's a working day or not.

Here's the code I've come up with so far, borrowed and adapted from various
similar threads. However, I'm getting a "run-time error (3085) undefined
function 'dtmtemp' in expression".

This is undoubtedly an easy one for the experienced hands, but I wouldn't
fall into that category, more's the pity...



Public Function IsWorkingDay(dtmTemp As Date) As Boolean

Dim rsHolidays As Recordset

If Weekday(dtmTemp) = 7 Or Weekday(dtmTemp) = 1 Then IsWorkingDay = False

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HolDate] FROM tblHolidays
Where [HolDate] = dtmTemp()", dbOpenSnapshot)

If not rsHolidays.BOF And rsHolidays.EOF Then
IsWorkingDay = True
Else
IsWorkingDay = False
End If

rsHolidays.Close
Set rsHolidays = Nothing

End Function

 

[Douglas J. Steele]

How did you declare rsHolidays?

If you simply used

Dim rsHolidays As Recordset

and you're using Access 2000 or newer, rsHolidays has (likely) been declared
as an ADO recordset, but you're trying to use DAO techniques on it.

Try:

Dim rsHolidays As DAO.Recordset

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the impressively speedy reply Damian.

I'm now getting a run-time error 13 Type Mismatch?

Mike

Hi Mike,

Here is the culprit:

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HolDate] FROM
tblHolidays
Where [HolDate] = dtmTemp()", dbOpenSnapshot)

dtmTemp() is not defined as a function... you need to reference your date
like this:

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HolDate] FROM
tblHolidays
Where [HolDate] = #" & format(dtmTemp, "yyyy/mm/dd") & "#",
dbOpenSnapshot)

Hope this helps.

Damian.

Hi All,

I'm trying to put together a function that will look at a date and
determine
whether or not it's a "working day" (ie not a weekend or public
holiday).
I've got a table (tblHolidays) with a field (HolDate) that contains a
list
of public holidays. I want to compare the date (dtmTemp) with the
values in
HolDate to see if it's a working day or not.

Here's the code I've come up with so far, borrowed and adapted from
various
similar threads. However, I'm getting a "run-time error (3085)
undefined
function 'dtmtemp' in expression".

This is undoubtedly an easy one for the experienced hands, but I
wouldn't
fall into that category, more's the pity...



Public Function IsWorkingDay(dtmTemp As Date) As Boolean

Dim rsHolidays As Recordset

If Weekday(dtmTemp) = 7 Or Weekday(dtmTemp) = 1 Then IsWorkingDay =
False

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HolDate] FROM
tblHolidays
Where [HolDate] = dtmTemp()", dbOpenSnapshot)

If not rsHolidays.BOF And rsHolidays.EOF Then
IsWorkingDay = True
Else
IsWorkingDay = False
End If

rsHolidays.Close
Set rsHolidays = Nothing

End Function

 

[Guest]

Thanks guys,

Both methods worked, but I used Dave's method for the reasons he mentioned.
Here's the impressively brief final code as implemented:

Function IsWorkingDay(dtmtemp As Date) As Boolean

If (Not IsNull(DLookup("[HolDate]", "[tblHolidays]", "[HolDate] = #" &
dtmtemp & "#"))) Or Weekday(dtmtemp) = 7 Or Weekday(dtmtemp) = 1 Then
IsWorkingDay = False
Else
IsWorkingDay = True
End If

End Function

You could save yourself some work and increase performance if you use the
DLookup function rather than the recordset method you are currently using:

If IsNull(dlookup("[holdate]","[holidays]", "[holdate] = #" & dtmTemp()
& "#")) Then
IsWorkingDay = False
Else
IsWorkingDay = True
End If

--
Dave Hargis, Microsoft Access MVP

Thanks for the impressively speedy reply Damian.

I'm now getting a run-time error 13 Type Mismatch?

Mike

Hi Mike,

Here is the culprit:

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HolDate] FROM tblHolidays
Where [HolDate] = dtmTemp()", dbOpenSnapshot)

dtmTemp() is not defined as a function... you need to reference your date
like this:

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HolDate] FROM tblHolidays
Where [HolDate] = #" & format(dtmTemp, "yyyy/mm/dd") & "#", dbOpenSnapshot)

Hope this helps.

Damian.


:

Hi All,

I'm trying to put together a function that will look at a date and determine
whether or not it's a "working day" (ie not a weekend or public holiday).
I've got a table (tblHolidays) with a field (HolDate) that contains a list
of public holidays. I want to compare the date (dtmTemp) with the values in
HolDate to see if it's a working day or not.

Here's the code I've come up with so far, borrowed and adapted from various
similar threads. However, I'm getting a "run-time error (3085) undefined
function 'dtmtemp' in expression".

This is undoubtedly an easy one for the experienced hands, but I wouldn't
fall into that category, more's the pity...



Public Function IsWorkingDay(dtmTemp As Date) As Boolean

Dim rsHolidays As Recordset

If Weekday(dtmTemp) = 7 Or Weekday(dtmTemp) = 1 Then IsWorkingDay = False

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HolDate] FROM tblHolidays
Where [HolDate] = dtmTemp()", dbOpenSnapshot)

If not rsHolidays.BOF And rsHolidays.EOF Then
IsWorkingDay = True
Else
IsWorkingDay = False
End If

rsHolidays.Close
Set rsHolidays = Nothing

End Function