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

SQL In VBA getting Compile Error: Expected Case

100+
P: 171
Hi Everyone,
I keep on getting an error saying Compile Error: Expected Case in my (MS Access) VBA What I was trying to do was to do a DSUM function for a Table where the criteria was coming from another table, I later found the criteria needs to be from within the domain table I had set DSUM function (makes sense). I then when to Query Bulder to Build The SQL string as Below and put it in VBA
Expand|Select|Wrap|Line Numbers
  1. Private Sub tbTotalColl_AfterUpdate()
  2.  'This should get the Sum of All the tblCollections.Amt figures grouped by  
  3.  'tblJobDetails.TFN, tblJobDetails.RcptID is the 1 side & tblCollections.RcptID 
  4.  'is the MANY side of the tables relationship 
  5.     SELECT tblJobDetails.TFN, Sum tblCollections.Amt AS SumOfAmt _
  6.     & FROM tblJobDetails INNER JOIN tblCollections ON tblJobDetails.RcptID =               
  7.     tblCollections.RcptID _
  8.     & GROUP BY tblJobDetails.TFN _
  9.     & HAVING tblJobDetails.TFN = frmPinkCardsMAIN.cboFullName Or    
  10.     tblJobDetails.TFN
  11.  
  12. End Sub
Thanks
Apr 8 '07 #1
Share this Question
Share on Google+
5 Replies


iburyak
Expert 100+
P: 1,017
Try this:


[PHP]SELECT tblJobDetails.TFN, Sum(tblCollections.Amt) AS SumOfAmt _
& FROM tblJobDetails INNER JOIN tblCollections ON tblJobDetails.RcptID = tblCollections.RcptID _
& WHERE tblJobDetails.TFN = frmPinkCardsMAIN.cboFullName _
& GROUP BY tblJobDetails.TFN [/PHP]


Hope it helps.
Apr 8 '07 #2

Expert 5K+
P: 8,434
The problem is nothing to do with the SQL syntax. It is the fact that you have simply typed SQL into your VBA module. You can't do that.

Your VBA code will need to place that SQL statement in a string, and then execute it in some way. Or place it in the appropriate property to have the desired effect - perhaps a form's Filter or RecordSource property, or some such.

The error you are receiving indicates that your VBA "Select Case" statement is not correctly written. And since it isn't VBA code, this is hardly surprising.

P.S. I have no idea whether the SQL syntax is correct or not - that's another story.
Apr 9 '07 #3

iburyak
Expert 100+
P: 1,017
The problem is nothing to do with the SQL syntax. It is the fact that you have simply typed SQL into your VBA module. You can't do that.

Your VBA code will need to place that SQL statement in a string, and then execute it in some way. Or place it in the appropriate property to have the desired effect - perhaps a form's Filter or RecordSource property, or some such.

The error you are receiving indicates that your VBA "Select Case" statement is not correctly written. And since it isn't VBA code, this is hardly surprising.

P.S. I have no idea whether the SQL syntax is correct or not - that's another story.
Syntax didn't look correct to me.
Apr 9 '07 #4

Expert 5K+
P: 8,434
Syntax didn't look correct to me.
You could be right. I didn't actually read the SQL - I just glanced at it and saw the VBA/SQL problem, so didn't bother to read further. (In fact, I was already expecting it, and just confirmed my suspicions).
Apr 9 '07 #5

iburyak
Expert 100+
P: 1,017
You are great.... :)
Apr 9 '07 #6

Post your reply

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