I want to create multiple reports w

[jh3016]

I want to create multiple reports with the following criteria:

OrdDate > 7/1/03

Status not equal "1Green" or "2Green"

Department1 = "Sales" or "Service" or "Quality" OR
Department2 = "Sales" or "Service" or "Quality" OR
Department3 = "Sales" or "Service" or "Quality"
Then combine Department1 & Department2 & Department3 together (I know I could have done this better, but I'm a newbie)

Then I want to count how many records this returns

Then I want to export this into Excel

Caution - I don't know coding so if you give me coding I need to know details because what may be apparent to you will never be apparent to me.

Let me know if you need additional information.

Thanks in advance.

 

[scriverb]

The following SQL code can be copied and pasted into the SQL window of a new query to select all fields from your table using the criteria that you indicated above. Save the query and give it a name:

Select A.*
FROM tblYourTableName as A
WHERE (A.OrdDate > #07/01/2003#) and (A.Status <> &quot;1Green&quot; and A.Status <> &quot;2Green&quot and ((Instr(1,&quot;SalesServiceQuality&quot;, A.Department1) > 0) or (Instr(1,&quot;SalesServiceQuality&quot;, A.Department2) > 0) or (Instr(1,&quot;SalesServiceQuality&quot;, A.Department1) > 0));


To get the recordcount of this query simply use a DCount function:
DCount(&quot;*&quot;,&quot;qryYourQueryName&quot

This function can be placed in the Control Source of a control in your report.

To export your query results to an Excel spreadsheet copy and paste the following code behind a Command Button on a form. Put in in the OnClick event procedure.

DoCmd.TransferSpreadsheet acExport, 8, &quot;qryCountOfMembers&quot;, &quot;c:\access\ExportedData.xls&quot;


Be sure to update the red code with the table, field, and path/file names that apply to your situation.




Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???