By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Help with VBA SQL Statement Expected End of Statement

P: 1
Hi, I'm working on a project and it is my first time implementing VBA with Access. I know my SQL Statement is close, but it obviously isn't perfect, if any of you can see what the error is I'd really appreciate it.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub CreateYearReport()
  3.  
  4. Dim StrQuery As String
  5. Dim Count As Integer
  6. Dim Port As String
  7. Port = "BK"
  8. Dim Arr(1 To 12) As Integer
  9.  
  10. Count = 1
  11.  
  12. Do While Count < 13
  13. Arr(Count) = QryMonth(Port, 5, 6)
  14. MsgBox ("Month: " & Count & " Total: " & Arr(Count))
  15.  
  16. Count = Count + 1
  17. Loop
  18.  
  19. End Sub
  20.  
  21. Function QryMonth(Port As String, Month As Integer, Year As Integer) As Integer
  22. StrQuery = "SELECT Count(GL3_SLA.Portfolio)" & _
  23. "FROM GL3_SLA" & _
  24. "WHERE (((GL3_SLA.Portfolio)= ""Port "" ) AND ((GL3_SLA.Sent_Date) Like ""*#" & Month & Year)")" & _
  25. "GROUP BY GL3_SLA.Portfolio;"
  26.  
  27.  
  28. DoCmd.OpenQuery ("MonthTotal")
  29.  
  30. End Function
  31.  
Once again I am getting a Syntax Error and an Expected End of Statement, thanks.
Jun 13 '07 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
OK, try this ...


Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub CreateYearReport()
  3. Dim StrQuery As String
  4. Dim Count As Integer
  5. Dim Port As String
  6. Dim Arr(1 To 12) As Integer
  7.     Port = "BK"
  8.     Count = 1
  9.  
  10.     Do While Count < 13
  11.         Arr(Count) = QryMonth(Port, 5, 6)
  12.         MsgBox ("Month: " & Count & " Total: " & Arr(Count))
  13.         Count = Count + 1
  14.     Loop
  15. End Sub
  16. Function QryMonth(Port As String, Month As Integer, Year As Integer) As Integer
  17.     StrQuery = "SELECT Count(GL3_SLA.Portfolio) " & _
  18.     "FROM GL3_SLA" & _
  19.     "WHERE ((GL3_SLA.Portfolio)='Port') AND ((Month(GL3_SLA.Sent_Date)=" & Month & _
  20.     ") AND Year(GL3_SLA.Sent_Date)=" & Year & "))) " & _
  21.     "GROUP BY GL3_SLA.Portfolio;"
  22.  
  23.     DoCmd.OpenQuery ("MonthTotal")
  24. End Function
  25.  
  26.  
Jun 15 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.