Compile Error Expected Case
Question Need help? Post your question and get tips & solutions from a community of 418,469 IT Pros & Developers. It's quick & easy. SQL In VBA getting Compile Error: Expected Case 100+ P: 171 iheartvba 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 PrivateSubtbTotalColl_AfterUpdate() 'ThisshouldgettheSumofAllthetblCollections.Amtfiguresgroupedby 'tblJobDetails.TFN,tblJobDetails.RcptIDisthe1side&tblCollections.RcptID 'istheMANYsideofthetablesrelationship SELECTtblJobDetails.TFN,SumtblCollections.AmtASSumOfAmt_ &FROMtblJobDetailsINNERJOINtblCollectionsONtblJobDetails.RcptID= tblCollections.RcptID_ &GROUPBYtblJobDetails.TFN_ &HAVINGtblJobDetails.TFN=frmPinkCardsMAIN.cboFullNameOr tblJobDetails.TFN EndSub Thanks Apr 8 '07 #1 Post Reply Share this Question 5 Replies Expert 100+ P: 1,017 iburyak 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 reply Expert 5K+ P: 8,435 Killer42 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 reply Expert 100+ P: 1,017 iburyak 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.
be down. Please try the request again. Your cache administrator is webmaster. Generated Thu, 06 Oct 2016 05:35:32 GMT by s_bd40 (squid/3.5.20)
Custom Search UtterAccess Forums>Microsoft® Access>Access Modules Compile Error Expected:Case Help Forum HomeSearchHelpUA Messages|-- UtterAccess.com NewsAccess Knowledge Center|-- http://www.utteraccess.com/forum/Compile-Error-ExpectedCa-t1833594.html Access Code Archive|-- Access Knowledgebase / FAQ|-- Access TutorialsMicrosoft® Access|-- Local Access User Groups (AUGs)|-- Interface Design|-- Access Q and A|-- Access Tables + Relationships|-- http://www.xtremevbtalk.com/archive/index.php/t-153087.html Access Queries|-- Access Forms|-- Access Reports|-- Access Macros|-- Access Modules|-- Access Date + Time|-- Access Errors + Error Handling|-- Access Built-in Functions|-- Access Searching + compile error Data Mining|-- Access Records|-- Access Security|-- Access Automation|-- Access Database Container|-- Tool bars, Menu bars + Ribbon|-- Access Services (Web Databases)|-- Access Data Access Pages (Deprecated)|-- Access Data Projects - ADP|-- Access Runtime, Packaging & DeploymentAnd More...|-- Microsoft SQL Server|-- SQL Server Reporting Services|-- Microsoft SharePoint|-- Microsoft Internet Explorer|-- Visual compile error expected Basic 6 and Earlier|-- Visual Basic 2003 and Later|-- ASP and VBScript|-- Web Development|-- PHP, Perl, MySQL and Postgres|-- Other Database Engines|-- BI Tools and Applications|-- JAVA and C#|-- GraphicsMicrosoft® Office|-- Microsoft Excel|-- Microsoft Excel FAQs|-- Microsoft Word|-- Microsoft Outlook|-- Microsoft PowerPoint|-- Microsoft FrontPage/Exp. Web|-- Other Microsoft ProductsPC|-- Network Issues|-- Q & A - Hardware|-- Q & A - Software|-- Q & A - Other PC|-- Virus + Security DiscussionUtterAccess Odds and Ends|-- General Chat|-- Q & A - UtterAccess.com Forums|-- Q & A - Access Wiki Options kstonemanView Member Profile May 26 2009, 10:48 AM Post#1Posts: 435Joined: 25-September 03From: KansasI am trying to convert a Access query to a SQL statement with no luck. I am getting a Compile Error Expected: Case.I am using two tables - SIS_daily_temp (SIS) and tblMU. Here is the code I am using:CODE SELECT Sum(SIS.Cap_Plan) AS Commit1, Sum(SIS.Demand) AS Demand1, Sum(SIS.Estimated_Staff&
can't figure out what I am doing wrong. I have followed all the syntax that I can find in my reference material and keep getting the same results - ERRORS on compile and syntax. Select * FROM rsaddress WHERE LName = ssTab1.Caption This gives me the error "Compile Error: Expected: Case" and highlights the '*' I keep getting this same kind of error whenever I try to use any SQL statements. Someone please point me in the right direction. Am I missing a connection that I need for SQL or is the some declarative I need ?? rsaddress = an ADODB recordset that is defined in the option explicit. LName = the last name as stored in the ADODB recordset. ssTab1.Caption is a tab with the letter of the last name I have done some displays and sstab1.caption displays correctly, the number of records in the file match what has been input to the file. I am pulling my hair out and bald women don't really look good. PLEASE HELP !!!! Jenn :confused: Marathon Man03-15-2004, 06:35 AMHow are you actually using the line of SQL - is it the Source property of the Recordset object? dwall03-15-2004, 07:08 AMIs your SQL line like this strSQL = "Select * FROM rsaddress WHERE LName = ssTab1.Caption" if so try strSQL = "Select * FROM rsaddress WHERE LName = '" & ssTab1.Caption & "'" jennie_195103-15-2004, 07:11 AMHow are you actually using the line of SQL - is it the Source property of the Recordset object? I'm not sure what you mean - I am trying to select records from an Access database that have a first letter that matches the letter on the caption of the sstab1 control. I have MDAC 2.6 installed and can't make a connection. I don't have SQL server set up as I am trying to set this application so a person doesn't have to purchase and install SQL on their computer to use this system. It was my understanding (perhaps incorrect) that SQL queries would be able to be used by anyone as long as the dataset was set up correctly and the MDAC component was installed. Thanks, Jenn jennie_195103-15-2004, 07:14 AMIs your SQL line like this strSQL = "Select * FROM rsaddress WHERE LName = ssTab1.Caption" if so try strSQL = "Select * FROM rsaddress WHERE LName = '" & ssTab1.Caption & "'" NOPE - maybe that is my problem, let me give that a try and see what happens. jennie_195103-15-2004, 07:20 AMNOPE - maybe that is my problem, let me give that a try and see what happens. That did the trick on the SQL statement syntax, now all I have to do is set up the population code for the tabs. Thank you for your keen eye and assistance. Jenn jennie_195103-15-2004, 07:36 AMNOPE - maybe that is my problem, let me give that a try and see what happens. Well,