vba Union SQL

[ittechguy]

I'm trying to get a union query to work in vba.

Private Sub cmdSearch_Click()    Dim sqlSearch As String
    If Not IsNull(Me.cboSearchLastName) Then
        sqlSearch = "SELECT tblCustomer.LastName, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, tblRooms.BuildingFK" _
& " FROM (tblBuilding INNER JOIN tblRooms ON tblBuilding.BuildingPK = tblRooms.BuildingFK) INNER JOIN" _
& " (tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) ON tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK" _
& " WHERE LastName ='" & Me.cboSearchLastName & "'"" _
& " UNION SELECT tblCustomer.LastName, tblRoomsPOC.CustomerFK, tblRooms.RoomsPK, tblRooms.BuildingFK" _
& " FROM (tblRooms INNER JOIN tblRoomsPOC ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK) INNER JOIN" _
& " (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK) ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK" _
& " WHERE LastName ='" & Me.cboSearchLastName & "'"
    End If
    Me.RecordSource = sqlSearch
End Sub

I get run-time error 3296, Join Expression Not Supported.

I did some research, it sounds like vba doesn't support "complicated queries"??? Is there a work-around for this?

[jimatqsi]

ittechguy,
"INNER JOIN tblCustomer INNER JOIN" is not going to work. It's not clear what you are trying to do but you need to either put tblCustomer at the beginning of the FROM clause and follow it with a comma or you need to specify how that table will be joined to the other tables. INNER JOIN implies certain join criteria have to be met and you have not supplied any.

Jim

[Luuk]

What is the '(' for in Line#4?

& " FROM (tblBuilding INNER JOIN tblRooms ......

Example inner join query:

SELECT
  tblCustomer.LastName,
  tblFacilityMgr.NameOfManager
FROM
  tblCustomer
  INNER JOIN tblFacilityMgr 
     ON tblCustomer.FacilityMgrId=tblFacilityMgr.tblId
WHERE
  tblCustomer.LastName = 'Obama'
 

[mbizup]

If you haven't already done so, I'd suggest getting the query to work in the query designer first - using design view, SQL view or both.

Once you have it working, you can copy the SQL into the VBA editor and work out the VBA syntax starting with a known working query.

[NeoPa]

Hi Miriam. Lovely to see you post. -Ade.

@ITTechGuy.
MBizup's advice is good. I'm not sure why you are posting VBA when your problem is with the SQL, but you can be assured that any SQL that works in Access will work equally well in Access when called from VBA. VBA simply creates the string (SQL) and executes it. Whether or not it works is down to Jet/ACE, which is a fundamentally independent engine.

[mbizup]

Thanks for the warm welcome, Ade :)

[ittechguy]

Thanks for your responses guys.

I tested this union query in the query builder and it worked (or so I thought it did). So I assumed this must be a vba issue. Last last night I ended up testing each half of the query separately. I found that the first half worked, but the second didn't. You were right Luuk.

I rebuilt the query and now it works!

[NeoPa]

It's always harder to work out when you think you've tested it already. Trust me, you aren't the first to fall foul of that one! It's hard to avoid. You just learn over time to be even more careful and check each step even more precisely.

One technique I often use though, whenever I have queries that fail and are VBA created, is to take the actual SQL strings being used and paste them into the SQL view of a new QueryDef object (No need to save it ever). From here I switch to design view if allowed then try running it. When it fails from here it generally throws out a more helpful error message than returned via VBA (Err. etc).

If that isn't helpful enough, and that can often be the case when working with UNION or otherwise complex queries, then chop bits out until you get something that does.

Remember though, if the problem's in the SQL then that's the best thing to post here. Only post the VBA if you think you have a VBA problem. That way you get more focused help. Some experts are particularly helpful with one, some with the other. Many with both of course, but not all.