SQL Query with Excel VBA

I’m trying to run a SQL query with Excel VBA. I got the query from someone else.

    vsql = "SELECT [MyTable].Cust_Name, Sum([MyTable].Amt_Paid) AS SumOfAmt_Paid, " & _
         " Count([MyTable].Unit_Num) AS CountOfUnit_Num  " & _
         "WHERE ((([MyTable].Grp) = ""ABC1"") And " & _
         "(([MyTable].Upload_Date) = [Enter Date:]) And " & _ 
         "(([MyTable].Sub_eEmpl) = [Enter Processor's ID:]))" & _ 
         "GROUP BY [MyTable].Cust_Name " & _
         "HAVING ((([MyTable].Cust_Name) Not Like ""Deposit Total*""))"

When I run this query, I’m getting the following error message: “The SELECT statement includes a reserve word or an argument name that is misspelled or missing, or the punctuation is incorrect.”

I can’t figure out what is wrong (plus I’ve never tried to create a SQL query in VBA that requires the user to enter 2 values (Date / ID)

Any help in getting this resolved would be greatly appreciated. Thanks in advance for any help or suggestions…….

Answer

You should end up with something that looks more like this:

• alias your table
• remove unneeded parentheses
• move the HAVING to a regular WHERE clause
• put the FROM in the correct place

SELECT 
    a.Cust_Name, Sum(a.Amt_Paid) AS SumOfAmt_Paid, Count(a.Unit_Num) AS CountOfUnit_Num 
FROM 
    [MyTable] a
WHERE 
    a.Grp = 'V1' and
    a.Upload_Date = #[somedate]# and
    a.Sub_eEmpl = 'someuser' and 
    a.Cust_Name not like 'Deposit Total*'
GROUP BY a.Cust_Name