SQL Statement

  • Thread starter Thread starter news
  • Start date Start date
N

news

Thank you so much for your reply. Here is my complete code. I replace the
field with a variable in the HAVING clause but now the query does not get
created any more, I don't get any error message, looks like nothing happen.
I'm not sure what is going on.

Private Sub Command20_Click()

Dim db As DAO.Database
Dim i As Integer
Dim qdfQueryDef As QueryDef
Dim strOutputPath As String
Dim strSQLSelect As String
Dim strSelectedCriteria(2) As String
Dim strFinalCriteria As String
Dim strField1 As String
Dim strField2 As String


On Error Resume Next

'-----------------------------------------------
'Initialize Variables...
'-----------------------------------------------
strSelectedCriteria(0) = "[TW:
ProvidersContractEffDate].Pgm_str_dt_Formated, " 'Contract Start Date
strSelectedCriteria(1) = "[Pgm_str_dt_Formated]+120 AS [Complete By], "
'"Complete By" date
strSelectedCriteria(2) = " FLDVST.Dt_Visited, "
'Visited Date

Select Case Me.Criteria
Case Is = "Contract Start Date"
strFinalCriteria = strSelectedCriteria(0)
strField1 = strSelectedCriteria(1)
strField2 = strSelectedCriteria(2)
Case Is = "Complete by Date"
strFinalCriteria = strSelectedCriteria(1)
strField1 = strSelectedCriteria(0)
strField2 = strSelectedCriteria(2)
Case Is = "Visited Date"
strFinalCriteria = strSelectedCriteria(2)
strField1 = strSelectedCriteria(0)
strField2 = strSelectedCriteria(1)
End Select

Set db = CurrentDb()
strSQLSelect = _
"SELECT " _
& "[TW: ProvidersContractEffDate].Root," _
& "[TW: ProvidersContractEffDate].[Key Name], " _
& "FLDVST.Rep_Name, FLDVST.Region, " _
& strFinalCriteria _
& strField1 _
& strField2 _
& "FLDVST.TW , PURPOSE.PURPOSE " _
& "FROM [TW: ProvidersContractEffDate] LEFT JOIN (FLDVST LEFT JOIN
Purpose ON FLDVST.Rec_Num = Purpose.KEY_NO) ON [TW:
ProvidersContractEffDate].Root = FLDVST.[Root Number] " _
& "GROUP BY [TW: ProvidersContractEffDate].Root, [TW:
ProvidersContractEffDate].[Key Name], FLDVST.Rep_Name, FLDVST.Region, [TW:
ProvidersContractEffDate].Pgm_str_dt_Formated, [Pgm_str_dt_Formated]+120,
FLDVST.Dt_Visited, FLDVST.TW, Purpose.PURPOSE " _
& "HAVING (((" & strFinalCriteria & ")" _
& "Between #" _
& [Forms]![TW: Reports]![StrDate] & "# " _
& "And " _
& "#" & [Forms]![TW: Reports]![EndDate] & "#) " _
& "AND ((FLDVST.TW)=-1) " _
& "AND ((Purpose.PURPOSE)=" & Chr(34) & "Provider
Orientation/Education" & Chr(34) _
& IIf(Me.Region <> "", "AND [FLDVST].[Region]= " & Chr(34) &
Me.Region & Chr(34) & Space(1), "") & ")) " _
& "ORDER BY [TW: ProvidersContractEffDate].[Key Name]; "

Debug.Print strSQLSelect

'---------------------------------------------------
'--Delete the extract querydef if it exists...
'---------------------------------------------------
' For Each qdfQueryDef In db.QueryDefs
' If qdfQueryDef.Name = "SOLOMON'S QUERY TEST" Then
' db.QueryDefs.Delete qdfQueryDef.Name
' Exit For
' End If
' Next qdfQueryDef

' Debug.Print strSQLSelect
'---------------------------------------------------
'--Create Query Def
'---------------------------------------------------
Set qdfQueryDef = db.CreateQueryDef("SOLOMON'S QUERY TEST",
strSQLSelect)


CleanUp:
db.Close
Set db = Nothing

End Sub
 
Remove "On Error Resume Next", place a break point in your code, and then step through it 1 line
at a time using the F8 key.

