New VB script

[jalenben1]

I wrote a script and trying to get it to work. My Pass Through Query SQL is as follows:

SELECT UNO_Incidents.AssignedToGroup, UNO_Incidents.IncidentID, UNO_Incidents.Category, UNO_Incidents.SymptomDeviceAlias, UNO_Incidents.Priority, UNO_Incidents.Status, UNO_Incidents.Type, UNO_Incidents.Region, UNO_Incidents.AssetType, UNO_Incidents."Sym-Type", UNO_Incidents.SubmitterGroup, UNO_Incidents.Description, UNO_Incidents.CreatedBy, UNO_Incidents.AssignedToFullName, UNO_Incidents.DateCreated, UNO_Incidents."Restoral-Date/Time", UNO_Incidents."Occurrence-Date", UNO_Incidents.ResolutionCategory, UNO_Incidents.ResolutionSubCategory, UNO_Incidents.ResolutionSpecifics, UNO_Incidents.Impact, UNO_Incidents.Node, UNO_Incidents.ModifiedTime
FROM UNO_Incidents UNO_Incidents
WHERE (UNO_Incidents.AssignedToGroup='CORP - Huawei Tier II') AND (UNO_Incidents.DateCreated>={ts '2011-01-01 00:00:00'})
ORDER BY UNO_Incidents.DateCreated DESC

My VBA script

Private Sub Generate_Huawei_Tier_II_Report_Click()
On Error GoTo Err_Generate_Huawei_Tier_II_Report_Click

Dim stDocName As String
stDocName = " Huawei Tier II Report"
Dim stWhere As String
Dim strQueryName As String
strQueryName = "qryHuaweiTierII Query"

If Not IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] <= " & SQLDate(Me.txtEnding)
ElseIf IsDate(Me.txtBeginning) And Not IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] >= " & SQLDate(Me.txtBeginning)
ElseIf IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] Between #" & Me.txtBeginning & "# And #" & Me.txtEnding & "#"

End If
'MsgBox stWhere
Debug.Print "Beginning: " & Me.txtBeginning & "Ending: " & Me.txtEnding & " StWhere: " & stWhere
DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_Generate_Huawei_Tier_II_Report_Click:

Exit Sub

Err_Generate_Huawei_Tier_II_Report_Click:
MsgBox Err.Description

End Sub

The error message I got was Compile error Sub or Function not defined

Help please?

 

[jalenben1]

More details...I have a command button called Generate Huawei Tier Report and when I clicked on it I got the above error message. I want to be able to put in dates beginning and ending and/or just generate the report.

 

[MajP]

do you have a SQLDATE function somewhere else?

 

[jalenben1]

Here is my SQL Date function:

Function SQLDate(varDate As Variant) As Variant
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
End If
End If



End Function


I think I need to create this right behind my script for this to work. Am I right?

 

[MajP]

Based on the other
"Sub or Function not defined"
my first guess would be that it can not find your SQLDATE function. I do not know where it is currently located, but I would put it in a standard module and make it a public function.

Public Function SQLDate(varDate As Variant) As Variant

I think the default is public, but it might be private if you do not specify.

 

[dhookom]

Also, make sure the name of your module is not "SQLDate".

You might as well get rid of

    Dim strQueryName As String
    strQueryName = "qryHuaweiTierII Query"

You are not using the code.

I also suggested the SQLDate() wasn't the same as Allen Browne has on his site.

Duane
Hook'D on Access
MS Access MVP