First off, I would like to say this site has helped me out so so much. I have been coming here for almost a year now. Thanks for the great site.
My problem:
I have created an Access query, that has a where clause based on a Form fields that user can filter by. User hits run report button to export data to excel. My query works as expected if ran in Access itself. But when ran thru VBA, if i filter with the Form's dropbox's, it returns empty. But if i get rid of the where clause, it returns all the tables data.
I need to export this data to a template file, that i have setup to create pivot tables based on the data. Everything will be working one I can get the vba code to return the query data. Below is my code
Access Query:
SELECT ITEM_FE.WM_YR_WK, ITEM_FE.Saved_Date, ITEM_FE.JDA_VNDR_STAT_CD, ITEM_FE.Cat, ITEM_FE.SubCat, ITEM_FE.Vendor_NBR, ITEM_FE.Vendor_Name, ITEM_FE.REPL_GROUP_NBR, ITEM_FE.ITEM1_DESC, ITEM_FE.ITEM2_DESC, ITEM_FE.Item, ITEM_FE.LocCnt, ITEM_FE.FCST, ITEM_FE.Sales, ITEM_FE.FCST_Accuracy, ITEM_FE.Sum_FE
FROM ITEM_FE
WHERE ((ITEM_FE.WM_YR_WK between Forms!GRS_FE![wmyrwkbox1] and Forms!GRS_FE![wmyrwkbox2]) Or Forms!GRS_FE![wmyrwkbox1] Is Null) And (ITEM_FE.JDA_VNDR_STAT_CD in (Forms!GRS_FE![JDABox1], Forms!GRS_FE![JDABox2],Forms!GRS_FE![JDABox3]) Or Forms!GRS_FE![JDABox1] Is Null) AND (ITEM_FE.Item in (Forms!GRS_FE![ItemBox1], Forms!GRS_FE![ItemBox2], Forms!GRS_FE![ItemBox3], Forms!GRS_FE![ItemBox4], Forms!GRS_FE![ItemBox5], Forms!GRS_FE![ItemBox6]) Or Forms!GRS_FE![ItemBox1] Is Null) AND (ITEM_FE.Cat in (Forms!GRS_FE![CatBox1], Forms!GRS_FE![CatBox2], Forms!GRS_FE![CatBox3], Forms!GRS_FE![CatBox4], Forms!GRS_FE![CatBox5]) Or Forms!GRS_FE![CatBox1] Is Null) And (ITEM_FE.SubCat in (Forms!GRS_FE![SubCatBox1], Forms!GRS_FE![SubCatBox2], Forms!GRS_FE![SubCatBox3], Forms!GRS_FE![SubCatBox4], Forms!GRS_FE![SubCatBox5]) Or Forms!GRS_FE![SubCatBox1] Is Null) And (ITEM_FE.Vendor_NBR in (Forms!GRS_FE![VendorNbrBox1], Forms!GRS_FE![VendorNbrBox2], Forms!GRS_FE![VendorNbrBox3], Forms!GRS_FE![VendorNbrBox4], Forms!GRS_FE![VendorNbrBox5]) Or Forms!GRS_FE![VendorNbrBox1] Is Null) And (ITEM_FE.Vendor_Name in (Forms!GRS_FE![VendorNameBox1], Forms!GRS_FE![VendorNameBox2], Forms!GRS_FE![VendorNameBox3], Forms!GRS_FE![VendorNameBox4], Forms!GRS_FE![VendorNameBox5]) Or Forms!GRS_FE![VendorNameBox1] Is Null)
ORDER BY 1, 3, 4, 5, 11;
VBA Code:
My problem:
I have created an Access query, that has a where clause based on a Form fields that user can filter by. User hits run report button to export data to excel. My query works as expected if ran in Access itself. But when ran thru VBA, if i filter with the Form's dropbox's, it returns empty. But if i get rid of the where clause, it returns all the tables data.
I need to export this data to a template file, that i have setup to create pivot tables based on the data. Everything will be working one I can get the vba code to return the query data. Below is my code
Access Query:
SELECT ITEM_FE.WM_YR_WK, ITEM_FE.Saved_Date, ITEM_FE.JDA_VNDR_STAT_CD, ITEM_FE.Cat, ITEM_FE.SubCat, ITEM_FE.Vendor_NBR, ITEM_FE.Vendor_Name, ITEM_FE.REPL_GROUP_NBR, ITEM_FE.ITEM1_DESC, ITEM_FE.ITEM2_DESC, ITEM_FE.Item, ITEM_FE.LocCnt, ITEM_FE.FCST, ITEM_FE.Sales, ITEM_FE.FCST_Accuracy, ITEM_FE.Sum_FE
FROM ITEM_FE
WHERE ((ITEM_FE.WM_YR_WK between Forms!GRS_FE![wmyrwkbox1] and Forms!GRS_FE![wmyrwkbox2]) Or Forms!GRS_FE![wmyrwkbox1] Is Null) And (ITEM_FE.JDA_VNDR_STAT_CD in (Forms!GRS_FE![JDABox1], Forms!GRS_FE![JDABox2],Forms!GRS_FE![JDABox3]) Or Forms!GRS_FE![JDABox1] Is Null) AND (ITEM_FE.Item in (Forms!GRS_FE![ItemBox1], Forms!GRS_FE![ItemBox2], Forms!GRS_FE![ItemBox3], Forms!GRS_FE![ItemBox4], Forms!GRS_FE![ItemBox5], Forms!GRS_FE![ItemBox6]) Or Forms!GRS_FE![ItemBox1] Is Null) AND (ITEM_FE.Cat in (Forms!GRS_FE![CatBox1], Forms!GRS_FE![CatBox2], Forms!GRS_FE![CatBox3], Forms!GRS_FE![CatBox4], Forms!GRS_FE![CatBox5]) Or Forms!GRS_FE![CatBox1] Is Null) And (ITEM_FE.SubCat in (Forms!GRS_FE![SubCatBox1], Forms!GRS_FE![SubCatBox2], Forms!GRS_FE![SubCatBox3], Forms!GRS_FE![SubCatBox4], Forms!GRS_FE![SubCatBox5]) Or Forms!GRS_FE![SubCatBox1] Is Null) And (ITEM_FE.Vendor_NBR in (Forms!GRS_FE![VendorNbrBox1], Forms!GRS_FE![VendorNbrBox2], Forms!GRS_FE![VendorNbrBox3], Forms!GRS_FE![VendorNbrBox4], Forms!GRS_FE![VendorNbrBox5]) Or Forms!GRS_FE![VendorNbrBox1] Is Null) And (ITEM_FE.Vendor_Name in (Forms!GRS_FE![VendorNameBox1], Forms!GRS_FE![VendorNameBox2], Forms!GRS_FE![VendorNameBox3], Forms!GRS_FE![VendorNameBox4], Forms!GRS_FE![VendorNameBox5]) Or Forms!GRS_FE![VendorNameBox1] Is Null)
ORDER BY 1, 3, 4, 5, 11;
VBA Code:
Code:
Option Compare Database
Public Saveitas As String
Public M As String
Public Y As String
Dim objExcel As Object
Dim xlApp As Object
Public x As Integer
Public xx As Integer
Public xxx As Integer
Public xxxx As Integer
Const strHmtTemplateFile As String = "L:\SVP_Replen_Plan\Space Planning\Systems\Systems Team\Keevin Claypool\GRS Reporting\Item FE Template.xlsx"
'------------------------------------------------------------
' GRSExport
'------------------------------------------------------------
Public Sub GRSExport1()
On Error Resume Next
' Test to see if the file currently exist, if so, delete file, so new file can be written.
Kill ("C:\Documents and Settings\" & VBA.Environ("username") & "\Desktop\Item FE.xls")
If Dir("C:\Documents and Settings\" & VBA.Environ("username") & "\Desktop\Item FE.xls") <> "" Then
MsgBox "Item FE.xls File already in use!" & vbNewLine & "Please Close File, then rerun Report."
Else
x = 0
'Dim strOutputFile As String
Dim strTemplateFile As String
Dim intCells As Long
Dim xlApp As Excel.Application
Dim xlWorkbook As Workbook
M = Month(Date)
Y = Year(Date)
D = Day(Date)
strTemplateFile = strHmtTemplateFile
'strOutputFile = "C:\Documents and Settings\" & VBA.Environ("username") & "\Desktop\Item FE"
Saveitas = "C:\Documents and Settings\" & VBA.Environ("username") & "\Desktop\Item FE.xls"
DoEvents
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open strTemplateFile
xlApp.Visible = False
DoEvents
'Export routines
'Store Data
Export_Data_To_Excel xlApp, "DataQuery", 16, 256, "DataQuery"
If Len(Dir$(Saveitas)) > 0 Then
SetAttr Saveitas, vbNormal
VBA.Kill Saveitas
End If
xlApp.Sheets("DataQuery").Activate
Debug.Print
xlApp.ActiveWorkbook.SaveAs Saveitas
xlApp.Visible = True
Debug.Print
Set xlApp = Nothing
DoCmd.SetWarnings False
End If
GRSExport1_Exit:
Exit Sub
End Sub
Public Function Export_Data_To_Excel(xlApp As Excel.Application, Source_Table As String, Field_Count As Long, Initial_Cell As Long, Workbook_tab As String)
Dim DB1 As Database
Dim rst1 As Recordset
Dim fld1 As Field
Dim intRow As Long
Dim intColumn As Long
intCells = Initial_Cell
Set DB1 = CurrentDb()
Set rst1 = DB1.OpenRecordset("DataQuery")
xlApp.Sheets(Workbook_tab).Activate
If x = 0 Then
intRow = 2
intColumn = 1
End If
Do Until rst1.EOF
With xlApp.Sheets(Workbook_tab)
For intFields = 0 To Field_Count - 1
.cells(intRow, intColumn) = rst1.Fields(intFields)
intColumn = intColumn + 1
Next intFields
End With
intRow = intRow + 1
intCells = intCells + 1
intColumn = 1
rst1.MoveNext
Loop
Set rst1 = Nothing
Set DB1 = Nothing
x = x + 1
End Function
Last edited: