|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
|
|
And, by the way, the field for Val1 control is general number.
"PeteyP" wrote:
[Quoted Text] > 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.
|
|
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:
[Quoted Text] > 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.
|
|
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.
"Steve Schapel" wrote:
[Quoted Text] > 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.
>
|
|
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
PeteyP wrote:
[Quoted Text] > 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.
>
> "Steve Schapel" wrote:
>
>> 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.
|
|
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
"Steve Schapel" wrote:
[Quoted Text] > 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
>
> PeteyP wrote:
> > 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.
> >
> > "Steve Schapel" wrote:
> >
> >> 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.
>
|
|
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
PeteyP wrote:
[Quoted Text] > 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 ?
|
|
T H A N K Y O U S T E V E
Thanks to you, my week ended on an upbeat note!
"Steve Schapel" wrote:
[Quoted Text] > 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
>
> PeteyP wrote:
> > 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 ?
>
|
|
Well, very happy to hear it, Pete. Best wishes with the rest of your
project.
--
Steve Schapel, Microsoft Access MVP
PeteyP wrote:
[Quoted Text] > T H A N K Y O U S T E V E
>
> Thanks to you, my week ended on an upbeat note!
>
|
|
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" wrote:
[Quoted Text] > Well, very happy to hear it, Pete. Best wishes with the rest of your
> project.
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> PeteyP wrote:
> > T H A N K Y O U S T E V E
> >
> > Thanks to you, my week ended on an upbeat note!
> >
>
|
|
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
PeteyP wrote:
[Quoted Text] > 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.
|
|
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.
"Steve Schapel" wrote:
[Quoted Text] > 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
>
> PeteyP wrote:
> > 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.
>
|
|
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
PeteyP wrote:
[Quoted Text] > 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, 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
"Steve Schapel" wrote:
[Quoted Text] > 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
>
> PeteyP wrote:
> > 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?
>
|
|
|