call function module from query - having much trouble

[Mitchell_Collen]

SELECT OrderStateTime, Diff2Dates(y, '06/01/1998', '06/26/2002') AS
Duration
FROM dboState

I am trying to use the funtion Diff2Dates created by Douglas Steele at
Pacific. I have opened Modules object window and clicked new and then pasted
Steele's code in the editor window. I have compiled it and got no errors. So
then I created the above query to test it.

However, when I run the above query it returns the following error:
ADO error: 'Diff2Dates' is not recognized function.


Please tell what I am doing wrong. Was I supposed to save it as a text file
and import it instead of pasting the code? I don't understand fully how to
call a module from a query. I renamed the module Diff2Dates <--not sure if
that was needed.

Please advise. Thanks in advance.
-Misty

Code by Douglas Steele:


'***************** Code Start **************
Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date,
_
Optional ShowZero As Boolean = False) As Variant

'Author: © Copyright 2001 Pacific Database Pty Limited
' Graham R Seach MCP MVP (e-mail address removed)
' Phone: +61 2 9872 9594 Fax: +61 2 9872 9593
' This code is freeware. Enjoy...
' (*) Amendments suggested by Douglas J. Steele MVP
'
'Description: This function calculates the number of years,
' months, days, hours, minutes and seconds between
' two dates, as elapsed time.
'
'Inputs: Interval: Intervals to be displayed (a string)
' Date1: The lower date (see below)
' Date2: The higher date (see below)
' ShowZero: Boolean to select showing zero elements
'
'Outputs: On error: Null
' On no error: Variant containing the number of years,
' months, days, hours, minutes & seconds between
' the two dates, depending on the display interval
' selected.
' If Date1 is greater than Date2, the result will
' be a negative value.
' The function compensates for the lack of any intervals
' not listed. For example, if Interval lists "m", but
' not "y", the function adds the value of the year
' component to the month component.
' If ShowZero is True, and an output element is zero, it
' is displayed. However, if ShowZero is False or
' omitted, no zero-value elements are displayed.
' For example, with ShowZero = False, Interval = "ym",
' elements = 0 & 1 respectively, the output string
' will be "1 month" - not "0 years 1 month".

On Error GoTo Err_Diff2Dates

Dim booCalcYears As Boolean
Dim booCalcMonths As Boolean
Dim booCalcDays As Boolean
Dim booCalcHours As Boolean
Dim booCalcMinutes As Boolean
Dim booCalcSeconds As Boolean
Dim booSwapped As Boolean
Dim dtTemp As Date
Dim intCounter As Integer
Dim lngDiffYears As Long
Dim lngDiffMonths As Long
Dim lngDiffDays As Long
Dim lngDiffHours As Long
Dim lngDiffMinutes As Long
Dim lngDiffSeconds As Long
Dim varTemp As Variant

Const INTERVALS As String = "dmyhns"

'Check that Interval contains only valid characters
Interval = LCase$(Interval)
For intCounter = 1 To Len(Interval)
If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
Exit Function
End If
Next intCounter

'Check that valid dates have been entered
If Not (IsDate(Date1)) Then Exit Function
If Not (IsDate(Date2)) Then Exit Function

'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
If Date1 > Date2 Then
dtTemp = Date1
Date1 = Date2
Date2 = dtTemp
booSwapped = True
End If

Diff2Dates = Null
varTemp = Null

'What intervals are supplied
booCalcYears = (InStr(1, Interval, "y") > 0)
booCalcMonths = (InStr(1, Interval, "m") > 0)
booCalcDays = (InStr(1, Interval, "d") > 0)
booCalcHours = (InStr(1, Interval, "h") > 0)
booCalcMinutes = (InStr(1, Interval, "n") > 0)
booCalcSeconds = (InStr(1, Interval, "s") > 0)

'Get the cumulative differences
If booCalcYears Then
lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss")
, 0, 1)
Date1 = DateAdd("yyyy", lngDiffYears, Date1)
End If

If booCalcMonths Then
lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0,
1)
Date1 = DateAdd("m", lngDiffMonths, Date1)
End If

If booCalcDays Then
lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
Date1 = DateAdd("d", lngDiffDays, Date1)
End If

If booCalcHours Then
lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
Date1 = DateAdd("h", lngDiffHours, Date1)
End If

If booCalcMinutes Then
lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
Date1 = DateAdd("n", lngDiffMinutes, Date1)
End If

If booCalcSeconds Then
lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
Date1 = DateAdd("s", lngDiffSeconds, Date1)
End If

If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
End If

