how to reference a module

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi,

I have a module, named "MnthDayCalculation" that
calculates the number of months and days of a date range.
In this case, todays date "now()" and a date in the
future "[ClassDate]". I am trying to use this in a text
box of a report, but I am having trouble. I have the
function listed at the bottom of this.

I have tried using several things in the control source,
like: =Modules!MnthDayCalculation(datecount(Now(),
[ClassDate])). When I test the function in the Imediate
window of the function, as in: ? datecount(Now(),
#11/30/03#) ... this returns 0 Months 2 Days. But I am
having trouble figuring out how I would reference this in
a text box. How can I go about doing this? Thanks.



*******************************************************

Function datecount(varDOB As Variant, varDate As Variant)
As String

' PURPOSE: Determines the difference between two dates in
years, months and days.
' ARGUMENTS: (will accept either dates (e.g., #03/24/00#)
or strings (e.g., "03/24/00")
' varDOB: The earlier of two dates.
' varDate: The later of two dates.
' RETURNS: A string as years.months.days, e.g., (17.6.21)
' NOTES: To test: Type '? datecount
("03/04/83", "03/23/00") in the debug window. The function
will return "17.0.19".

Dim dteDOB As Date, dteDate As Date, dteHold As Date
Dim intOldMonths As Integer, intOldDays As Integer
Dim intNuMonths As Integer, intNudays As Integer
Dim intMonths As Integer, intDays As Integer

If IsDate(varDOB) And IsDate(varDate) Then
dteDOB = DateValue(varDOB)
dteDate = DateValue(varDate)

intOldMonths = Month(dteDOB)
intOldDays = Day(dteDOB)
intNuMonths = Month(dteDate)
intNudays = Day(dteDate)

If intNudays < intOldDays Then
intNudays = intNudays + 30
intNuMonths = intNuMonths - 1
End If

If intNuMonths < intOldMonths Then
intNuMonths = intNuMonths + 12
End If

intMonths = intNuMonths - intOldMonths
intDays = intNudays - intOldDays

datecount = LTrim(Str(intMonths)) & IIf(intMonths =
1, " Month ", " Months ") & LTrim(Str(Int(intDays))) & IIf
(intDays = 1, " Day ", " Days ")

End If
End Function
 
It's simply =datecount(Now(), #11/30/03#)

Since public procedure names must be unique, there's no need to qualify with
the module name.
 
Hi,

As long as you don't have two functions with the same name in your project,
or the name of your function is not the same as a name of a native Access
function, you don't have to qualify the function name with module name.

Just use the function name in your report, as you call it from immediate
window.

HTH,
Bogdan
 
Thanks for the help!!!!!!!!


-----Original Message-----
It's simply =datecount(Now(), #11/30/03#)

Since public procedure names must be unique, there's no need to qualify with
the module name.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Hi,

I have a module, named "MnthDayCalculation" that
calculates the number of months and days of a date range.
In this case, todays date "now()" and a date in the
future "[ClassDate]". I am trying to use this in a text
box of a report, but I am having trouble. I have the
function listed at the bottom of this.

I have tried using several things in the control source,
like: =Modules!MnthDayCalculation(datecount(Now(),
[ClassDate])). When I test the function in the Imediate
window of the function, as in: ? datecount(Now(),
#11/30/03#) ... this returns 0 Months 2 Days. But I am
having trouble figuring out how I would reference this in
a text box. How can I go about doing this? Thanks.



*******************************************************

Function datecount(varDOB As Variant, varDate As Variant)
As String

' PURPOSE: Determines the difference between two dates in
years, months and days.
' ARGUMENTS: (will accept either dates (e.g., #03/24/00#)
or strings (e.g., "03/24/00")
' varDOB: The earlier of two dates.
' varDate: The later of two dates.
' RETURNS: A string as years.months.days, e.g., (17.6.21)
' NOTES: To test: Type '? datecount
("03/04/83", "03/23/00") in the debug window. The function
will return "17.0.19".

Dim dteDOB As Date, dteDate As Date, dteHold As Date
Dim intOldMonths As Integer, intOldDays As Integer
Dim intNuMonths As Integer, intNudays As Integer
Dim intMonths As Integer, intDays As Integer

If IsDate(varDOB) And IsDate(varDate) Then
dteDOB = DateValue(varDOB)
dteDate = DateValue(varDate)

intOldMonths = Month(dteDOB)
intOldDays = Day(dteDOB)
intNuMonths = Month(dteDate)
intNudays = Day(dteDate)

If intNudays < intOldDays Then
intNudays = intNudays + 30
intNuMonths = intNuMonths - 1
End If

If intNuMonths < intOldMonths Then
intNuMonths = intNuMonths + 12
End If

intMonths = intNuMonths - intOldMonths
intDays = intNudays - intOldDays

datecount = LTrim(Str(intMonths)) & IIf(intMonths =
1, " Month ", " Months ") & LTrim(Str(Int(intDays))) & IIf
(intDays = 1, " Day ", " Days ")

End If
End Function
Click to expand...


.
Click to expand...
 
Hi,

I have a module, named "MnthDayCalculation" that
calculates the number of months and days of a date range.
In this case, todays date "now()"
Click to expand...

Not that Now() does NOT return today's date: it returns today's date
and the current time, accurate to microseconds. If you want just the
date you may want to use Date() instead.
and a date in the
future "[ClassDate]". I am trying to use this in a text
box of a report, but I am having trouble. I have the
function listed at the bottom of this.

I have tried using several things in the control source,
like: =Modules!MnthDayCalculation(datecount(Now(),
[ClassDate])). When I test the function in the Imediate
window of the function, as in: ? datecount(Now(),
#11/30/03#) ... this returns 0 Months 2 Days. But I am
having trouble figuring out how I would reference this in
a text box. How can I go about doing this? Thanks.
Click to expand...

=Datecount([DOB], Date())

The module name is irrelevant; if it's a Public Function, you can just
call it.
 
Back
Top