424,279 Members | 1,907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,279 IT Pros & Developers. It's quick & easy.

Problem on Exporting 2003Access parameters Query to 2003Excel

P: 3
Sir can you help me to transfer my Access Query to MS excel? i have a
command button on the form to export the parameter query named
"HVACWindwardQuery" to excel spreadsheet and i got the codes from
searching on the internet and books but the problem is when i run the
command button "Export" it just only open the Blank Spreadsheet, no
data at all that it came from my query named "HVACWindwardQuery" and
there's an error on it "Run-time error '3061': Too few parameters.
Expected 2." and when i open the VBA editor this is an error that is on
highlight in yellow:
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName) ....can
you help me to solve this problem, here is the code that im using it
came from the book but its not working out:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub cmdExport_Click() 
  2. Dim xlApp As Excel.Application
  3. Dim xlSheet As Excel.Worksheet
  4. Dim xlWorkbook As Excel.Workbook
  5. Dim acQuery As QueryDef
  6. Dim objRST As Recordset
  7. Dim strQueryName As String
  8. strQueryName = "HVACWindwardQuery"
  9. Set xlApp = CreateObject("Excel.Application")
  10. xlApp.Visible = True
  11. Set xlWorkbook = xlApp.Workbooks.Add
  12. Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)
  13.  
  14. Set xlSheet = xlWorkbook.Sheets(1)
  15. For lvlColumn = 0 To objRST.Fields.Count - 1
  16. xlSheet.Cells(1, lvlColumn + 1).Value = _
  17. objRST.Fields(lvlColumn).Name
  18. Next
  19. 'Change the font to bold for the header row
  20. xlSheet.Range(xlSheet.Cells(1, 1), _
  21. xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True
  22. 'Add a border to header row cells
  23. With xlSheet.Range(xlSheet.Cells(1, 1), _
  24. xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeLeft)
  25. .LineStyle = xlContinuous
  26. .Weight = xlThin
  27. .ColorIndex = xlAutomatic
  28. End With
  29. With xlSheet.Range(xlSheet.Cells(1, 1), _
  30. xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeTop)
  31. .LineStyle = xlContinuous
  32. .Weight = xlThin
  33. .ColorIndex = xlAutomatic
  34. End With
  35. With xlSheet.Range(xlSheet.Cells(1, 1), _
  36. xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeBottom)
  37. .LineStyle = xlContinuous
  38. .Weight = xlThin
  39. .ColorIndex = xlAutomatic
  40. End With
  41. With xlSheet.Range(xlSheet.Cells(1, 1), _
  42. xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeRight)
  43. .LineStyle = xlContinuous
  44. .Weight = xlThin
  45. .ColorIndex = xlAutomatic
  46. End With
  47. With xlSheet
  48. .Range("A2").CopyFromRecordset objRST
  49. .Name = Left(strQueryName, 31)
  50. End With
  51. Set xlSheet = Nothing
  52. Set xlWorkbook = Nothing
  53. Set xlApp = Nothing
  54. End Sub
  55.  
and this is the SQL View:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT [HVAC Windward Table].[EQUIPMENT / PM NUMBER], [HVAC Windward Table].[TAG NO#], [HVAC Windward Table].[FACILITY NUMBER], [HVAC Windward Table].[BUILDING / LOCATION], [HVAC Windward Table].[EQUIPMENT TYPE], [HVAC Windward Table].[FREQ PM], [HVAC Windward Table].DATE, [HVAC Windward Table].REMARKS, [HVAC Windward Table].[JOB PLAN], [HVAC Windward Table].[FILTER SIZE], [HVAC Windward Table].[WORK ORDER NO], [HVAC Windward Table].[REPORTED DATE], [HVAC Windward Table].[TAG NO], [HVAC Windward Table].[ANNEX/SUB-ANNEX], [HVAC Windward Table].[SPEC NO], [HVAC Windward Table].[JOB DESCRIPTION], [HVAC Windward Table].RATING, [HVAC Windward Table].[COMMENTS/ACTION], [HVAC Windward Table].[INSPECTOR NAME]
  3. FROM [HVAC Windward Table]WHERE ((([HVAC Windward Table].DATE)>=forms![HVAC Windward Form]!txtdatefrom And ([HVAC Windward Table].DATE)<=forms![HVAC Windward Form]!txtDateTo));
  4.  
Still searchin to solve this problem and i read it on the book it says:
The query requires parameters, it should be probably use an alternate
method of opening a query and that is an OpenRecordset method. I don't
know how to use or create a code for OpenRecordset pls try to give me a
code using OpenRecordset method of the QueryDef object to open a
parameter query and Transport my query named "HVACWindwardQuery" into
Excel Spreadsheet or the best method code for this problem. In my Form
there is two(2) date to fill in and once you click the button it will
go to "HVACWindwardQuery" and preview my Report and i created a button
again to Export the query to Excel but is not working at all it gives
me a blank spreadsheet. Pls help me... Thanks and god bless..
Dec 13 '07 #1
Share this Question
Share on Google+
9 Replies


Jim Doherty
Expert 100+
P: 897
Sir can you help me to transfer my Access Query to MS excel? i have a
command button on the form to export the parameter query named
"HVACWindwardQuery" to excel spreadsheet and i got the codes from
searching on the internet and books but the problem is when i run the
command button "Export" it just only open the Blank Spreadsheet, no
data at all that it came from my query named "HVACWindwardQuery" and
there's an error on it "Run-time error '3061': Too few parameters.
Expected 2." and when i open the VBA editor this is an error that is on
highlight in yellow:
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName) ....can
you help me to solve this problem, here is the code that im using it
came from the book but its not working out:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub cmdExport_Click() 
  2. Dim xlApp As Excel.Application
  3. Dim xlSheet As Excel.Worksheet
  4. Dim xlWorkbook As Excel.Workbook
  5. Dim acQuery As QueryDef
  6. Dim objRST As Recordset
  7. Dim strQueryName As String
  8. strQueryName = "HVACWindwardQuery"
  9. Set xlApp = CreateObject("Excel.Application")
  10. xlApp.Visible = True
  11. Set xlWorkbook = xlApp.Workbooks.Add
  12. Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)
  13.  
  14. Set xlSheet = xlWorkbook.Sheets(1)
  15. For lvlColumn = 0 To objRST.Fields.Count - 1
  16. xlSheet.Cells(1, lvlColumn + 1).Value = _
  17. objRST.Fields(lvlColumn).Name
  18. Next
  19. 'Change the font to bold for the header row
  20. xlSheet.Range(xlSheet.Cells(1, 1), _
  21. xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True
  22. 'Add a border to header row cells
  23. With xlSheet.Range(xlSheet.Cells(1, 1), _
  24. xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeLeft)
  25. .LineStyle = xlContinuous
  26. .Weight = xlThin
  27. .ColorIndex = xlAutomatic
  28. End With
  29. With xlSheet.Range(xlSheet.Cells(1, 1), _
  30. xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeTop)
  31. .LineStyle = xlContinuous
  32. .Weight = xlThin
  33. .ColorIndex = xlAutomatic
  34. End With
  35. With xlSheet.Range(xlSheet.Cells(1, 1), _
  36. xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeBottom)
  37. .LineStyle = xlContinuous
  38. .Weight = xlThin
  39. .ColorIndex = xlAutomatic
  40. End With
  41. With xlSheet.Range(xlSheet.Cells(1, 1), _
  42. xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeRight)
  43. .LineStyle = xlContinuous
  44. .Weight = xlThin
  45. .ColorIndex = xlAutomatic
  46. End With
  47. With xlSheet
  48. .Range("A2").CopyFromRecordset objRST
  49. .Name = Left(strQueryName, 31)
  50. End With
  51. Set xlSheet = Nothing
  52. Set xlWorkbook = Nothing
  53. Set xlApp = Nothing
  54. End Sub
  55.  
and this is the SQL View:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT [HVAC Windward Table].[EQUIPMENT / PM NUMBER], [HVAC Windward Table].[TAG NO#], [HVAC Windward Table].[FACILITY NUMBER], [HVAC Windward Table].[BUILDING / LOCATION], [HVAC Windward Table].[EQUIPMENT TYPE], [HVAC Windward Table].[FREQ PM], [HVAC Windward Table].DATE, [HVAC Windward Table].REMARKS, [HVAC Windward Table].[JOB PLAN], [HVAC Windward Table].[FILTER SIZE], [HVAC Windward Table].[WORK ORDER NO], [HVAC Windward Table].[REPORTED DATE], [HVAC Windward Table].[TAG NO], [HVAC Windward Table].[ANNEX/SUB-ANNEX], [HVAC Windward Table].[SPEC NO], [HVAC Windward Table].[JOB DESCRIPTION], [HVAC Windward Table].RATING, [HVAC Windward Table].[COMMENTS/ACTION], [HVAC Windward Table].[INSPECTOR NAME]
  3. FROM [HVAC Windward Table]WHERE ((([HVAC Windward Table].DATE)>=forms![HVAC Windward Form]!txtdatefrom And ([HVAC Windward Table].DATE)<=forms![HVAC Windward Form]!txtDateTo));
  4.  
Still searchin to solve this problem and i read it on the book it says:
The query requires parameters, it should be probably use an alternate
method of opening a query and that is an OpenRecordset method. I don't
know how to use or create a code for OpenRecordset pls try to give me a
code using OpenRecordset method of the QueryDef object to open a
parameter query and Transport my query named "HVACWindwardQuery" into
Excel Spreadsheet or the best method code for this problem. In my Form
there is two(2) date to fill in and once you click the button it will
go to "HVACWindwardQuery" and preview my Report and i created a button
again to Export the query to Excel but is not working at all it gives
me a blank spreadsheet. Pls help me... Thanks and god bless..
Hi QC,

Welcome to the scripts!

I edited your post to include code tags around your posted code. Please remember to do that if you post back (your first post accepted) :)

Ok in relation to your posted code I have amended it to reference the querydef and open a snapshot recordset. Included in that is a reference to your form controls that supply the parameters for the query.

The changes made I have highlighted in bold and at the end of each line added an XXX so you can study the differences between the two
I have commented out those line within it that are not needed in view of amendments made.

The below should now work for you

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdExport_Click()
  2.     Dim xlApp As Excel.Application
  3.     Dim xlSheet As Excel.Worksheet
  4.     Dim xlWorkbook As Excel.Workbook
  5.     Dim acQuery As QueryDef
  6.     Dim objRST As Recordset
  7.     'Dim strQueryName As String 'XXX not now needed
  8. 'strQueryName = "HVACWindwardQuery" 'XXX not now needed
  9.     Set xlApp = CreateObject("Excel.Application")
  10.     xlApp.Visible = True
  11.     Set xlWorkbook = xlApp.Workbooks.Add
  12.  
  13.     Set acQuery = CurrentDb.QueryDefs("HVACWindwardQuery") 'XXX
  14.  
  15.     acQuery![forms![HVAC Windward Form]!txtdatefrom] = Me!txtDateFrom 'XXX
  16.     acQuery![forms![HVAC Windward Form]!txtdateTo] = Me!txtDateTo 'XXX
  17. '    Set objRST = Application.CurrentDb.OpenRecordset(strQueryName) 'XXX not now needed
  18.  
  19.     Set objRST = acQuery.OpenRecordset(dbOpenSnapshot) 'XXXX
  20.  
  21.     Set xlSheet = xlWorkbook.Sheets(1)
  22.     For lvlColumn = 0 To objRST.Fields.Count - 1
  23.         xlSheet.Cells(1, lvlColumn + 1).Value = _
  24.         objRST.Fields(lvlColumn).Name
  25.     Next
  26.     'Change the font to bold for the header row
  27.     xlSheet.Range(xlSheet.Cells(1, 1), _
  28.                  xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True
  29.     'Add a border to header row cells
  30.     With xlSheet.Range(xlSheet.Cells(1, 1), _
  31.                      xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeLeft)
  32.         .LineStyle = xlContinuous
  33.         .Weight = xlThin
  34.         .ColorIndex = xlAutomatic
  35.     End With
  36.     With xlSheet.Range(xlSheet.Cells(1, 1), _
  37.                      xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeTop)
  38.         .LineStyle = xlContinuous
  39.         .Weight = xlThin
  40.         .ColorIndex = xlAutomatic
  41.     End With
  42.     With xlSheet.Range(xlSheet.Cells(1, 1), _
  43.                      xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeBottom)
  44.         .LineStyle = xlContinuous
  45.         .Weight = xlThin
  46.         .ColorIndex = xlAutomatic
  47.     End With
  48.     With xlSheet.Range(xlSheet.Cells(1, 1), _
  49.                      xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeRight)
  50.         .LineStyle = xlContinuous
  51.         .Weight = xlThin
  52.         .ColorIndex = xlAutomatic
  53.     End With
  54.     With xlSheet
  55.         .Range("A2").CopyFromRecordset objRST
  56.         '.Name = left(strQueryName, 31) 'XXX not now needed
  57.      .Name = left(acQuery.Name, 31) 'XXX
  58.     End With
  59.     Set xlSheet = Nothing
  60.     Set xlWorkbook = Nothing
  61.     Set xlApp = Nothing
  62.     objRST.Close  'XXX
  63.     Set objRST = Nothing  'XXX
  64.     Set acQuery= Nothing  'XXX
  65.  
  66. End Sub

Regards

Jim:)
Dec 15 '07 #2

P: 3
Sir Jim,

I'm sorry for not having a code tags in my posted code, next time i will do that. Well, how can i thank you? The code is now perfectly working in my command button. Thank's for helping me, your such a great person..This code will be useful to others in solving parameter query to excel . Thanks and god bless!
Dec 15 '07 #3

Jim Doherty
Expert 100+
P: 897
Sir Jim,

I'm sorry for not having a code tags in my posted code, next time i will do that. Well, how can i thank you? The code is now perfectly working in my command button. Thank's for helping me, your such a great person..This code will be useful to others in solving parameter query to excel . Thanks and god bless!
Hi QC

You are very welcome and thanks for posting back. You have no need to apologise I wasnt chastising :)) it is your first post you are not necessarily going to know first time..... in I know I didnt!!

