Argument problem

[PeteyP]

Getting the 'type mismatch' error for this macro's function name action
argument and I don't know how to enter the requirements for defining dates,
although I have researched this as best I can. Can anyone help me out?
Thanks in advance.

The action argument is:
WorkingDays2([Forms]![TestForm1]![Val1],[Forms]![TestForm1]![PlanD1]-[Forms]![TestForm1]![ActD1])

The name of the macro is WorkingDays2.
The name of the module is WorkingDays.
The name of the Function is WorkingDays2. In it, I am calculating the
number of business days between two dates. Using the macro, I am attempting
to show the difference on a form in a control named Val1.

 

[PeteyP]

And, by the way, the field for Val1 control is general number.

 

[Steve Schapel]

Petey,

My guess is that you are using a RunCode action in your macro... Am I
right? If so, I think you need a = in front of the function name argument.

Apart from that, you will need to give more information. Since the
WorkingDays2() function is exclusive to your database, maybe you could
copy/paste the VBA code for this function into your return post, which
might help us to see what's happening. Also helpful to say what event
you are using to run this macro.

 

[PeteyP]

Thanks, Steve, for your willingness to look at this. Yes, I am using
RunCode and have tried it with both the = and without it; no success there.
The Event I'm using to kick off the macro is On Load, for the form control
properties. Here is the VB code:

Option Compare Database

Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
'Dim rst As DAO.Recordset
'Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

Thanks again.

Petey,

My guess is that you are using a RunCode action in your macro... Am I
right? If so, I think you need a = in front of the function name argument.

Apart from that, you will need to give more information. Since the
WorkingDays2() function is exclusive to your database, maybe you could
copy/paste the VBA code for this function into your return post, which
might help us to see what's happening. Also helpful to say what event
you are using to run this macro.

--
Steve Schapel, Microsoft Access MVP

Getting the 'type mismatch' error for this macro's function name action
argument and I don't know how to enter the requirements for defining dates,
although I have researched this as best I can. Can anyone help me out?
Thanks in advance.

The action argument is:
WorkingDays2([Forms]![TestForm1]![Val1],[Forms]![TestForm1]![PlanD1]-[Forms]![TestForm1]![ActD1])

The name of the macro is WorkingDays2.
The name of the module is WorkingDays.
The name of the Function is WorkingDays2. In it, I am calculating the
number of business days between two dates. Using the macro, I am attempting
to show the difference on a form in a control named Val1.

 

[Steve Schapel]

Petey,

Well, first thing is, there seems to be an error in the code for the
function, in that the two lines:
'Dim rst As DAO.Recordset
'Dim DB As DAO.Database
.... are commented out. Please remove the ' from the beginning of those
lines.

But the main thing is you have used invalid syntax in your call to the
function. If you look at the code for the WorkingDays2() function, you
will see that it takes 2 arguments, being a start date and an end date.
So what you have put...

WorkingDays2([Forms]![TestForm1]![Val1],[Forms]![TestForm1]![PlanD1]-[Forms]![TestForm1]![ActD1])
.... doesn't look right. Assuming you want the number of "working days"
between the PlanD1 and the ActD1, I think it should be like this...
WorkingDays2([Forms]![TestForm1]![PlanD1],[Forms]![TestForm1]![ActD1])

--
Steve Schapel, Microsoft Access MVP

Thanks, Steve, for your willingness to look at this. Yes, I am using
RunCode and have tried it with both the = and without it; no success there.
The Event I'm using to kick off the macro is On Load, for the form control
properties. Here is the VB code:

Option Compare Database

Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
'Dim rst As DAO.Recordset
'Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

Thanks again.

Petey,

My guess is that you are using a RunCode action in your macro... Am I
right? If so, I think you need a = in front of the function name argument.

Apart from that, you will need to give more information. Since the
WorkingDays2() function is exclusive to your database, maybe you could
copy/paste the VBA code for this function into your return post, which
might help us to see what's happening. Also helpful to say what event
you are using to run this macro.

--
Steve Schapel, Microsoft Access MVP

