SQL Query with Excel VBA

Question

我试图用Excel VBA运行SQL查询。我是从别人那里得到这个问题的。

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*""))"

当我运行这个查询时，我得到以下错误消息:" SELECT语句包含一个保留词或参数名称，拼写错误或缺失，或标点符号不正确。">

我无法找出什么是错的(加上我从来没有试图在VBA中创建一个SQL查询，要求用户输入2个值(日期/ID)

如果能帮助解决这个问题，我将不胜感激。提前感谢任何帮助或建议.......

Answer 1

您应该最终看起来像这样:

• alias your table
• 删除不需要的括号
• 将HAVING移动到常规WHERE子句
• 把FROM放到正确的位置

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