Note: You should also set qdfQueryDef = Nothing at the end of your procedure.

Tom
__________________________________


Thank you so much for your reply. Here is my complete code. I replace the
field with a variable in the HAVING clause but now the query does not get
created any more, I don't get any error message, looks like nothing happen.
I'm not sure what is going on.

Private Sub Command20_Click()

Dim db As DAO.Database
Dim i As Integer
Dim qdfQueryDef As QueryDef
Dim strOutputPath As String
Dim strSQLSelect As String
Dim strSelectedCriteria(2) As String
Dim strFinalCriteria As String
Dim strField1 As String
Dim strField2 As String


On Error Resume Next

'-----------------------------------------------
'Initialize Variables...
'-----------------------------------------------
strSelectedCriteria(0) = "[TW:
ProvidersContractEffDate].Pgm_str_dt_Formated, " 'Contract Start Date
strSelectedCriteria(1) = "[Pgm_str_dt_Formated]+120 AS [Complete By], "
'"Complete By" date
strSelectedCriteria(2) = " FLDVST.Dt_Visited, "
'Visited Date

Select Case Me.Criteria
Case Is = "Contract Start Date"
strFinalCriteria = strSelectedCriteria(0)
strField1 = strSelectedCriteria(1)
strField2 = strSelectedCriteria(2)
Case Is = "Complete by Date"
strFinalCriteria = strSelectedCriteria(1)
strField1 = strSelectedCriteria(0)
strField2 = strSelectedCriteria(2)
Case Is = "Visited Date"
strFinalCriteria = strSelectedCriteria(2)
strField1 = strSelectedCriteria(0)
strField2 = strSelectedCriteria(1)
End Select

Set db = CurrentDb()
strSQLSelect = _
"SELECT " _
& "[TW: ProvidersContractEffDate].Root," _
& "[TW: ProvidersContractEffDate].[Key Name], " _
& "FLDVST.Rep_Name, FLDVST.Region, " _
& strFinalCriteria _
& strField1 _
& strField2 _
& "FLDVST.TW , PURPOSE.PURPOSE " _
& "FROM [TW: ProvidersContractEffDate] LEFT JOIN (FLDVST LEFT JOIN
Purpose ON FLDVST.Rec_Num = Purpose.KEY_NO) ON [TW:
ProvidersContractEffDate].Root = FLDVST.[Root Number] " _
& "GROUP BY [TW: ProvidersContractEffDate].Root, [TW:
ProvidersContractEffDate].[Key Name], FLDVST.Rep_Name, FLDVST.Region, [TW:
ProvidersContractEffDate].Pgm_str_dt_Formated, [Pgm_str_dt_Formated]+120,
FLDVST.Dt_Visited, FLDVST.TW, Purpose.PURPOSE " _
& "HAVING (((" & strFinalCriteria & ")" _
& "Between #" _
& [Forms]![TW: Reports]![StrDate] & "# " _
& "And " _
& "#" & [Forms]![TW: Reports]![EndDate] & "#) " _
& "AND ((FLDVST.TW)=-1) " _
& "AND ((Purpose.PURPOSE)=" & Chr(34) & "Provider
Orientation/Education" & Chr(34) _
& IIf(Me.Region <> "", "AND [FLDVST].[Region]= " & Chr(34) &
Me.Region & Chr(34) & Space(1), "") & ")) " _
& "ORDER BY [TW: ProvidersContractEffDate].[Key Name]; "

Debug.Print strSQLSelect

'---------------------------------------------------
'--Delete the extract querydef if it exists...
'---------------------------------------------------
' For Each qdfQueryDef In db.QueryDefs
' If qdfQueryDef.Name = "SOLOMON'S QUERY TEST" Then
' db.QueryDefs.Delete qdfQueryDef.Name
' Exit For
' End If
' Next qdfQueryDef

' Debug.Print strSQLSelect
'---------------------------------------------------
'--Create Query Def
'---------------------------------------------------
Set qdfQueryDef = db.CreateQueryDef("SOLOMON'S QUERY TEST",
strSQLSelect)


CleanUp:
db.Close
Set db = Nothing

End Sub
 
