Ms Access Syntax Error Comma In Query Expression
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the syntax error (comma) in query expression access 2010 workings and policies of this site About Us Learn more about Stack
Syntax Error Comma In Query Expression Access 2007
Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions syntax error (comma) in query expression iif Jobs Documentation Tags Users Badges Ask Question 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; it only takes a minute: Sign up Syntax Error: Comma in Query Expression of SQL Statement? up vote 3 down vote favorite 2 Alright folks, I am 100% Batshit insane at this point. Can someone possibly help me here? I'm clueless after trying EVERYTHING and searching for others with this experience. I'm at a loss here. I've tried brackets around all the table/field names. I've tried taking the tablename off the Select fields, I've tried moving the parenthesis backwards and forwards but to no avail. The error I get is: "Syntax Error (comma) in query expression. '( [F1], [F2], [F3], [F4], [F5], [F6], [F7], [F8] )'. CurrentDb.Execute "INSERT INTO [tblSalesJournal] ( [Store], [Business Unit], [Country], [State], [Store Type], [Net Sales], [Fee], [Total] ) " & _ "SELECT ( [F1], [F2], [F3], [F4], [F5], [F6], [F7], [F8] ) " & _ "FROM [tblImport]" Any help is GREATLY appreciated sql vba ms-access share|improve this question asked Mar 14 '14 at 19:00 user2296381 671516 add a comment| 1 Answer 1 active oldest votes up vote 8 down vote The problem is: SELECT ( [F1], [F2], [F3], [F4], [F5], [F6], [F7], [F8] ) You should not bracket a SELECT statement, therefore SELECT [F1], [F2], [F3], [F4], [F5], [F6], [F7], [F8] Some MS errors are odd. share|improve this answer answered Mar 14 '14 at 19:03 Fionnuala 77.2k665110 INSERT INTO SET uses brackets INSERT INTO VALUES u
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 developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question 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; it only takes a minute: Sign up Syntax error (comma) in query expression. MS Access 2013 up vote 1 down vote favorite I'm building a sql query in Access 2013. This is my query: http://stackoverflow.com/questions/22413569/syntax-error-comma-in-query-expression-of-sql-statement SELECT COUNT(Age) FROM Members AS [Under 12] WHERE Age <= 12, COUNT(Age) FROM Members AS [13-18] WHERE Age BETWEEN 13 AND 18 but I keep getting this error: Syntax error (comma) in query expression 'Age <= 12, COUNT(Age) FROM SukkotMembers AS [13-18] WHERE Age BETWEEN 13 AND 18` The problem is with the comma at the end of line two, but I can't see why. When I run just one line it works fine. I am really http://stackoverflow.com/questions/32549045/syntax-error-comma-in-query-expression-ms-access-2013 new to SQL Just so you are aware. Also I just realized that the AS [Under 12] and AS [13-18] are not working and I also have no idea why sql ms-access share|improve this question edited Sep 13 '15 at 11:37 asked Sep 13 '15 at 11:09 yobddigi 1428 add a comment| 2 Answers 2 active oldest votes up vote 3 down vote accepted You can also sum: SELECT Sum(Abs(Age <= 12)) AS [Under 12], Sum(Abs(Age BETWEEN 13 AND 18)) AS [13-18] FROM Members share|improve this answer answered Sep 13 '15 at 13:26 Gustav 12.8k21026 That is quite elegant! –Andre Sep 13 '15 at 15:43 Thanks! short and sweet –yobddigi Sep 14 '15 at 15:22 add a comment| up vote 4 down vote First: the syntax you are using is not valid SQL as you have more than one FROM clause which is not allowed; the same applies to the where clause, which by the way operates on the whole set. You can't just take bits and pieces of SQL and patch together to get a working query - there is a rather strict and well-defined syntax that you must adhere to. What you want is either to use subqueries that return scalar values or to use conditional counting: SELECT COUNT(IIF(Age<=12,age,null)) AS [Under 12] ,COUNT(IIF(Age BETWEEN 13 AND 18,age,null)) AS [13-18] FROM Members Also, FROM
be down. Please try the request again. Your cache administrator is webmaster. Generated Thu, 20 Oct 2016 19:15:27 GMT by s_wx1085 (squid/3.5.20)
Links Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag Search Advanced Search Find All Thanked Posts Go to Page... Thread Tools Rate Thread Display Modes 11-25-2010, 01:07 PM #1 JH40 Newly Registered User Join Date: Sep 2010 Posts: 100 Thanks: 1 Thanked 0 Times in 0 Posts Syntax error (comma) in expression I'm getting an error message "syntax error (comma) in query expression" on the below expression. Would someone mind helping me understand where I am going wrong? I may have nested my two IIF statements incorrectly, but that doesn't have much to do with the comma, I'm thinking. But my thinking isn't reallyworking here, so I'll defer to the forum respectfully! Amt: Round(Sum(IIf([Work_Days_Left]<1,0 Or ([MTD]+Nz([Cap])<0,([Target]/[Work_Days_Left])),(([Target]-([MTD]+Nz([Cap])))/[Work_Days_Left]))),2) JH40 View Public Profile Find More Posts by JH40 11-25-2010, 02:04 PM #2 the_net_2.0 Banned Join Date: Sep 2010 Posts: 812 Thanks: 3 Thanked 16 Times in 15 Posts Re: Syntax error (comma) in expression Quote: Originally Posted by JH40 Amt: Round( Sum( IIf( [Work_Days_Left]<1,0 Or ([MTD]+Nz([Cap])<0, ([Target]/[Work_Days_Left])), (([Target]-([MTD]+Nz([Cap])))/[Work_Days_Left])),) 2) first, I don't think you can have an 'OR' in a boolean section of an IIF() statement, can you? second, you have a comma inside your SUM() function, after the close of the IIF() last parenthesis. that function has only one arg. (see red ","). third, the entire underlined section need to be eliminated. currently, it is not part of any function. the_net_2.0 View Public Profile Find More Posts by the_net_2.0 11-26-2010, 06:27 AM #3 JH40 Newly Registered User Join Date: Sep 2010 Posts: 100 Thanks: 1 Thanked 0 Times in 0 Posts Re: Syntax error (comma) in expression Thank you for your response. Let me clarify as I think my original post was unclear. Here is the actual expression that works fine: Amt: Round(Sum(IIf([MTD]+Nz([Cap])<0,([Target]/[Work_Days_Left]),(([Target]-([MTD]+Nz([Cap])))/[Work_Days_Left]))),2) Once Work_Days_Left hits zero, the problem occurs as it is division by zero