Business Days

[Moses]

Yes there was some misspelling going on that has been corrected using the
code given by Douglas. My problem now is that I cannot get the query to call
the function I still get the following message:
Undifined Function, 'CalcWorkDays' in expression
My query is using the following code:
Here is the SQL code behind my query:
SELECT MTGLIBP1_CHMSTR01.[ACCT#], MTGLIBP1_CHMSTR01.INTPTD,
MTGLIBP1_CHBKRPCY.BKDISR, MTGLIBP1_CHBKRPCY.BKCLOS
FROM MTGLIBP1_CHBKRPCY INNER JOIN MTGLIBP1_CHMSTR01 ON
MTGLIBP1_CHBKRPCY.[ACCT#] = MTGLIBP1_CHMSTR01.[ACCT#];
I am inserting the following code in a blank field in my query to use the
function:
=CalcWorkDays(BKDISR, BKCLOS)
which should count the number of business days between the BKDISR and BKCLOS
date fields.

This is not happening does any one have any further suggestions. Sorry for
all the trouble.

Thanks Douglas,

That code worked and I understood what you meant about First day being part
of the comment. However, I am still getting my first message:
Undifined Function, 'CalcWorkDays' in expression

Here is the SQL code behind my query:
SELECT MTGLIBP1_CHMSTR01.[ACCT#], MTGLIBP1_CHMSTR01.INTPTD,
MTGLIBP1_CHBKRPCY.BKDISR, MTGLIBP1_CHBKRPCY.BKCLOS
FROM MTGLIBP1_CHBKRPCY INNER JOIN MTGLIBP1_CHMSTR01 ON
MTGLIBP1_CHBKRPCY.[ACCT#] = MTGLIBP1_CHMSTR01.[ACCT#];

and I used the code you gave me for the module.

Earlier you said you had

In the TotalDays field in control source I have
=CalcWorkDays(Me.txtStart,Me.txtEnd)

and that you were getting an error "argument not optional". This could
happen if either txtStart or txtEnd were NULL.

If you're still getting the Undefined Function error, then perhaps
either you have a misspelling somewhere, or the function is defined as
Private Function rather than Public Function in your Module.

John W. Vinson[MVP]

 

[Douglas J. Steele]

Assuming you mean you're trying to add the function call into your query,
you don't want the = sign, and you should assign an Alias to name the
computed field.

Try putting

WorkDays: CalcWorkDays([BKDISR], [BKCLOS])

into a blank field on the "Field" row of the grid.

Exactly where is your function declared, btw? It needs to be in a true
module, not in the code associated with a form. As John points out, make
sure you don't have Private in the declaration (and, as you've already
discovered, don't name the module CalcWorkDays)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes there was some misspelling going on that has been corrected using the
code given by Douglas. My problem now is that I cannot get the query to
call
the function I still get the following message:
Undifined Function, 'CalcWorkDays' in expression
My query is using the following code:
Here is the SQL code behind my query:
SELECT MTGLIBP1_CHMSTR01.[ACCT#], MTGLIBP1_CHMSTR01.INTPTD,
MTGLIBP1_CHBKRPCY.BKDISR, MTGLIBP1_CHBKRPCY.BKCLOS
FROM MTGLIBP1_CHBKRPCY INNER JOIN MTGLIBP1_CHMSTR01 ON
MTGLIBP1_CHBKRPCY.[ACCT#] = MTGLIBP1_CHMSTR01.[ACCT#];
I am inserting the following code in a blank field in my query to use the
function:
=CalcWorkDays(BKDISR, BKCLOS)
which should count the number of business days between the BKDISR and
BKCLOS
date fields.

This is not happening does any one have any further suggestions. Sorry for
all the trouble.

Thanks Douglas,

That code worked and I understood what you meant about First day being
part
of the comment. However, I am still getting my first message:
Undifined Function, 'CalcWorkDays' in expression

Here is the SQL code behind my query:
SELECT MTGLIBP1_CHMSTR01.[ACCT#], MTGLIBP1_CHMSTR01.INTPTD,
MTGLIBP1_CHBKRPCY.BKDISR, MTGLIBP1_CHBKRPCY.BKCLOS
FROM MTGLIBP1_CHBKRPCY INNER JOIN MTGLIBP1_CHMSTR01 ON
MTGLIBP1_CHBKRPCY.[ACCT#] = MTGLIBP1_CHMSTR01.[ACCT#];

and I used the code you gave me for the module.

Earlier you said you had

In the TotalDays field in control source I have
=CalcWorkDays(Me.txtStart,Me.txtEnd)

and that you were getting an error "argument not optional". This could
happen if either txtStart or txtEnd were NULL.

If you're still getting the Undefined Function error, then perhaps
either you have a misspelling somewhere, or the function is defined as
Private Function rather than Public Function in your Module.

John W. Vinson[MVP]

 

[Moses]

Doug and everyone,

Thankyou this has definetely helped. I was able to pull the data that I
wanted. However, I have one more problem. The data is coming from AS400
database and didn't realize I needed to convert the date fields in Access in
order for the function to work.
Does anyone have any advise?

Assuming you mean you're trying to add the function call into your query,
you don't want the = sign, and you should assign an Alias to name the
computed field.

Try putting

WorkDays: CalcWorkDays([BKDISR], [BKCLOS])

into a blank field on the "Field" row of the grid.

Exactly where is your function declared, btw? It needs to be in a true
module, not in the code associated with a form. As John points out, make
sure you don't have Private in the declaration (and, as you've already
discovered, don't name the module CalcWorkDays)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes there was some misspelling going on that has been corrected using the
code given by Douglas. My problem now is that I cannot get the query to
call
the function I still get the following message:
Undifined Function, 'CalcWorkDays' in expression
My query is using the following code:
Here is the SQL code behind my query:
SELECT MTGLIBP1_CHMSTR01.[ACCT#], MTGLIBP1_CHMSTR01.INTPTD,
MTGLIBP1_CHBKRPCY.BKDISR, MTGLIBP1_CHBKRPCY.BKCLOS
FROM MTGLIBP1_CHBKRPCY INNER JOIN MTGLIBP1_CHMSTR01 ON
MTGLIBP1_CHBKRPCY.[ACCT#] = MTGLIBP1_CHMSTR01.[ACCT#];
I am inserting the following code in a blank field in my query to use the
function:
=CalcWorkDays(BKDISR, BKCLOS)
which should count the number of business days between the BKDISR and
BKCLOS
date fields.

This is not happening does any one have any further suggestions. Sorry for
all the trouble.

On Fri, 23 Dec 2005 11:16:02 -0800, "Moses"

Thanks Douglas,

That code worked and I understood what you meant about First day being
part
of the comment. However, I am still getting my first message:
Undifined Function, 'CalcWorkDays' in expression

Here is the SQL code behind my query:
SELECT MTGLIBP1_CHMSTR01.[ACCT#], MTGLIBP1_CHMSTR01.INTPTD,
MTGLIBP1_CHBKRPCY.BKDISR, MTGLIBP1_CHBKRPCY.BKCLOS
FROM MTGLIBP1_CHBKRPCY INNER JOIN MTGLIBP1_CHMSTR01 ON
MTGLIBP1_CHBKRPCY.[ACCT#] = MTGLIBP1_CHMSTR01.[ACCT#];

and I used the code you gave me for the module.


Earlier you said you had

In the TotalDays field in control source I have
=CalcWorkDays(Me.txtStart,Me.txtEnd)

and that you were getting an error "argument not optional". This could
happen if either txtStart or txtEnd were NULL.

If you're still getting the Undefined Function error, then perhaps
either you have a misspelling somewhere, or the function is defined as
Private Function rather than Public Function in your Module.

John W. Vinson[MVP]

 

[John Vinson]

Doug and everyone,

Thankyou this has definetely helped. I was able to pull the data that I
wanted. However, I have one more problem. The data is coming from AS400
database and didn't realize I needed to convert the date fields in Access in
order for the function to work.
Does anyone have any advise?

WorkDays: CalcWorkDays(CDate([BKDISR]), CDate([BKCLOS]))

assuming that the AS400 fields make sense as a date - i.e. if BKDISR
contains the text string "12/23/2005" or some other string which
Access can recognize as a date, you'll be fine. If it contains a
number in the AS400 date schema (whatever that is), or a text string
such as 20051223, or something else - you'll need to post what the
field actually contains.

John W. Vinson[MVP]

 

[Moses]

Doug,

Hope you had a great Holiday. The date that AS400 displays is 20050502. What
type of conversion do I have to make to get WorkDays: CalcWorkDays[BKDISR],
[BKCLOS]) to calculate the number of business days between these two fields.

Thanks,

Doug and everyone,

Thankyou this has definetely helped. I was able to pull the data that I
wanted. However, I have one more problem. The data is coming from AS400
database and didn't realize I needed to convert the date fields in Access in
order for the function to work.
Does anyone have any advise?

WorkDays: CalcWorkDays(CDate([BKDISR]), CDate([BKCLOS]))

assuming that the AS400 fields make sense as a date - i.e. if BKDISR
contains the text string "12/23/2005" or some other string which
Access can recognize as a date, you'll be fine. If it contains a
number in the AS400 date schema (whatever that is), or a text string
such as 20051223, or something else - you'll need to post what the
field actually contains.

John W. Vinson[MVP]

 

[Douglas J. Steele]

Is that a string, or a number?

If it's a number, try:

CalcWorkDays(CDate(Format([BKDISR], "yyyy/-mm/-dd")), CDate(Format([BKCLOS],
"yyyy/-mm/-dd")))

If it's a string, try:

CalcWorkDays(DateSerial(Left([BKDISR], 4), Mid([BKDISR], 5, 2),
Right([BKDISR], 2)), DateSerial(Left([BKCLOS], 4), Mid([BKCLOS], 5, 2),
Right([BKCLOS], 2)))

You could also write your own function to do the conversion, just in case
either of the dates is Null:

Function ConvertDate(AS400Date As Variant) As Date

If IsNull(AS400Date) Then
' Pick a default date?
ConvertDate = #1/1/1970#
Else
DateSerial(Left(AS400Date, 4), Mid(AS400Date, 5, 2), Right(AS400Date,
2))
End If

End Function

and then use

CalcWorkDays(ConvertDate([BKDISR]), ConvertDate([BKCLOS])))

(you probably want to put in some error handling into the ConvertDate...)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

Hope you had a great Holiday. The date that AS400 displays is 20050502.
What
type of conversion do I have to make to get WorkDays:
CalcWorkDays[BKDISR],
[BKCLOS]) to calculate the number of business days between these two
fields.

Thanks,

Doug and everyone,

Thankyou this has definetely helped. I was able to pull the data that I
wanted. However, I have one more problem. The data is coming from AS400
database and didn't realize I needed to convert the date fields in
Access in
order for the function to work.
Does anyone have any advise?

WorkDays: CalcWorkDays(CDate([BKDISR]), CDate([BKCLOS]))

assuming that the AS400 fields make sense as a date - i.e. if BKDISR
contains the text string "12/23/2005" or some other string which
Access can recognize as a date, you'll be fine. If it contains a
number in the AS400 date schema (whatever that is), or a text string
such as 20051223, or something else - you'll need to post what the
field actually contains.

John W. Vinson[MVP]