I don't see any place in your code where you give strFinalCriteria a value?


--

Ken Snell
<MS ACCESS MVP>

news said:
Thank you so much for your reply. Here is my complete code. I replace
the
field with a variable in the HAVING clause but now the query does not get
created any more, I don't get any error message, looks like nothing
happen.
I'm not sure what is going on.

Private Sub Command20_Click()

Dim db As DAO.Database
Dim i As Integer
Dim qdfQueryDef As QueryDef
Dim strOutputPath As String
Dim strSQLSelect As String
Dim strSelectedCriteria(2) As String
Dim strFinalCriteria As String
Dim strField1 As String
Dim strField2 As String


On Error Resume Next

'-----------------------------------------------
'Initialize Variables...
'-----------------------------------------------
strSelectedCriteria(0) = "[TW:
ProvidersContractEffDate].Pgm_str_dt_Formated, " 'Contract Start Date
strSelectedCriteria(1) = "[Pgm_str_dt_Formated]+120 AS [Complete By], "
'"Complete By" date
strSelectedCriteria(2) = " FLDVST.Dt_Visited, "
'Visited Date

Select Case Me.Criteria
Case Is = "Contract Start Date"
strFinalCriteria = strSelectedCriteria(0)
strField1 = strSelectedCriteria(1)
strField2 = strSelectedCriteria(2)
Case Is = "Complete by Date"
strFinalCriteria = strSelectedCriteria(1)
strField1 = strSelectedCriteria(0)
strField2 = strSelectedCriteria(2)
Case Is = "Visited Date"
strFinalCriteria = strSelectedCriteria(2)
strField1 = strSelectedCriteria(0)
strField2 = strSelectedCriteria(1)
End Select

Set db = CurrentDb()
strSQLSelect = _
"SELECT " _
& "[TW: ProvidersContractEffDate].Root," _
& "[TW: ProvidersContractEffDate].[Key Name], " _
& "FLDVST.Rep_Name, FLDVST.Region, " _
& strFinalCriteria _
& strField1 _
& strField2 _
& "FLDVST.TW , PURPOSE.PURPOSE " _
& "FROM [TW: ProvidersContractEffDate] LEFT JOIN (FLDVST LEFT JOIN
Purpose ON FLDVST.Rec_Num = Purpose.KEY_NO) ON [TW:
ProvidersContractEffDate].Root = FLDVST.[Root Number] " _
& "GROUP BY [TW: ProvidersContractEffDate].Root, [TW:
ProvidersContractEffDate].[Key Name], FLDVST.Rep_Name, FLDVST.Region, [TW:
ProvidersContractEffDate].Pgm_str_dt_Formated, [Pgm_str_dt_Formated]+120,
FLDVST.Dt_Visited, FLDVST.TW, Purpose.PURPOSE " _
& "HAVING (((" & strFinalCriteria & ")" _
& "Between #" _
& [Forms]![TW: Reports]![StrDate] & "# " _
& "And " _
& "#" & [Forms]![TW: Reports]![EndDate] & "#) " _
& "AND ((FLDVST.TW)=-1) " _
& "AND ((Purpose.PURPOSE)=" & Chr(34) & "Provider
Orientation/Education" & Chr(34) _
& IIf(Me.Region <> "", "AND [FLDVST].[Region]= " & Chr(34) &
Me.Region & Chr(34) & Space(1), "") & ")) " _
& "ORDER BY [TW: ProvidersContractEffDate].[Key Name]; "

Debug.Print strSQLSelect

'---------------------------------------------------
'--Delete the extract querydef if it exists...
'---------------------------------------------------
' For Each qdfQueryDef In db.QueryDefs
' If qdfQueryDef.Name = "SOLOMON'S QUERY TEST" Then
' db.QueryDefs.Delete qdfQueryDef.Name
' Exit For
' End If
' Next qdfQueryDef

' Debug.Print strSQLSelect
'---------------------------------------------------
'--Create Query Def
'---------------------------------------------------
Set qdfQueryDef = db.CreateQueryDef("SOLOMON'S QUERY TEST",
strSQLSelect)


CleanUp:
db.Close
Set db = Nothing

End Sub
Click to expand...
 
Back
Top