Dropping a recordset into Excel

[bstice]

I am trying to drop a recordset into excel and everything seems to be working but no records are coming through. I am trying to dynamically change one of the fields ((GLData." & FIELD1 & ")='" & crit1 & "') in the where clause and I think that is causing the problem. Could someone take a look and provide feedback? I really appreciate it. Thanks

Brennan

Private Sub detailreport()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlapp As Object, xlwb As Object, xlws As Object
Dim fldCount As Integer
Dim iCol As Integer
Dim str As String
Dim combo1 As String
Dim FIELD1 As String
Dim crit1 As String
Dim pstype As String

pstype = Me.pstype
combo1 = Me.AccountingPeriod
FIELD1 = Me.field
crit1 = Me.OrgDetail
str = "SELECT GLData.[Accounting Period *], GLData.[FML Organization Code *], GLData.[Posted Date], GLData.[Journal Entry Source Name], GLData.[Titan Voucher Number], GLData.[Journal Entry Created By Last Name], GLData.[Journal Entry Created By Phone Nbr], GLData.[FML Account Code *], GLData.[FML CSUB Account Code *], GLData.[FML CSUB Account Name *], GLData.[Debit Amount - US], GLData.[Credit Amount - US], GLData.[Net Amount - US], GLData.[Journal Entry Line Description] FROM GLData WHERE (((GLData.[Accounting Period *])='" & combo1 & "') AND ((GLData." & FIELD1 & ")='" & crit1 & "') AND ((GLData.Measure)='" & pstype & "'));"
Set db = CurrentDb
Set rs = db.OpenRecordset(str, dbOpenSnapshot)
Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add
Set xlws = xlwb.Worksheets("Sheet1")

xlapp.Visible = True
xlapp.UserControl = True

fldCount = rs.Fields.Count
For iCol = 1 To fldCount
xlws.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next
xlws.Cells(2, 1).CopyFromRecordset rs
xlapp.Selection.CurrentRegion.Columns.AutoFit
xlapp.Selection.CurrentRegion.Rows.AutoFit
rs.Close

Set rs = Nothing

Set xlws = Nothing
Set xlwb = Nothing
Set xlapp = Nothing

End Sub

 

[pbaldy]

After the string is set add this:

Debug.Print str

and examine the SQL string in the Immediate window. If you don't see the problem, paste it into an new query and try to run it. You can also paste the SQL here and see if anybody can spot the problem.