JOIN Err.

[Trudye]

I'm getting the err msg Err in JOIN. I re-created the SQL in a query and except for the HAVING statement our code is identical.

strSQL = "SELECT max(Cost) AS MaxPhoto16x20, [Deal Detail].Item " _
& "FROM (Clients " _
& "JOIN Deals on Clients.ID = Deals.ClientId) " _
& "JOIN [Deal Detail] on Deals.Dealid = [Deal Detail].Dealid " _
& "WHERE Clientid = " & sID & " AND Item = '16x20 Photo'" _
& " GROUP BY [Deal Detail].Item, Clients.Id "
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sItem = rst!MaxPhoto16x20
GoSub Write_Recs
End If

Any Idea what it is I'm missing?.
Trudye

 

[steven290]

i dont think you can use where when using group by you would need to use having

strSQL = "SELECT max(Cost) AS MaxPhoto16x20, [Deal Detail].Item " _
& "FROM (Clients " _
& "JOIN Deals on Clients.ID = Deals.ClientId) " _
& "JOIN [Deal Detail] on Deals.Dealid = [Deal Detail].Dealid " _
& " GROUP BY [Deal Detail].Item, Clients.Id "_
& "Having Clientid = " & sID & " AND Item  = '16x20 Photo'"

 

[cmmrfrds]

I don't see Clients.Id in the select list.

I believe anything in the group by needs to represented in the list, otherwise why group by it.

 

[Trudye]

Thanks Guys for responding, I found out that INNER was missing. I thought it was implied/the default.

Be well,
Trudye