Syntax error on Union all SQL query WHERE statement 1

[Brians440]

All,

I'm trying to write a query to pull the same parameter data from 10 different files. I've started the first two and am already stuck I get "SQL syntax error (missing operator) in query expression '(((T_CABS_USAG.....and so on. Here's what I have so far. TIA Brian

SELECT * FROM T_CABS_USAGE_SOC
WHERE ((((T_CABS_USAGE_SOC.BAN)like'*9533*')) OR (((T_CABS_USAGE_SOC.BAN)LIKE'*9533*')))
UNION ALL SELECT* FROM T_CABS_USAGE_SOC-2009Q3
WHERE ((((T_CABS_USAGE_SOC-2009Q3.BAN)LIKE'*9533*')) OR (((T_CABS_USAGE_SOC-2009Q3.BAN)LIKE'*9533*')));

I hate asking a basic question, but I'm stuck and nothing online I've found covers this. Thanks again.

 

[dhookom]

Are you really missing all the spaces in your SQL? Also, if you use special characters like "-" in your object names, you must wrap the name in []s.
Also, I'm not sure why you have duplicate " Like '*9533*' "
Try:

SELECT * 
FROM T_CABS_USAGE_SOC
WHERE BAN like '*9533*'
UNION ALL 
SELECT * 
FROM [T_CABS_USAGE_SOC-2009Q3]
WHERE BAN LIKE '*9533*';

Duane
Hook'D on Access
MS Access MVP

 

[Brians440]

Hey thanks very much. I had the duplicates cause I was trying make it harder than it obviously had to be. Thanks again.

 

[Brians440]

I have another question. Instead of putting in another value other than *9533* can I put one value = TEST and then in the query just have the WHERE statement read WHERE BAN LIKE '*TEST*'; That way I won't have to change the BAN statement 10X. Thanks

 

[dhookom]

You have succeeded in confusing me. Do you really want TEST or do you want something dynamically set at run-time?


Duane
Hook'D on Access
MS Access MVP

 

[Brians440]

Sorry for the confusion. I'm wanting to setup one "ID" that is global in nature. Case in point I'm going to have 10 files of the same type of data. Instead of having to change the "BAN" field 10x let's say from 9533 to 5119 for each union all, is there a way to set a global reference? So, I would just put the 5119 in one time in place of the 9533.

I'm writing this for a groups use and we have a lot of BAN number IDs. Thanks again.

So, in my mind it would be something like this: (I know this isn't the right statement)

Define TEST = '5119'
SELECT *
FROM T_CABS_USAGE_SOC
WHERE BAN like '*TEST*'
UNION ALL
SELECT *
FROM [T_CABS_USAGE_SOC-2009Q3]
WHERE BAN LIKE '*TEST*';
UNION ALL
SELECT *
FROM [T_CABS_USAGE_SOC-2009Q4]
WHERE BAN LIKE '*TEST*';
UNION ALL
SELECT *
FROM [T_CABS_USAGE_SOC-2010Q1]
WHERE BAN LIKE '*TEST*';

 

[dhookom]

You should be referencing a control on a form. Assume you have a form [frmMyForm] with a text box [txtBAN].

SELECT * 
FROM T_CABS_USAGE_SOC
WHERE BAN like [forms]![frmMyForm]![txtBan]
UNION ALL
===etc===

Duane
Hook'D on Access
MS Access MVP