Ms Access Vba Compile Error Expected
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring compile error expected to developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question
Vba Expected = When Calling Sub
x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; compile error expected = msgbox it only takes a minute: Sign up MS Access - Module - VBA - Compile Error : Expected Idenitifier - Table with Spaces in Name up vote 1 down vote favorite I have a table that I am trying to excel vba compile error expected = write a module that will go in and add two columns to an existing table. I have a table that has existing spaces in the name and I get an error of Compile Error : Expected Identifier. Private Sub AddColumn() Dim curDatabase As Database Dim [Final LOS and PAPW TABLE] As TableDef Dim Yrs_of_Svc As Field Dim Yrs_of_Svc_Cat As Field ' Get a reference to the current database Set curDatabase = CurrentDb ' Get a reference to a table
Vba Compile Error Expected Function Or Variable
named TestTable - NOTE: The table MUST exist Set [Final LOS and PAPW TABLE] = curDatabase.TableDefs("[Final LOS and PAPW TABLE]") 'define the fields using the CreateField method ' Syntax: .CreateField("FIELD NAME", TYPE, [Length]) ' I used the predefined types already in Access Set Yrs_of_Svc = [Final LOS and PAPW TABLE].CreateField("Yrs_of_Svc", dbDouble, 5) Set Yrs_of_Svc_Cat = [Final LOS and PAPW TABLE].CreateField("Yrs_of_Svc_Cat", dbText, 2) If Len(Offc_TRMN_DT) > 1 Then [Final LOS and PAPW TABLE].Yrs_of_Svc = [Final LOS and PAPW TABLE].Offc_TRMN_DT - [Final LOS and PAPW TABLE].Offc_EMPLMT_DT Else [Final LOS and PAPW TABLE].Yrs_of_Svc = [Final LOS and PAPW TABLE].BSE_ISS_RGST_DT - [Final LOS and PAPW TABLE].Offc_EMPLMT_DT End If Select Case Yrs_of_Svc ' Evaluate Number. Case 0 To 1.99 [Final LOS and PAPW TABLE].Yrs_of_Svc_Cat = 1 Case 2.01 To 2.99 [Final LOS and PAPW TABLE].Yrs_of_Svc_Cat = 2 Case 3.01 To 3.99 [Final LOS and PAPW TABLE].Yrs_of_Svc_Cat = 3 Case 4.01 To 4.99 [Final LOS and PAPW TABLE].Yrs_of_Svc_Cat = 4 Case Is > 5# [Final LOS and PAPW TABLE].Yrs_of_Svc_Cat = 5+ End Select End Sub Thank you for your help. Robert vba ms-access ms-access-2010 share|improve this question asked Apr 15 '14 at 15:18 user2320821 1661214 Dim [Final LOS and PAPW TABLE] As TableDef is not valid syntax. –enderland Apr 15 '14 at 16:07 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted The variable name doesn't have to be the same as the table name,
Community Links Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag Search Advanced Search Find vba compile error byref argument type mismatch All Thanked Posts Go to Page... Thread Tools Rate Thread vba compile error expected end of statement Display Modes 01-14-2003, 10:04 AM #1 MaxNEMC Guest Posts: n/a Compile Error: Expected: = I wrote
Expected Sub Function Or Property
a function that I want to have 3 parameters for Import_Reports(tblName as String, Specs as String, InputDir as String) When i try to run it, i get: Compile Error: http://stackoverflow.com/questions/23087920/ms-access-module-vba-compile-error-expected-idenitifier-table-with-spa Expected = BUT If i define 2 of the parameters within the function as vars, leaving only one as an argument, it works fine. I can use any one of the 3 alone as an argument and it works, but any more than 1 and i get that compile error... i'm stumped! Any help would be greatly appreciated! http://www.access-programmers.co.uk/forums/showthread.php?t=40303 Max 01-14-2003, 10:49 AM #2 Fornatian Dim Person Join Date: Aug 2000 Location: England Posts: 1,396 Thanks: 0 Thanked 0 Times in 0 Posts Max, A compile error of this nature means that a variable is missing an assignment somewhere. Can you post your code? Also, can you elaborate on.. Quote: If i define 2 of the parameters within the function as vars, leaving only one as an argument... __________________ Ian build a better system and they'll build a better idiot. Fornatian View Public Profile Find More Posts by Fornatian 01-14-2003, 10:58 AM #3 MaxNEMC Guest Posts: n/a Function Import_Reports(Specs As String, tblName As String, InputDir as string) Dim ImportFile, ImportErrors As String 'Dummy As Integer 'InputDir = "c:\windows\desktop\adhocrpts\" 'Specs = "Highland Import Specs" ImportFile = Dir(InputDir & "\*.txt") 'Sets the Filename to be imported Do While Len(ImportFile) > 0 'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1)) 'This is to import each file into individual tables 'tblName = "H
soon) Ruby (coming soon) Getting Started Code Samples Resources Patterns and Practices App Registration Tool Events Podcasts Training API Sandbox Videos Documentation Office Add-ins Office Add-in Availability Office Add-ins Changelog Microsoft Graph https://msdn.microsoft.com/en-us/library/office/gg264652.aspx API Office 365 Connectors Office 365 REST APIs SharePoint Add-ins Office UI Fabric Submit https://bytes.com/topic/visual-basic/answers/629707-sql-vba-getting-compile-error-expected-case to the Office Store All Documentation https://www.yammer.com/ http://feeds.feedburner.com/office/fmNx Office VBA language reference Visual Basic language reference Error Messages Error Messages Expected Function or variable Expected Function or variable Expected Function or variable #Else clause must be preceded by a matching #If #Else If, #Else, or #End If must be preceded by a matching compile error #If #ElseIf must be preceded by a matching #If or #ElseIf and followed by an #ElseIf, #Else, or #End If
Question Need help? Post your question and get tips & solutions from a community of 418,595 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