Anyway I,m pleased it worked for you.Have a good festive season

Regards

Jim :)
Dec 15 '07 #4

P: 3
Hi Jim,
I also have a question that is related to this task. I am attempting to put together some VBA to help in automating some heavily manually intensive Access and Excel tasks done by about five people at my workplace. I do not have a background in VBA, but have done a fair amount of reading on it over the past week and pieces are coming together slowly. My question is; What would one way be to modify the above code so that if the query was run again, but with a different input, the query would export to a second worksheet instead of overwriting the first. Thank s in advance. The users of this at work will be so happy once it's up and running.
Dec 16 '07 #5

Jim Doherty
Expert 100+
P: 897
Hi Jim,
I also have a question that is related to this task. I am attempting to put together some VBA to help in automating some heavily manually intensive Access and Excel tasks done by about five people at my workplace. I do not have a background in VBA, but have done a fair amount of reading on it over the past week and pieces are coming together slowly. My question is; What would one way be to modify the above code so that if the query was run again, but with a different input, the query would export to a second worksheet instead of overwriting the first. Thank s in advance. The users of this at work will be so happy once it's up and running.

Hi Tak,

Please explain precisely what you mean by different input. I know I can guess that you might mean the name of a different query (maybe five different queries all named differently, conversely it could mean one query of a particular name but having different parameters on which it performs its function within itself)

