Thanks for stepping in Steve.
Thanks for stepping in Steve.
Thank you, Steve!
What I am trying to do is almost too complicated for me to explain beyone generalities. I've tried your suggestions, and I can't seem to make them work, but I think it's because I can't aticulate in SQL. Or maybe, in this case, English.
I have a report (EngageSelectedYPRep) that is based upon a query (YPEngageQSelected). The SQL of that Query is below:
SELECT OrganizationsT.OrgName, OrganizationsT.[OrgArtsCulture], OrganizationsT.[OrgEnvironmental], OrganizationsT.[OrgHealthcare], OrganizationsT.[OrgYouthMentorship], OrganizationsT.[OrgPovertyBasicNeeds], OrganizationsT.[OrgSportsAthletics], OrganizationsT.[OrgIntlDiversity], OrganizationsT.[OrgBusiness], OrganizationsT.[OrgChurch], OrganizationsT.[OrgAddictionDis], OrganizationsT.[OrgAnimals], OrganizationsT.[OrgCollege], YPsT.[YPFirstName], YPsT.[YPLastName], YPsT.[YPArtsCulture], YPsT.[YPEnvironmental], YPsT.[YPHealthcare], YPsT.[YPYouthMentorship], YPsT.[YPPovertyBasicNeeds], YPsT.[YPSportsAthletics], YPsT.[YPIntlDiversity], YPsT.[YPBusiness], YPsT.[YPChurch], YPsT.[YPAddictionDis], YPsT.[YPAnimals], YPsT.[YPCollege]This EngageSelectedYPRep is built for the YP selected from the Form referenced in the first item of the WHERE clause (see red text above) and lists, in the Detail part, the organizations that match based upon the remainder of the FROM clause (see the blue text).
FROM OrganizationsT, YPsT
WHERE YPsT.YPID=Forms![EngageSelectedYPF]!YPMatchSearch And ((OrganizationsT.OrgArtsCulture=True And YPsT.YPArtsCulture=True) Or (OrganizationsT.OrgEnvironmental=True And YPsT.YPEnvironmental=True) Or (OrganizationsT.OrgHealthcare=True And YPsT.YPHealthcare=True) Or (OrganizationsT.OrgYouthMentorship=True And YPsT.YPYouthMentorship=True) Or (OrganizationsT.OrgPovertyBasicNeeds=True And YPsT.YPPovertyBasicNeeds=True) Or (OrganizationsT.OrgSportsAthletics=True And YPsT.YPSportsAthletics=True) Or (OrganizationsT.OrgIntlDiversity=True And YPsT.YPIntlDiversity=True) Or (OrganizationsT.OrgBusiness=True And YPsT.YPBusiness=True) Or (OrganizationsT.[OrgChurch]=True And [YPsT].[YPChurch]=True) Or (OrganizationsT.[OrgAddictionDis]=True And [YPsT].[YPAddictionDis]=True) Or (OrganizationsT.[OrgAnimals]=True And [YPsT].[YPAnimals]=True) Or (OrganizationsT.[OrgCollege]=True And [YPsT].[YPCollege]=True));
So the EngageSelectedYPRep shows a YP and lists all of the Orgs that match its interests.
Beside each org on EngageSelectedYPRep is a button to open a report showing further details on that Org (GetOrgInfoRep). This report is based upon a query (GetOrgInfoRepQ), whose SQL is as follows -- it opens the OrgReport for EVERY organization:
SELECT OrganizationsT.*, ContactsT.ContactFullName, ContactsT.PrimaryContact, ContactsT.PrimaryContactText, ContactsT.ContactPhone, ContactsT.ContactEmail, ContactsT.ContactAddress1, ContactsT.ContactAddress2, ContactsT.ContactCity, ContactsT.ContactState, ContactsT.ContactZip, ContactsT.ContactFirstLast
FROM OrganizationsT LEFT JOIN ContactsT ON OrganizationsT.OrgName = ContactsT.ContactOrg;
The button that opens the report runs this command -- it opens it only for the organization that it beside in the Detail part:DoCmd.OpenReport "GetOrgInfoRep", acViewPreview, , "OrgName = '" & Me.OrgName & "'"
What I want is an additional button on EngageSelectedYPRepthat opens the GetOrgInfoRep, but for each organization in the Detail part. So, it shows MORE orgs than the current button (which opens only for the org it is beside), but less orgs that the full report -- it filters the full report to show only the ones matching the blue text for the YP displayed on the current EngageSelectedYPRep.
Good grief, does that make sense???
I feel like, somehow, the DoCmd.OpenReport could work, but, for the life of me, I cannot figure out how. I tried both query methods, but I think my skill is not up to it.
Thank you for any further thoughts you might have!!!
--ak
I'm currently trying a simpler tack, with a syntax question posted here:
https://www.accessforums.net/program...tml#post145824
Thanks for any thoughts -- it's so much appreciated!
Disclaimer: I do not have the queries/reports to test this. I do not have Access 2010 and have never seen reports that have buttons on them. Sometimes I think I know what I am doing; most of the time I am wrong! This is a taillight guarantee - you cannot see my taillights, so there is NO guarantee. Your mileage may vary.
I have a report (EngageSelectedYPRep) that is based upon a query (YPEngageQSelected).
<snip>
</snip>
Beside each org on EngageSelectedYPRep is a button to open a report showing further details on that Org (GetOrgInfoRep). This report is based upon a query (GetOrgInfoRepQ), whose SQL is as follows -- it opens the OrgReport for EVERY organization:SELECT OrganizationsT.*, ContactsT.ContactFullName, ContactsT.PrimaryContact, ContactsT.PrimaryContactText, ContactsT.ContactPhone, ContactsT.ContactEmail, ContactsT.ContactAddress1, ContactsT.ContactAddress2, ContactsT.ContactCity, ContactsT.ContactState, ContactsT.ContactZip, ContactsT.ContactFirstLast
FROM OrganizationsT LEFT JOIN ContactsT ON OrganizationsT.OrgName = ContactsT.ContactOrg;
The button that opens the report runs this command -- it opens it only for the organization that it beside in the Detail part:DoCmd.OpenReport "GetOrgInfoRep", acViewPreview, , "OrgName = '" & Me.OrgName & "'"
What I want is an additional button on EngageSelectedYPRepthat opens the GetOrgInfoRep, but for each organization in the Detail part. So, it shows MORE orgs than the current button (which opens only for the org it is beside), but less orgs that the full report -- it filters the full report to show only the ones matching the blue text for the YP displayed on the current EngageSelectedYPRep.
Using the queries above, this is the code to generate a WHERE clause:
Hopefully , this will give you the idea of where I was headed.......Code:'Private Sub button_Click() <<= use your (new) button name Dim r As DAO.Recordset Dim pWHERE As String Set r = CurrentDb.OpenRecordset("YPEngageQSelected", dbOpenForwardOnly) If Not (r.BOF And r.EOF) Then Do While Not r.EOF pWHERE = pWHERE & "OrgName = '" & r("OrgName") & "' OR " r.MoveNext Loop If Len(Trim(pWHERE)) > 0 Then pWHERE = Left(pWHERE, Len(pWHERE) - 5) & "'" End If ' MsgBox pWHERE DoCmd.OpenReport "GetOrgInfoRep", acViewPreview, , pWHERE Else MsgBox " OOPs - No records!!" End If 'clean up r.Close Set r = Nothing End Sub
BTW, this almost has to be done in VBA.. from Help:
Note The maximum length of the Where condition argument is 256 characters (in query design view). If you need to enter a more complex SQL WHERE clause longer than this, use the OpenReport method of the DoCmd object in Visual Basic instead. You can enter SQL WHERE clause statements of up to 32,768 characters in Visual Basic.
Last edited by ssanfu; 11-08-2012 at 02:54 PM. Reason: added comment / computer can't spell
Also, joining tables like this
results in a Cartesian join.Code:FROM OrganizationsT, YPsT
If the first table has 3 rows and the second table has 4 rows, the result is 12 rows.
Just something to be aware of.....
Thank you, Steve!! OMG, That is very complicated looking!
I think I have something going at that other link I posted. I *think* my only outstanding issue is how to use 'or' in a where condition -- "this and this" or "this and this" ...
Thank you so much, everyone!
Based upon your help, with lots of reworking -- it's very different from how it started -- it's working completely!!!
I can't tell you how much I appreciate all of your help.
I ended up doing a DoCmd.OpenRep and adding my criteria at the whereclause, but I had to rework a lot of fields and change how I looked at them (and, on that note, special thanks to Julie for using "Not IsNull" in other help she gave me, because it worked perfectly here!!!!