Getting the 'type mismatch' error for this macro's function name action
argument and I don't know how to enter the requirements for defining dates,
although I have researched this as best I can. Can anyone help me out?
Thanks in advance.

The action argument is:
WorkingDays2([Forms]![TestForm1]![Val1],[Forms]![TestForm1]![PlanD1]-[Forms]![TestForm1]![ActD1])

The name of the macro is WorkingDays2.
The name of the module is WorkingDays.
The name of the Function is WorkingDays2. In it, I am calculating the
number of business days between two dates. Using the macro, I am attempting
to show the difference on a form in a control named Val1.

 

[PeteyP]

OK, Steve, thank you. I took out the comments and learned to make sure that
DAO 3.6 Library is checked in the Tools|References area.

I also changed the call to read as you suggested. Happily everything opens
and runs without errors; unfortunately, no results are returned.

I'd understood from the comment lines that the code accepts two dates and
calculates the number of business days between them. How do the placeholders
in the code recognize my field names, Pland1 and ActD1 and how does the
function know that I want to show the result in a form control called Var1 ?

Thanks again.
Pete

Petey,

Well, first thing is, there seems to be an error in the code for the
function, in that the two lines:
'Dim rst As DAO.Recordset
'Dim DB As DAO.Database
.... are commented out. Please remove the ' from the beginning of those
lines.

But the main thing is you have used invalid syntax in your call to the
function. If you look at the code for the WorkingDays2() function, you
will see that it takes 2 arguments, being a start date and an end date.
So what you have put...

WorkingDays2([Forms]![TestForm1]![Val1],[Forms]![TestForm1]![PlanD1]-[Forms]![TestForm1]![ActD1])
.... doesn't look right. Assuming you want the number of "working days"
between the PlanD1 and the ActD1, I think it should be like this...
WorkingDays2([Forms]![TestForm1]![PlanD1],[Forms]![TestForm1]![ActD1])

--
Steve Schapel, Microsoft Access MVP

Thanks, Steve, for your willingness to look at this. Yes, I am using
RunCode and have tried it with both the = and without it; no success there.
The Event I'm using to kick off the macro is On Load, for the form control
properties. Here is the VB code:

Option Compare Database

Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
'Dim rst As DAO.Recordset
'Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

Thanks again.

Petey,

My guess is that you are using a RunCode action in your macro... Am I
right? If so, I think you need a = in front of the function name argument.

Apart from that, you will need to give more information. Since the
WorkingDays2() function is exclusive to your database, maybe you could
copy/paste the VBA code for this function into your return post, which
might help us to see what's happening. Also helpful to say what event
you are using to run this macro.

--
Steve Schapel, Microsoft Access MVP

PeteyP wrote:
Getting the 'type mismatch' error for this macro's function name action
argument and I don't know how to enter the requirements for defining dates,
although I have researched this as best I can. Can anyone help me out?
Thanks in advance.

The action argument is:
WorkingDays2([Forms]![TestForm1]![Val1],[Forms]![TestForm1]![PlanD1]-[Forms]![TestForm1]![ActD1])

The name of the macro is WorkingDays2.
The name of the module is WorkingDays.
The name of the Function is WorkingDays2. In it, I am calculating the
number of business days between two dates. Using the macro, I am attempting
to show the difference on a form in a control named Val1.

 

[Steve Schapel]

Pete,

Ah, ok, now I start to see what you are trying to achieve!

I assume you have valid dates entered in the Pland1 and ActD1 controls
on the form. When you put references to these controls into the
arguments of the function, the function will use them as its StartDate
and EndDate variables, which is exactly what you want. If you want the
result to be shown in the Var1 textbox, then you should just nter the
function into the Control Source property of Var1 - with a = in front.
This is not a job for a macro after all. Also, since Var1 is on the
same form as Pland1 and ActD1, there is probably no need for the Forms!
reference. Just put the Control Source of Var1 like this...
=WorkingDays2([PlanD1],[ActD1])
Let us know how that goes.

 

[PeteyP]

T H A N K Y O U S T E V E

Thanks to you, my week ended on an upbeat note!

Pete,

Ah, ok, now I start to see what you are trying to achieve!