As you can imagine 'logic' is somewhat specific to circumstance and one needs to know 'precisely' what it is you need a routine to do

Regards

Jim :)
Dec 16 '07 #6

P: 3
Hi Tak,

Please explain precisely what you mean by different input. I know I can guess that you might mean the name of a different query (maybe five different queries all named differently, conversely it could mean one query of a particular name but having different parameters on which it performs its function within itself)

As you can imagine 'logic' is somewhat specific to circumstance and one needs to know 'precisely' what it is you need a routine to do

Regards

Jim :)

Hi Jim,
I really appreciate your quick response. I have envisioned the process flow such that a user inputs project titles to a form, which references the query, which then exports to Excel. This is for a reporting process which typically involves 10 or so identical queries run on different project titles.
Ideally what I want to happen is for a series of 10 or so queries with input boxes to be set up using a Macro on a single form which will sequentially output the query results to an Excel workbook into different sheets. The process in place now is as a query set up as a parameter query. The user has to input the project title and manually export the query results to excel for a series of 10 or so different project titles.
Dec 17 '07 #7

Jim Doherty
Expert 100+
P: 897
Hi Jim,
I really appreciate your quick response. I have envisioned the process flow such that a user inputs project titles to a form, which references the query, which then exports to Excel. This is for a reporting process which typically involves 10 or so identical queries run on different project titles.
Ideally what I want to happen is for a series of 10 or so queries with input boxes to be set up using a Macro on a single form which will sequentially output the query results to an Excel workbook into different sheets. The process in place now is as a query set up as a parameter query. The user has to input the project title and manually export the query results to excel for a series of 10 or so different project titles.

