473,320 Members | 1,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Syntax Error in Query

I have a numeric column in a query called Prov1.Volume. When I try
taking the sum of that column (using SUM aggregate function in Design
View) it gives a syntax error. It says that SumOfProv1.Volume is an
invalid name. How do I get around this error. (note: the name is
Prov1.Volume because the column name came from an inner join. Is there
a way to rename this column and possibly thereby avoid the error? Sums
of other columns work fine, since they have simple names like "Sensor").

Aug 17 '06 #1
2 1803
Tom.

yes, you can rename the column. In SQL using the ' as newname' clause.
In the QBE query-editor of MS-Access by placing a new name before the
expression or fieldname followed by a colon. like "newvolume:
[prov1].[volume]". Look in the SQL-view to see the result. Important is
that the fieldnames are unique. otherwise QBE will create new names
like expr2:

Marc

Tom Baxter schreef:
I have a numeric column in a query called Prov1.Volume. When I try
taking the sum of that column (using SUM aggregate function in Design
View) it gives a syntax error. It says that SumOfProv1.Volume is an
invalid name. How do I get around this error. (note: the name is
Prov1.Volume because the column name came from an inner join. Is there
a way to rename this column and possibly thereby avoid the error? Sums
of other columns work fine, since they have simple names like "Sensor").
Aug 17 '06 #2
"Tom Baxter" <as*****@juno.comwrote in
news:11********************@i42g2000cwa.googlegrou ps.com:
I have a numeric column in a query called Prov1.Volume. When
I try taking the sum of that column (using SUM aggregate
function in Design View) it gives a syntax error. It says
that SumOfProv1.Volume is an invalid name. How do I get
around this error. (note: the name is Prov1.Volume because
the column name came from an inner join. Is there a way to
rename this column and possibly thereby avoid the error? Sums
of other columns work fine, since they have simple names like
"Sensor").
Yes, to rename a field or expression is easy. In SQL you simply
add the statement AS NewName after the field declaration.

In hte query builder, you prefix the name or expression with the
newname followed by a colon. In your case that aliasing of the
field name might need to happen in the query with the inner
join.

SQLexample = "select long.horrible_fieldname sa X from

Querybuilder X: long.horrible_fieldname

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 17 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: sam | last post by:
When I run this SQL query: SELECT u.*, o.* FROM users u, orders o WHERE TO_DAYS(o.order_date) BETWEEN TO_DAYS('2003-09-20')-10 AND TO_DAYS('2003-09-20')+10
1
by: Steve | last post by:
I just spent waaaaaaaaaaaayy too much time trying to track down an error that was incorrectly reported just now, and I would like to see if someone can explain to me why it was reported that way. ...
29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
4
by: Robert | last post by:
What is the syntax to use in a VBA script to execute a pass-through? Thanx
24
by: deko | last post by:
I'm trying to log error messages and sometimes (no telling when or where) the message contains a string with double quotes. Is there a way get the query to insert the string with the double...
7
by: John Øllgård Jensen | last post by:
Hi Using MS Asccess 2000: In a query I'm trying to create a new field with following expression: FilmDate: Left(,4) The field "FilmNo" is another text field in the query. This is...
12
by: Brad Baker | last post by:
I am trying to write a simple ASP.net/C# page which allows users to select some values and produce a report based on a SQL query. I have a self posting dropdown form which allows users to select...
7
by: bryant | last post by:
Hi all. I am new to ASP and working in Expression Web. The following query displays the information I need in the gridview for a single record. SELECT "OE_HDR"."ORD_NO", "OE_HDR"."CUST_NAM",...
10
by: bobf | last post by:
I am using a program 'My Contact Table' which is a code generator program. It allows you to easily create a PHP/MySQL web application without writing any code. I am trying to create an additional...
7
by: Yesurbius | last post by:
I am receiving the following error when attempting to run my query. In my mind - this error should not be happening - its a straight-forward query with a subquery. I am using Access 2003 with all...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.