Crosstab query on the fly

[cquinn]

i am tring to use a list box on a form to make a cross tab query on the
fly. I am getting a transform error when i run it. can anybody tell
me what this means. My sql is posted below.




Private Sub MakeFilterCriteria_Click()
On Error GoTo Err_MakeFilterCriteria

Dim strCritString As String
Dim strBuildString As String
Dim strFullString As String
Dim intPosWhere As Integer
Dim intPosSemi As Integer
Dim qd As QueryDef
Dim rst As DAO.Recordset
Dim frm As Form
Dim booFirstFlag As Boolean
Dim intSelItem As Variant


booFirstFlag = False 'this flag shows whether a where has yet
been added
Set frm = Forms("form- JobSite Production")
Set qd = CurrentDb.QueryDefs("query- JOBSITE PRODUCTION")
strFullString = qd.SQL ' gets the SQL from the existing query

'trim any existing where clause from the SQL
intPosWhere = InStr(1, strFullString, "WHERE")
intPosSemi = InStrRev(strFullString, ";")
If intPosWhere > 0 Then
strFullString = Left(strFullString, intPosWhere - 3)
Else: strFullString = Left(strFullString, intPosSemi - 1)
End If

'filter JobSite
If frm.JobCodeLst.ItemsSelected.Count Then
booFirstFlag = True
strCritString = "WHERE [query- JOBSITE LIST]![ID] In("
strBuildString = ""
For Each intSelItem In frm.JobCodeLst.ItemsSelected
strBuildString = strBuildString & "," &
frm.JobCodeLst.ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString)
- 1)
End If 'strips out superfluous leading comma
strCritString = strCritString & strBuildString & ")"
End If

'Write recoded SQL string back to query
strFullString = strFullString & vbCrLf & strCritString
qd.SQL = strFullString

'Check for no hits
Set rst = CurrentDb.OpenRecordset("query- JOBSITE PRODUCTION")
If rst.BOF And rst.EOF Then
MsgBox "NO Records to Process"
Exit Sub
End If

rst.Close ' free up resources

'Open the result QUERY
DoCmd.OpenQuery "query- JOBSITE PRODUCTION"
DoCmd.Close acForm, Me.Name

Set rst = Nothing 'free up resources
Set qd = Nothing 'free up resources

Exit_MakeFilterCriteria:
Exit Sub

Err_MakeFilterCriteria:
MsgBox Err.Description
Resume Exit_MakeFilterCriteria


End Sub

 

[Duane Hookom]

You didn't really show your sql, you shared your code. Try pull the SQL
property by using
Debug.Print strFullString
If you share this, we can probably find the issue.