Compile Error Expected Case Access
Contents |
Question Need help? Post your question and get tips & solutions from a community of 418,469 IT Pros & Developers. It's quick access compile error expected = & easy. SQL In VBA getting Compile Error: Expected Case 100+ P: 171
Access Vba Compile Error Expected =
iheartvba Hi Everyone, I keep on getting an error saying Compile Error: Expected Case in my (MS Access) VBA What compile error expected expression 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
Compile Error Expected Array
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 _ & compile error expected end with 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 i
Join INTELLIGENT WORK FORUMSFOR COMPUTER PROFESSIONALS Log In Come Join Us! Are you aComputer / IT professional?Join Tek-Tips Forums! Talk With Other Members Be Notified Of ResponsesTo
Compile Error Expected Identifier
Your Posts Keyword Search One-Click Access To YourFavorite Forums Automated SignaturesOn Your
Compile Error Expected Identifier Or Bracketed Expression
Posts Best Of All, It's Free! Join Us! *Tek-Tips's functionality depends on members receiving e-mail. By joining you compile error expected end of statement are opting in to receive e-mail. Posting Guidelines Promoting, selling, recruiting, coursework and thesis posting is forbidden.Tek-Tips Posting Policies Jobs Jobs from Indeed What: Where: jobs by Link To This Forum! https://bytes.com/topic/visual-basic/answers/629707-sql-vba-getting-compile-error-expected-case Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.Just copy and paste the BBCode HTML Markdown MediaWiki reStructuredText code below into your site. Microsoft: Access Modules (VBA Coding) Forum at Tek-Tips HomeForumsProgrammersDBMS PackagesMicrosoft: Access Modules (VBA Coding) Forum Compile Error: Expected Case thread705-1183282 Forum Search FAQs Links MVPs Compile Error: Expected Case Compile Error: Expected Case http://www.tek-tips.com/viewthread.cfm?qid=1183282 tbiceps (IS/IT--Management) (OP) 27 Jan 06 13:51 This is a line of code in an application. I get a compile error when I attempt to run this code that states the following:(Compile error:Expected Case)SELECT QryProcessOperationsArea.[Report ID], QryProcessOperationsArea.[Customized or Standard], QryProcessOperationsArea.[Report Title], QryProcessOperationsArea.[ApplicationProducing Report], QryProcessOperationsArea.[Report Type] FROM QryProcessOperationsArea;I copied this line of code from the row source of the specific form. Does anyone have any clues? RE: Compile Error: Expected Case SkipVought (Programmer) 27 Jan 06 14:17 You did not provide any VB code -- just SQL.Where is your code? Skip, Be Advised! For those this winter, caught in Arctic-like calamity, be consoled...Many are cold, but few are frozen! RE: Compile Error: Expected Case tbiceps (IS/IT--Management) (OP) 27 Jan 06 15:57 This is SQL code generated by access in the QBE window. My goal is to convert this SQL statement into a VBA readable format. However, I will insert the SQL statement in the following block code.Private Sub BeginSrchButton_Click()On Error GoTo Err_BeginSrchButton_ClickIf Combo9 = "All" ThenDoCmd.OpenForm "fReportQuerybyWMSApp", acNormal, "QryProcessOperationsAreaAll", , acFormPropertySettings, acWindowNormal"""/IDEAL LOCATION FOR SEQUEL STATEMENT/""""Select QryProcessOperationsArea.[Report ID], QryProcessOperationsArea.[Customized or Standard], QryPr
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit https://www.experts-exchange.com/questions/20993852/SELECT-TOP-1-Compile-Error-Expected-Case.html Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > SELECT TOP 1...Compile Error: Expected: Case Want to Advertise Here? Solved SELECT TOP 1...Compile Error: Expected: Case Posted on 2004-05-18 MS Access 1 Verified Solution 4 Comments 1,400 Views Last Modified: 2012-05-04 Hey, I am trying to write a script that returns a result from an SQL query as a variable. Right now compile error I have the SQL statement: SELECT TOP 1 [Inventory].[Sample Number] AS sampleNumber FROM Inventory ORDER BY [Inventory].[Sample Number] DESC; Where sampleNumber is the variable. However, I get a compile error Expected: Case. What is wrong with the syntax/statement? Holdidgga 0 Question by:holddigga Facebook Twitter LinkedIn Google LVL 77 Best Solution bypeter57r Hello holddigga, Nothing wrong with your SQl statement but to use it you have to open a recordset based on it. An alternative in compile error expected this case would be to use a DMax fuction to get the highest value. Go to Solution 4 Comments LVL 77 Overall: Level 77 MS Access 67 Message Accepted Solution by:peter57r2004-05-18 Hello holddigga, Nothing wrong with your SQl statement but to use it you have to open a recordset based on it. An alternative in this case would be to use a DMax fuction to get the highest value. x= dmax("[sample number]","Inventory") Pete 0 Message Author Comment by:holddigga2004-05-18 Hey, I think i got the statement working...i stupidly didnt write DoCmd.RunSQL " " but now it says during runtime "A RunSQL action requires an argument consisting of an sql statement" that doesnt make sense seeing as how I wrote and SQL statement... What is dmax??? holddigga 0 LVL 32 Overall: Level 32 MS Access 31 Message Expert Comment by:jadedata2004-05-18 try using (dao code) dim rs as dao.recordset set rs = currentdb.openrecordset("{SQLSTRING HERE}", dbopensnapshot) x = rs("sampleNumber") 0 Message Author Comment by:holddigga2004-05-18 Hey, thanks a lot the dmax worked perfectly! 0 Write Comment First Name Please enter a first name Last Name Please enter a last name Email We will never share this with anyone. Comment Submit Your Comment By clicking you are agreeing to Experts Exchange's Terms of Use. Featured Post How to run any project wit