Combining 3 reports into one!!!

[Kckronic21]

Hi,
I was wondering if you could solve a problem that I am having. First of all, I want to know if there was a code for setting up the paper size, orientation, and the margins of various reports. Another problem that I am having is I have 3 reports that all have the same paper size, orientation, and margins. All 3 reports are being used by 3 different queries. The first query has the center criteria set up like this: Like &quot;*&quot; & [Forms]![All_Employees_Center_Form]![CenterNametxtbox] & &quot;*&quot;. The second query has the center criteria set up like this: Like &quot;*&quot; & [Forms]![All_Employees_Center_Form_2]![CenterNametxtbox] & &quot;*&quot; and has the position criteria set up like this: <>&quot;Teacher&quot; And <> &quot;Sub.&quot; The last query has the center criteria set up like this: Like &quot;*&quot; & [Forms]![All_Employees_Center_Form_3]![CenterNametxtbox] & &quot;*&quot; and has the position criteria set up like this: &quot;Teacher&quot; Or 'Sub.&quot; I wanted to know if there was a way to create one form and one report for all 3 situations.

Thanks

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmmmmmmmmm,

Of course, it CAN be done. But like the Tic-Tac commercial says ... &quot; ... I wouldn't advise it ...&quot;

Still, such an inquiry at least shows some interest and initative, so:

I won't get into the ugly details, but I can provide an example - with SOME discussion.

First, what you are listing/discussing is simply the &quot;Where&quot; clause of a standard SQL statement. So (ASSUMING) that the remainder of the three queries are exactly the same, &quot;all&quot; you need to do is Re-write the where clause of A query - but you will need to do it in a module.

First, open up a module.
Copy the &quot;stuff between &quot;Public ... &quot; and &quot;End Function&quot; (below).

Paste it into your Module.

Public Function basMyNewQuery(FormName As String)

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    Dim strSql(6) As String          'create storage spaces

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qryMyNewQry")

    strSql(0) = "SELECT [ContLastName] " & ", " & "[ContFirstName] AS Contact, "
    strSql(0) = strSql(0) & "tblContribution.GiftType, tblContribution.ReceiptAmount "
    strSql(0) = strSql(0) & "FROM tblContributitor "
    strSql(0) = strSql(0) & "INNER JOIN tblContribution ON "
    strSql(0) = strSql(0) & "tblContributitor.ContributorID = "
    strSql(0) = strSql(0) & "tblContribution.[Customer ID] "

    strSql(5) = "ORDER BY [ContLastName] " & ", " & "[ContFirstName], "
    strSql(5) = strSql(5) & "tblContribution.GiftType DESC"
    
    strSql(6) = "Where tblContributitor.ContLastName = "
    strSql(1) = strSql(6) & Chr(34) & "Mary" & Chr(34) & " "
    strSql(2) = strSql(6) & Chr(34) & "Doe" & Chr(34) & " "
    strSql(3) = strSql(6) & Chr(34) & "Smith" & Chr(34) & " "

    strSql(4) = strSql(0)

    Select Case FormName
         Case Is = "All_Employees_Center_Form"
                strSql(4) = strSql(4) & strSql(1)

        Case Is = "All_Employees_Center_Form_1"
               strSql(4) = strSql(4) & strSql(2)

        Case Is = "All_Employees_Center_Form_2"
               strSql(4) = strSql(4) & strSql(3)
    End Select

    qdf.SQL = strSql(4) & strSql(5) & ";"

    DoCmd.OpenReport "ReportNameHere", acPreview

End Function

If the above is total greek to you. Stop here and just use the existing system / process.

IF the overall process appears to be 'sensible', then you can proceed. The strSQL elements represents CLAUSEs in the query. The are &quot;Select&quot;; &quot;From&quot;; Order By&quot;; and &quot;Where&quot;. In general, you can copy and paste the relevant parts from your three queries to the corresponding part of the example. You will usually need to 'adjust' the SQL syntax to be acceptable to VBA, so 'Judicious' rearrangement ot quotes is usually necessary. Also, notice that I concatenate parts of individual clauses. this is only for 'readability' - but I strongly suggest that you follow the example. Aslo note that MOST of the substrings end with a SPACE. These ARE IMPORTANT.

If you are doing this, you need to change &quot;ReportNameHere&quot; to your report's name. And, in EACH of the reports, change the recordsource to &quot;qryMyNewQry&quot;.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over