davegri:
Thank you for the great recommendation... at this time, I'm experiencing some syntax issues.
Below code (copied into SQL query) executes fine:
Code:
INSERT INTO 001_tblRawData_SelectedCategories ( IncidentID, DTG, IncidentDate, IncidentTime,
Category, CategoryGroup, Submitted, IncidentType, Victim_3_PHDRCompletionDate )
SELECT IncidentID, DTG, IncidentDate, IncidentTime,
Category, CategoryGroup, Submitted, IncidentType, Victim_3_PHDRCompletionDate
FROM 000_tblRawData
WHERE ((([000_tblRawData].Category) In ('Assault')));
Now, I'm trying to convert this working SQL statements into multiple VBA lines to be concatenated. At this time, I'm testing the splitting and concatenation of SQL statements with only a few fields.
Code:
strSQL_00_Gen2 = "INSERT INTO 001_tblRawData_SelectedCategories ( IncidentID, DTG, IncidentDate, IncidentTime, " _
& "Category, CategoryGroup, Submitted, IncidentType, Victim_3_PHDRCompletionDate )"
strSQL_00_Gen3 = "SELECT IncidentID, DTG, IncidentDate, IncidentTime, " _
& "Category, CategoryGroup, Submitted, IncidentType, Victim_3_PHDRCompletionDate "
strSQL_00_Gen4 = "FROM 000_tblRawData"
SQLFinal = strSQL_00_Gen2 & strSQL_00_Gen3 & strSQL_00_Gen4
... the above lines are written before the WHERE clause which never changed and should be working:
Code:
'Build the IN string by looping through the listbox
For i = 0 To ListBoxActions1.ListCount - 1
If ListBoxActions1.Selected(i) Then
If ListBoxActions1.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & ListBoxActions1.Column(0, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Category] in (" & Left(strIN, Len(strIN) - 1) & ")"
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
'strSQL = strSQL & strWhere
SQLFinal = SQLFinal & strWhere
End If
Finally, now when selecting values from the form's listbox, a dialogue box popped up indicating "Item not found in this collection" (which is an error message that I coded).
Unless I'm mistaking, I believe the error lies within the four concatenated SQL statements:
SQLFinal = strSQL_00_Gen2 & strSQL_00_Gen3 & strSQL_00_Gen4
What am I missing? Are there unnessary blank space or did I do the next line coding with underscore and ampersand incorrectly?
Thanks,
EEH