If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
If booCalcMonths Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMonths & IIf(lngDiffMonths <> 1, " months", "
month")
End If
End If

If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
If booCalcDays Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
End If
End If

If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
End If
End If

If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
If booCalcMinutes Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", "
minute")
End If
End If

If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
If booCalcSeconds Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", "
second")
End If
End If

If booSwapped Then
varTemp = "-" & varTemp
End If

Diff2Dates = Trim$(varTemp)

End_Diff2Dates:
Exit Function

Err_Diff2Dates:
Resume End_Diff2Dates

End Function
'************** Code End *****************

 

[Klatuu]

It appears you have done the correct thing, but let's check a couple of things.
You did put this in a standard module and not a form module?
The name of the module should not be the same as the name of the function.

You are passing a variable y to the function instead of a string containing
the letter y and string values instead of date values that the function is
expecting. It sould be:

Diff2Dates(y, '06/01/1998', '06/26/2002')

 

[Mitchell_Collen via AccessMonster.com]

Yes, I am passing y and not string "y" and I pasted the code into standard
module. Do you think this is not recognizing it because I am using sql server?
I also tried naming my calling my function like this dbo.module7.
Diff2Dates() but to no avail.

Please advise.
-Misty

It appears you have done the correct thing, but let's check a couple of things.
You did put this in a standard module and not a form module?
The name of the module should not be the same as the name of the function.

You are passing a variable y to the function instead of a string containing
the letter y and string values instead of date values that the function is
expecting. It sould be:

Diff2Dates(y, '06/01/1998', '06/26/2002')

SELECT OrderStateTime, Diff2Dates('y', #06/01/1998#, #06/26/2002#) AS
Duration

[quoted text clipped - 204 lines]

End Function
'************** Code End *****************

 

[Douglas J. Steele]

Diff2Dates("y",#06/01/1998#, #06/26/2002#)

 

[Douglas J. Steele]

This isn't pass-through query, is it?

You must have linked tables in Access, pointing to the actual tables in SQL
Server. You'd then write a query in Access to use that linked table.

Also, as pointed out in my reply to Dave, the function expects dates, and
dates in Access are delimited with # characters:

Diff2Dates("y", #06/01/1998#, #06/26/2002#)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, I am passing y and not string "y" and I pasted the code into standard
module. Do you think this is not recognizing it because I am using sql
server?
I also tried naming my calling my function like this
dbo.module7.
Diff2Dates() but to no avail.

Please advise.
-Misty

It appears you have done the correct thing, but let's check a couple of
things.
You did put this in a standard module and not a form module?
The name of the module should not be the same as the name of the function.

You are passing a variable y to the function instead of a string
containing
the letter y and string values instead of date values that the function is
expecting. It sould be:

Diff2Dates(y, '06/01/1998', '06/26/2002')

SELECT OrderStateTime, Diff2Dates('y', #06/01/1998#, #06/26/2002#)
AS
Duration

[quoted text clipped - 204 lines]

End Function
'************** Code End *****************

 

[Mitchell_Collen via AccessMonster.com]

When I use that syntax the .adp database gives this error: error in list of
function arguments: # not recognized unable to parse query text and then it
trys to correct it by rewiting it as Diff2Dates([y], #06 / 01 / 1998 #, #06 /
26 / 2002 #) AS Duration, however when I run it like
Diff2Dates("y",'06/01/1998','06/26/2002')As Duration, the error says function
not recognized.

I wonder if I save the funtion as a .bas and import into a module. I don't
know, maybe that sounds silly.

-Misty

Diff2Dates("y",#06/01/1998#, #06/26/2002#)

It appears you have done the correct thing, but let's check a couple of
things.

[quoted text clipped - 227 lines]

 

[Douglas J. Steele]

You never said anything about it being an ADP. That code is intended for
MDBs.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

When I use that syntax the .adp database gives this error: error in list
of
function arguments: # not recognized unable to parse query text and then
it
trys to correct it by rewiting it as Diff2Dates([y], #06 / 01 / 1998 #,
#06 /
26 / 2002 #) AS Duration, however when I run it like
Diff2Dates("y",'06/01/1998','06/26/2002')As Duration, the error says
function
not recognized.

I wonder if I save the funtion as a .bas and import into a module. I don't
know, maybe that sounds silly.

-Misty

Diff2Dates("y",#06/01/1998#, #06/26/2002#)

It appears you have done the correct thing, but let's check a couple of
things.

[quoted text clipped - 227 lines]

End Function
'************** Code End *****************

 

[Mitchell_Collen via AccessMonster.com]

I apologize about that, I am new to Access being linked to SQL server and
just now thought it being different. But, I was thinking that even though
it's intended for the regular mdb, that it would work in .adp as long as the
module is in my instance. I am sooo new to this. I'll keep trying to get it
to work and I'll update this thread if it ends of working or I find an
alternative.

Thanks everyone for helping out.
-Misty

You never said anything about it being an ADP. That code is intended for
MDBs.

When I use that syntax the .adp database gives this error: error in list
of

[quoted text clipped - 19 lines]

 

[Klatuu]

Thanks, Douglas. Got distracted and did not complete before sending.
--
Dave Hargis, Microsoft Access MVP

This isn't pass-through query, is it?

You must have linked tables in Access, pointing to the actual tables in SQL
Server. You'd then write a query in Access to use that linked table.

Also, as pointed out in my reply to Dave, the function expects dates, and
dates in Access are delimited with # characters:

Diff2Dates("y", #06/01/1998#, #06/26/2002#)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, I am passing y and not string "y" and I pasted the code into standard
module. Do you think this is not recognizing it because I am using sql
server?
I also tried naming my calling my function like this
dbo.module7.
Diff2Dates() but to no avail.

Please advise.
-Misty

It appears you have done the correct thing, but let's check a couple of
things.
You did put this in a standard module and not a form module?
The name of the module should not be the same as the name of the function.

You are passing a variable y to the function instead of a string
containing
the letter y and string values instead of date values that the function is
expecting. It sould be:

Diff2Dates(y, '06/01/1998', '06/26/2002')
SELECT OrderStateTime, Diff2Dates('y', #06/01/1998#, #06/26/2002#)
AS
Duration
[quoted text clipped - 204 lines]
End Function
'************** Code End *****************