VBA sql not working as expected

[jcright]

Hello all,



I've been having an odd problem with my sql code in my vba. I built it first in a query so that I knew I would get it right and it does show me the results that I want based on the criteria that I want. However, when I convert it to sql and copy that into my vba sub, then it behaves oddly. The problem I have is that it doesn't recognize my criterias. It is supposed to grab information from two tables based on a month and then it is supposed to post data but only if there is data to post. Instead, it sifts through the entire Misc table, does calculations on everything and then posts. Each month this does this and I didn't realize it until just now. So 5 months ago there is 5 times the data that I need, 4 months ago shows 4 times the data that I need, etc. I'm sure its something simple that I'm missing because that's how it always works. However, it doesn't duplicate information for one person. So I'm not sure if my function has anything to do with it. Any help would be greatly appreciated.

I have two Tables: Misc and Comms2. I join them in a query. That query calls two functions to make calculations and then posts the results into another table called Override. The idea is to find out how much of an override certain people get on total sales for specific summed categories and based on their location.

Here is the sql statement that Access spits out for me:
INSERT INTO Override ( DateofComm, Instrument, MiscAmount, Store, SP1, factorBasis, overrideTotal )

SELECT Misc.DateofComm, Misc.Instrument, Misc.MiscAmount, Misc.Store, Comms2.SP1, getFactor([SP1],[Misc].[Store],[Misc].[Instrument]) AS Expr1, [MiscAmount]*getFactor([SP1],[Misc].[Store],[Misc].[Instrument]) AS Expr2

FROM Comms2 INNER JOIN Misc ON Comms2.DateofComm = Misc.DateofComm

GROUP BY Misc.DateofComm, Misc.Instrument, Misc.MiscAmount, Misc.Store, Comms2.SP1, getFactor([SP1],[Misc].[Store],[Misc].[Instrument]), [MiscAmount]*getFactor([SP1],[Misc].[Store],[Misc].[Instrument])

HAVING (((Misc.DateofComm)=[Date of Comms]) AND ((Comms2.SP1)="Dave" Or (Comms2.SP1)="Anthony" Or (Comms2.SP1)="Emily" Or (Comms2.SP1)="Dana") AND ((getFactor([SP1],[Misc].[Store],[Misc].[Instrument])) Is Not Null));


Below is the code in my sub:
Public Sub appOverride(commDate)
Dim appOver As String

appOver = "INSERT INTO Override ( DateofComm, Instrument, MiscAmount, Store, SP1, factorBasis, overrideTotal ) " & _
"SELECT Misc.DateofComm, Misc.Instrument, Misc.MiscAmount, Misc.Store, Comms2.SP1, getFactor([SP1],[Misc].[Store],[Misc].[Instrument]) AS Expr1, [MiscAmount]*getFactor([SP1],[Misc].[Store],[Misc].[Instrument]) AS Expr2 " & _
"FROM Comms2 INNER JOIN Misc ON Comms2.DateofComm = Misc.DateofComm " & _
"GROUP BY Misc.DateofComm, Misc.Instrument, Misc.MiscAmount, Misc.Store, Comms2.SP1, getFactor([SP1],[Misc].[Store],[Misc].[Instrument]), [MiscAmount]*getFactor([SP1],[Misc].[Store],[Misc].[Instrument]) " & _
"HAVING Misc.DateofComm=#" & Format(commDate, "mmmm yyyy") & "# AND Comms2.SP1='Dave' Or Comms2.SP1='Emily' Or Comms2.SP1='Anthony' Or Comms2.SP1='Dana' AND getFactor([SP1],[Misc].[Store],[Misc].[Instrument]) Is Not Null;"

DoCmd.SetWarnings False
DoCmd.RunSQL appOver
DoCmd.SetWarnings True

End Sub

[CJ_London]

you've taken out too many brackets in your HAVING clause - you need to separate out the AND's and the OR's - compare with the original

[jcright]

you've taken out too many brackets in your HAVING clause - you need to separate out the AND's and the OR's - compare with the original

Yep, that did the trick. Thank you. Knew it had to be something simple. I had either read or saw a YouTube post saying something along the lines of Access putting in unnecessary brackets in "Having" section and to just take them out. So that's what I've been doing. Now I understand better. Thanks again.