"AND" or "&" in the FROM clause of a query

[RuralGuy]

Thanks for stepping in Steve.

[kelann]

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]
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));

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).

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

[kelann]

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!

[ssanfu]

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:

'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

Hopefully , this will give you the idea of where I was headed.......



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.

[ssanfu]

Also, joining tables like this

FROM OrganizationsT, YPsT

results in a Cartesian join.

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.....

[kelann]

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" ...

[kelann]

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!!!!