Syntax Error in Sub Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob - 16 Jul 2007 19:46 GMT

I have the following sub query in my query which is trying to calculate a
base based on two critieria within the same table that is used for the main
query.

Base: (SELECT Small Busiiness Activity Detail.Spent FROM Small Business
Activity Detail WHERE Minority Indicator Categories = "sb" or "lb")

I keep getting syntax error message. What am I doing wrong, please?

Rob

Reply to this Message

Maurice - 16 Jul 2007 20:42 GMT

Looking at your query i see a lot of spaces in the table and fieldname. Try
placing your tablename and fieldname between [ ] then try again.

So it should look like:
[Small Busiiness Activity Detail].spent

hth

Signature

Maurice Ausum

> I have the following sub query in my query which is trying to calculate a
> base based on two critieria within the same table that is used for the main
[quoted text clipped - 6 lines]
>
> Rob

Reply to this Message

Rob - 16 Jul 2007 21:02 GMT

I took your advice and am still having the same problem, i.e. my subquery has
a syntax error. This is where I am thus far:

Base: (SELECT [Small Busiiness Activity Detail].[Spent] WHERE [Small
Business Activity Detail].[Minority Indicator Categories] = "sb" or "lb")

Any help would be greatly appreciated.

Rob

> Looking at your query i see a lot of spaces in the table and fieldname. Try
> placing your tablename and fieldname between [ ] then try again.
[quoted text clipped - 14 lines]
> >
> > Rob

Reply to this Message

Chris2 - 18 Jul 2007 01:53 GMT

> I took your advice and am still having the same problem, i.e. my subquery has
> a syntax error. This is where I am thus far:
[quoted text clipped - 5 lines]
>
> Rob

Rob,

Your second subquery has no FROM clause. It won't work without one.

Try:

Base: (SELECT [Small Busiiness Activity Detail].[Spent] FROM [Small Business
Activity Detail] WHERE [Small Business Activity Detail].[Minority Indicator Categories] =
"sb" or "lb")

Your first subquery in your original post:

Base: (SELECT Small Busiiness Activity Detail.Spent FROM Small Business
Activity Detail WHERE Minority Indicator Categories = "sb" or "lb")

Did have a FROM clause.

As far as I can tell, you corrected one error, a lack of [], and then accidentally deleted
the FROM clause in the process.

I also notice that Business appears to be misspelled as Busiiness in both subqueries, and
if that is a misspelling, that will also cause and error.

Comments:

Avoid spaces or special characters in the names of tables, columns, forms, controls,
reports, and modules.

Sincerely,

Chris O.

Reply to this Message

Douglas J. Steele - 18 Jul 2007 02:49 GMT

>> I took your advice and am still having the same problem, i.e. my subquery
>> has
[quoted text clipped - 14 lines]
> Indicator Categories] =
> "sb" or "lb")

Your use of "or" is incorrect. It needs to be either

Base: (SELECT [Small Busiiness Activity Detail].[Spent] FROM [Small Business
Activity Detail] WHERE [Small Business Activity Detail].[Minority Indicator
Categories] =
"sb" or [Small Business Activity Detail].[Minority Indicator Categories] =
"lb")

or

Base: (SELECT [Small Busiiness Activity Detail].[Spent] FROM [Small Business
Activity Detail] WHERE [Small Business Activity Detail].[Minority Indicator
Categories] IN ("sb" or "lb"))

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

Reply to this Message

Marshall Barton - 16 Jul 2007 20:57 GMT

>I have the following sub query in my query which is trying to calculate a
>base based on two critieria within the same table that is used for the main
[quoted text clipped - 4 lines]
>
>I keep getting syntax error message. What am I doing wrong, please?

Names with spaces or other funky characters must be enclosed
in [ ]

Base: (SELECT [Small Busiiness Activity Detail].Spent
FROM [Small Business Activity Detail]
WHERE [Minority Indicator Categories] = "sb"
or [Minority Indicator Categories] = "lb")

Signature

Marsh
MVP [MS Access]

Reply to this Message

John Spencer - 17 Jul 2007 00:23 GMT

That may error also since it looks as if this is being used a calculated
column.

Perhaps the user need to use a SUM predicate around spent or Min or Max
or ???. This query could obviously return more than one record.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

>> I have the following sub query in my query which is trying to calculate a
>> base based on two critieria within the same table that is used for the main
[quoted text clipped - 12 lines]
> WHERE [Minority Indicator Categories] = "sb"
> or [Minority Indicator Categories] = "lb")

Reply to this Message