Ok tak I get the idea...so that I can code something that represents your actual tablename and fieldnames and a typical query are you able to post the structure of the table and the syntax of a typical query that you currently are using. That way, anything I do will represent itelf properly on your system as opposed to you having to remap anything

An example of posting ..something like this

TableName
tblMyTable
FieldName1 datatype text
Fieldname2 datatype number
FieldName3 datatype memo

and so on
SQL systaxt derived from the SQL window

SELECT Fieldname1,Fieldname2 FROM tblMyTable WHERE etc etc


Jim :)
Dec 17 '07 #8

P: 3
Ok tak I get the idea...so that I can code something that represents your actual tablename and fieldnames and a typical query are you able to post the structure of the table and the syntax of a typical query that you currently are using. That way, anything I do will represent itelf properly on your system as opposed to you having to remap anything

An example of posting ..something like this

TableName
tblMyTable
FieldName1 datatype text
Fieldname2 datatype number
FieldName3 datatype memo

and so on
SQL systaxt derived from the SQL window

SELECT Fieldname1,Fieldname2 FROM tblMyTable WHERE etc etc


Jim :)


Hi again Jim, I didn't expect the level of help that you are providing, and I really do appreciate it. The analysts will be happy this week.

This is the query that is being run;

Expand|Select|Wrap|Line Numbers
  1.  
  2. PARAMETERS [Enter the name of the project] Text ( 255 );
  3. SELECT ShOrRpt_ShLvl_With_ItemsbyContainer.[Gold Order Num], ShOrRpt_ShLvl_With_ItemsbyContainer.[Client SO ID Num], ShOrRpt_ShLvl_With_ItemsbyContainer.[Client PO Num], [AOR - Grouped].Ship_To_ID, [AOR - Grouped].Ship_to_Desc AS Consignee, ShOrRpt_ShLvl_With_ItemsbyContainer.City, ShOrRpt_ShLvl_With_ItemsbyContainer.ST, ShOrRpt_ShLvl_With_ItemsbyContainer.Zip, ShOrRpt_ShLvl_With_ItemsbyContainer.jba_line_no AS Line_No, ShOrRpt_ShLvl_With_ItemsbyContainer.item_id, ShOrRpt_ShLvl_With_ItemsbyContainer.item_desc, ShOrRpt_ShLvl_With_ItemsbyContainer.qty AS Qty_Shipped, [Qty]*[unit_per_pack] AS Exploded_Units, ShOrRpt_ShLvl_With_ItemsbyContainer.[Tender Date] AS [Ship Date], [AOR - Grouped].CarrierName AS [Carrier Name], ShOrRpt_ShLvl_With_ItemsbyContainer.[Tracking Num], ShOrRpt_ShLvl_With_ItemsbyContainer.[Pro Num], [AOR - Grouped].Service_Level_Desc AS Service_Level, IIf([status]="Closed","Delivered",IIf([status]="Possible D","Possible Delay",IIf([status]="OPEN","Open",IIf([status]="EXCEPTION","Exception",[status])))) AS Status_, IIf([Delivery Date]<#1/1/2006#,Null,[Delivery Date]) AS POD_Date, IIf((IsNull([delivery Date]) Or [delivery date]<#1/1/2006#) And [status]="Open" And ([POD Time]=#12/30/1899#),Null,Format([POD Time],"hh:nn AM/PM")) AS POD_Time, ShOrRpt_ShLvl_With_ItemsbyContainer.[POD Name], ShOrRpt_ShLvl_With_ItemsbyContainer.Origin
  4. FROM ShOrRpt_ShLvl_With_ItemsbyContainer INNER JOIN [AOR - Grouped] ON ShOrRpt_ShLvl_With_ItemsbyContainer.[Gold Order Num] = [AOR - Grouped].gold_so_no
  5. WHERE (((ShOrRpt_ShLvl_With_ItemsbyContainer.projectname)=[Enter the name of the project]));
  6.  

The Form is titled Project Title along with the combo box. The Macro is entitled ExportToExcel. The table is entitled "ShOrRpt_ShLvl".
What I am trying to do precisely is export the results of this query to the 6th row of each worksheet in the workbook that will be used. The first 5 rows will contain column headers and title information from a template. Each user traditionally has run the query approximately 10 times for each different project name title to produce 10 different reports. I really appreciate your help and have spent most of last week and this weekend reading up on VBA and Access.
Dec 17 '07 #9

Jim Doherty
Expert 100+
P: 897
Hi again Jim, I didn't expect the level of help that you are providing, and I really do appreciate it. The analysts will be happy this week.

This is the query that is being run;

Expand|Select|Wrap|Line Numbers
  1.  
  2. PARAMETERS [Enter the name of the project] Text ( 255 );
  3. SELECT ShOrRpt_ShLvl_With_ItemsbyContainer.[Gold Order Num], ShOrRpt_ShLvl_With_ItemsbyContainer.[Client SO ID Num], ShOrRpt_ShLvl_With_ItemsbyContainer.[Client PO Num], [AOR - Grouped].Ship_To_ID, [AOR - Grouped].Ship_to_Desc AS Consignee, ShOrRpt_ShLvl_With_ItemsbyContainer.City, ShOrRpt_ShLvl_With_ItemsbyContainer.ST, ShOrRpt_ShLvl_With_ItemsbyContainer.Zip, ShOrRpt_ShLvl_With_ItemsbyContainer.jba_line_no AS Line_No, ShOrRpt_ShLvl_With_ItemsbyContainer.item_id, ShOrRpt_ShLvl_With_ItemsbyContainer.item_desc, ShOrRpt_ShLvl_With_ItemsbyContainer.qty AS Qty_Shipped, [Qty]*[unit_per_pack] AS Exploded_Units, ShOrRpt_ShLvl_With_ItemsbyContainer.[Tender Date] AS [Ship Date], [AOR - Grouped].CarrierName AS [Carrier Name], ShOrRpt_ShLvl_With_ItemsbyContainer.[Tracking Num], ShOrRpt_ShLvl_With_ItemsbyContainer.[Pro Num], [AOR - Grouped].Service_Level_Desc AS Service_Level, IIf([status]="Closed","Delivered",IIf([status]="Possible D","Possible Delay",IIf([status]="OPEN","Open",IIf([status]="EXCEPTION","Exception",[status])))) AS Status_, IIf([Delivery Date]<#1/1/2006#,Null,[Delivery Date]) AS POD_Date, IIf((IsNull([delivery Date]) Or [delivery date]<#1/1/2006#) And [status]="Open" And ([POD Time]=#12/30/1899#),Null,Format([POD Time],"hh:nn AM/PM")) AS POD_Time, ShOrRpt_ShLvl_With_ItemsbyContainer.[POD Name], ShOrRpt_ShLvl_With_ItemsbyContainer.Origin
  4. FROM ShOrRpt_ShLvl_With_ItemsbyContainer INNER JOIN [AOR - Grouped] ON ShOrRpt_ShLvl_With_ItemsbyContainer.[Gold Order Num] = [AOR - Grouped].gold_so_no
  5. WHERE (((ShOrRpt_ShLvl_With_ItemsbyContainer.projectname)=[Enter the name of the project]));
  6.  

The Form is titled Project Title along with the combo box. The Macro is entitled ExportToExcel. The table is entitled "ShOrRpt_ShLvl".
What I am trying to do precisely is export the results of this query to the 6th row of each worksheet in the workbook that will be used. The first 5 rows will contain column headers and title information from a template. Each user traditionally has run the query approximately 10 times for each different project name title to produce 10 different reports. I really appreciate your help and have spent most of last week and this weekend reading up on VBA and Access.
Hi Tak,

!) Is the table called ShOrRpt_ShLvl
or
is it called
ShOrRpt_ShLvl_With_ItemsbyContainer
or
is ShOrRpt_ShLvl_With_ItemsbyContainer a query based on a table called
ShOrRpt_ShLvl

I am happy to help you on this... but the thread will be too long ping ponging back and forth with minor clarifications and so on with what I have in mind for doing with this. I am going to deal with it off line with you and I will concisely report back the findings to this thread eventually so that others may benefit from any solution.

If you private message me with your email address I will email you

Jim :)
Dec 17 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.