I assume you have valid dates entered in the Pland1 and ActD1 controls
on the form. When you put references to these controls into the
arguments of the function, the function will use them as its StartDate
and EndDate variables, which is exactly what you want. If you want the
result to be shown in the Var1 textbox, then you should just nter the
function into the Control Source property of Var1 - with a = in front.
This is not a job for a macro after all. Also, since Var1 is on the
same form as Pland1 and ActD1, there is probably no need for the Forms!
reference. Just put the Control Source of Var1 like this...
=WorkingDays2([PlanD1],[ActD1])
Let us know how that goes.

--
Steve Schapel, Microsoft Access MVP

OK, Steve, thank you. I took out the comments and learned to make sure that
DAO 3.6 Library is checked in the Tools|References area.

I also changed the call to read as you suggested. Happily everything opens
and runs without errors; unfortunately, no results are returned.

I'd understood from the comment lines that the code accepts two dates and
calculates the number of business days between them. How do the placeholders
in the code recognize my field names, Pland1 and ActD1 and how does the
function know that I want to show the result in a form control called Var1 ?

 

[Steve Schapel]

Well, very happy to hear it, Pete. Best wishes with the rest of your
project.

 

[PeteyP]

I may be celebrating too soon, altho my gratitude abounds each time I write
here.

The formula works if PlanD1 is less than (earlier) than ActD1. If the other
way around, get the Overflow error. I think maybe more code is required in
order to instruct Access what to do if the ActD1 is less than PlanD1.

 

[Steve Schapel]

Yes, Pete, it looks like the function assumes the dates will be
chronological. Rather than altering the code in the function itself, I
think I would be inclined to adjust in the expression you use. One
example...

=IIf([PlanD1]<[ActD1],WorkingDays2([PlanD1],[ActD1]),WorkingDays2([ActD1],[PlanD1]))

Does that do what you want?

 

[PeteyP]

Almost, Steve. Your solution is straightforward and simple. How can I make
the calulation return a negative value when [PlanD1]<[ActD1]? Will I need to
convert my dates into serial values?

Thanks for your continued help.

Yes, Pete, it looks like the function assumes the dates will be
chronological. Rather than altering the code in the function itself, I
think I would be inclined to adjust in the expression you use. One
example...

=IIf([PlanD1]<[ActD1],WorkingDays2([PlanD1],[ActD1]),WorkingDays2([ActD1],[PlanD1]))

Does that do what you want?

--
Steve Schapel, Microsoft Access MVP

I may be celebrating too soon, altho my gratitude abounds each time I write
here.

The formula works if PlanD1 is less than (earlier) than ActD1. If the other
way around, get the Overflow error. I think maybe more code is required in
order to instruct Access what to do if the ActD1 is less than PlanD1.

 

[Steve Schapel]

Pete,

As for converting dates to serial values, the dates are already stored
in your database as numbers, and just shown in date format, so no
conversion applies.

Wel, I guess one way would be to re-write the WorkingDays function to
accommodate to this situation. More kludgy but effective approach would
be to simply force a minus...

=IIf([PlanD1]<[ActD1],-WorkingDays2([PlanD1],[ActD1]),WorkingDays2([ActD1],[PlanD1]))

 

[PeteyP]

Thanks, Steve. I had no idea that could be done. I fiddled with it a dozen
different ways and read dozens of posts. So, I learned a lot in the
process, but you always had the exact answers needed. Thank you V E R Y
much.

Pete

Pete,

As for converting dates to serial values, the dates are already stored
in your database as numbers, and just shown in date format, so no
conversion applies.

Wel, I guess one way would be to re-write the WorkingDays function to
accommodate to this situation. More kludgy but effective approach would
be to simply force a minus...

=IIf([PlanD1]<[ActD1],-WorkingDays2([PlanD1],[ActD1]),WorkingDays2([ActD1],[PlanD1]))



--
Steve Schapel, Microsoft Access MVP

Almost, Steve. Your solution is straightforward and simple. How can I make
the calulation return a negative value when [PlanD1]<[ActD1]? Will I need to
convert my dates into serial values?