SQL Query with Excel VBA

Question

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

"WHERE ((([MyTable].Grp) = 'ABC1') And " ...Not Like 'Deposit Total*'))" Use single-quotes around query values. And what are you doing with [Enter Date:] here? - are you replacing that with a date value ? — Tim Williams
– Tim Williams, Commented Feb 24, 2021 at 22:11
@TimWilliams........The requestor is looking for a way for the user to be able to enter a date, like in an input box — Shaves
– Shaves, Commented Feb 24, 2021 at 22:32
I changed the query to use variables from an Input box but still have the same error message: "SELECT [AR Data].Cust_Name, Sum([AR Data].Amt_Paid) AS SumOfAmt_Paid, Count([AR Data].Unit_Num) AS CountOfUnit_Num WHERE ((([AR Data].Grp) = 'V1') And (([AR Data].Upload_Date) = #" & strDate & "# And (([AR Data].Sub_eEmpl) = '" & strUser & "'))" & _ " FROM [AR Data]" & _ " GROUP BY [AR Data].Cust_Name" & _ " HAVING ((([AR Data].Cust_Name) Not Like 'Deposit Total*'))" — Shaves
– Shaves, Commented Feb 24, 2021 at 23:04
Don't build the SQL statement in one string, build it up piece by piece. — norie
– norie, Commented Feb 24, 2021 at 23:27
You don't have a FROM clause, a very critical part of SQL select statement. — Parfait
– Parfait, Commented Feb 25, 2021 at 1:27

Tim Williams · Accepted Answer · 2021-02-24 23:22:48Z

2

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

answered Feb 24, 2021 at 23:22

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shaves Over a year ago

@timwilliams...........Thanks worked like a champ and was exactly what I needed...

Collectives™ on Stack Overflow

SQL Query with Excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related