Function In SQL Problem

[Seikyo]

How come I can use this function in SQL Queries? It said that my function is
not defined. Sighx... Can anyone check if there is any problem with it? I
modified from some source code to suit my project.

Public Function OverTime(dteSCHD As Date, dteEnd As Date, dteBase As Date,
dteLate As Date, Optional vSeparator As Variant = ":") As Variant
Dim iHr As Integer
Dim iHrTemp As Integer
Dim iMin As Integer
Dim iMinTemp As Integer
Dim dteTemp As Date
Dim vTemp As Variant
Dim bSwapped As Boolean

OverTime = Null
bSwapped = False

'Check that both dates are valid
If Not IsDate(dteSCHD) Or Not IsDate(dteEnd) Or Not IsDate(dteBase) Then
DoCmd.Beep
MsgBox "You must supply valid dates.", vbOKOnly + vbExclamation,
"Invalid date"
Exit Function
End If

'Check that dteStart < dteEnd
If dteSCHD > dteEnd Then
'dteStart > dteEnd. Swap them
dteTemp = dteSCHD
dteSCHD = dteEnd
dteEnd = dteTemp
bSwapped = True
End If

'Calculate the time differences
iHr = DateDiff("h", dteSCHD, dteEnd) - _
IIf(Format(dteSCHD, "nnss") <= Format(dteEnd, "nnss"), 0, 1)
dteSCHD = DateAdd("h", iHr, dteSCHD)
iMin = DateDiff("n", dteSCHD, dteEnd) - _
IIf(Format(dteSCHD, "ss") <= Format(dteEnd, "ss"), 0, 1)
dteSCHD = DateAdd("n", iMin, dteSCHD)

If iHr >= 6 Then
iHr = DateDiff("h", 1, iHr) ' Lunch Hour (Standard 1 Hour)
iHrTemp = DateDiff("h", dteBase, iHr) - _
IIf(Format(dteBase, "nnss") <= Format(iHr, "nnss"), 0, 1) 'Calculate
the time differences
dteBase = DateAdd("h", iHrTemp, dteBase)
iHr = iHrTemp
If (iHr = 0) And (iMin < 10) Then
iHr = 0
iMin = 0
ElseIf (iHr < 0) Then
dteLate = DateAdd("h", Abs(iHr), dteLate)
dteLate = DateAdd("n", Abs(iMin), dteLate)
End If
Else
iHr = DateDiff("h", dteBase, iHr) - _
IIf(Format(dteBase, "nnss") <= Format(iHr, "nnss"), 0, 1) 'Calculate
the time differences
dteBase = DateAdd("h", iHr, dteBase)
If (iHr = 0) And (iMin < 10) Then
iHr = 0
iMin = 0
ElseIf (iHr < 0) Then
dteLate = DateAdd("h", Abs(iHr), dteLate)
dteLate = DateAdd("n", Abs(iMin), dteLate)
End If
End If

vTemp = iHr & vSeparator & iMin

OverTime = IIf(bSwapped, "-", "") & vTemp

'Debug.Print iHrs & ":" & iMin

End Function

 

[Duane Hookom]

You shouldn't put a message box in a function called in a query. Also, is
the a "project/adp"?
Is the function saved in a module? Is the name of the module not the same as
the function?

 

[Seikyo]

I take your advice, I will remove the message box in the function.

The function name is the same as the module name created. Is there a problem
in that?

My project is a mdb file. Normal Standalone Computer Usage.

 

[Dirk Goldgar]

I take your advice, I will remove the message box in the function.

The function name is the same as the module name created. Is there a
problem in that?

Yes. The function must not have the same name as the module that
creates it; actually, it must not have the same name as any other
public object or variable. Since the module's name doesn't really
matter, rename the module, maybe by sticking a prefix like "mod" or
"bas" on the front of it.