By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 1,035 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

Syntax Error in Query

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
"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 discussion thread is closed

Replies have been disabled for this discussion.