Excel report with search criteria as headings (1 Viewer)

brp

Registered User.
Local time
Today, 00:11
Joined
Mar 26, 2012
Messages
13
I have created a Allen Browne's type search form and result exported to excel format for more flexibility using following vba.



Dim qdf As DAO.QueryDef

Dim strWhere As String
Dim strFile As String
Const strcStub = "SELECT tblEquipment.*FROM tblEquipment" & vbCrLf
Const strcTail = "ORDER BY SomeField;"
Const strcExportQuery = "Query1"



If Me.FilterOn Then
strWhere = "WHERE" & Me.Filter & vbCrLf
End If

Set qdf = CurrentDb.CreateQueryDef("Query1", strcStub & strWhere)

strFile = "D:\DB\REPORT\MyExports.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strcExportQuery, strFile


CurrentDb.QueryDefs.Delete "Query1"


Now I want to show the searched criteria(suppose [Equipment], [Date of Issued]) to show on the top of the excel worksheet as headings.

Is it possible ? Any help would be a great appreciate.
 

Attachments

  • Book1.xlsx
    8.5 KB · Views: 98
Last edited:

Micron

AWF VIP
Local time
Today, 03:11
Joined
Oct 20, 2018
Messages
3,476
Some clarification might be in order, mostly for others I think.
Does that even work at all? You have several spots with no spaces so I foresee words being jammed together that should not be.

Also, what do you mean by 'headings'? Column headings? Show the criteria as cell values and not column headings?

Please enclose code within code tags (# on forum toolbar) and use indentation to make it easier to read code. You can go back and add them to your already posted code.
 

brp

Registered User.
Local time
Today, 00:11
Joined
Mar 26, 2012
Messages
13
I have attached the Excel report that what I want. Is it possible to do it?
 

Attachments

  • Book1.xlsx
    9.1 KB · Views: 97

isladogs

MVP / VIP
Local time
Today, 07:11
Joined
Jan 14, 2017
Messages
18,186
@Brp
I have deleted your duplicate thread which itself included duplicate posts.
Anyone who wants to answer can do so in this thread.
 

June7

AWF VIP
Local time
Yesterday, 23:11
Joined
Mar 9, 2014
Messages
5,424
Options:

1. textboxes in report header referencing form controls and use OutputTo method - it did work in my test

2. Excel automation code

Your query will fail because it lacks separator space after * wildcard and after "WHERE"

Const strcStub = "SELECT tblEquipment.* FROM tblEquipment" & vbCrLf

strWhere = "WHERE " & Me.Filter & vbCrLf

vbCrLf will prevent words jamming together when SQL statement compiles. A space in the string instead would serve as well.

What is content of Me.Filter?

Why not using ORDER BY clause?
 
Last edited:

brp

Registered User.
Local time
Today, 00:11
Joined
Mar 26, 2012
Messages
13
thanks for your quick reply with vb code corrections. I am newbie to access vba and copied the Allen Browne's search form and above codes from an example. I haven't physically created a report and put a button to export the filter form to excel format.

Therefore, Step by steps would be highly appreciated.

Regards
 

June7

AWF VIP
Local time
Yesterday, 23:11
Joined
Mar 9, 2014
Messages
5,424
Forum is not place to learn basics. Step by step would too lengthy and complex. We don't know your db. You now have some ideas to work with. Do research and attempt code and when you have specific issue, post question.
 

Users who are viewing this thread

Top Bottom