SQL WHERE statement

[dksj67]

I would like to set multiple criteria for an SQL WHERE statement. I tried the
following code:

PARAMETERS [MCC] Text ( 255 );
SELECT [MCC / Buses].[MCC Name] AS [MCC NUMBER], [MCC Buckets].[Equipment
Number], [MCC Buckets].Description, [MCC Buckets].[CUB NO], [MCC Buckets].
[SPACE FACTOR], [MCC Buckets].[Power QTY], [MCC Buckets].[Unit of Power],
[MCC Buckets].[Front or Rear], [MCC Buckets].[Starter Size], [MCC Buckets].
[Breaker Frame or Fuse Clip Type], [MCC Buckets].Phase, [MCC Buckets].
[Running Load], [MCC Buckets].[Fuse Size], [MCC Buckets].[Breaker AT Rating],
[MCC Buckets].[Breaker AF Rating]
FROM [MCC / Buses] INNER JOIN [MCC Buckets] ON [MCC / Buses].ID = [MCC
Buckets].[MCC Name]
WHERE (([MCC / Buses].[MCC Name])=[MCC]) AND (LEFT([MCC Buckets].[Equipment
Number]) <> "Z")
ORDER BY [MCC Buckets].[Front or Rear], [MCC Buckets].[CUB NO];

but it doesnt seem to work. Can I use a logical AND in a WHERE clause? If not
how can I set multiple criteria? Thanks,

Dan Kreiling

 

[Douglas J. Steele]

That looks like perfectly valid SQL.

What you say "it doesnt seem to work", what do you mean? How are you
invoking the query? Do you get an error message? If so, what's the error? If
you don't get an error, what does the query return, and what do you expect
it to return?

 

[dksj67]

With the below code I get an error message that says "Too many arguments" and
shows the WHERE statement. I am not invoking the query yet other than
clicking on the query icon in Access queries collection. Eventually I will
assign it a button on the switchboard.

That looks like perfectly valid SQL.

What you say "it doesnt seem to work", what do you mean? How are you
invoking the query? Do you get an error message? If so, what's the error? If
you don't get an error, what does the query return, and what do you expect
it to return?

I would like to set multiple criteria for an SQL WHERE statement. I tried
the

[quoted text clipped - 22 lines]

Dan Kreiling

 

[John Spencer]

PARAMETERS [MCC] Text ( 255 );
SELECT [MCC / Buses].[MCC Name] AS [MCC NUMBER]
, [MCC Buckets].[Equipment Number]
, [MCC Buckets].Description
, [MCC Buckets].[CUB NO]
, [MCC Buckets].[SPACE FACTOR]
, [MCC Buckets].[Power QTY]
, [MCC Buckets].[Unit of Power]
, [MCC Buckets].[Front or Rear]
, [MCC Buckets].[Starter Size]
, [MCC Buckets].[Breaker Frame or Fuse Clip Type]
, [MCC Buckets].Phase
, [MCC Buckets].[Running Load]
, [MCC Buckets].[Fuse Size]
, [MCC Buckets].[Breaker AT Rating]
, [MCC Buckets].[Breaker AF Rating]
FROM [MCC / Buses] INNER JOIN [MCC Buckets]
ON [MCC / Buses].ID = [MCC Buckets].[MCC Name]

WHERE [MCC / Buses].[MCC Name])=[MCC]


AND LEFT([MCC Buckets].[Equipment Number], 1) <> "Z"


ORDER BY [MCC Buckets].[Front or Rear], [MCC Buckets].[CUB NO];

You seem to be missing an argument in the where clasuse in the LEFT
function. YOu should have a comma one before the parentheses.

Or change the where to

WHERE [MCC / Buses].[MCC Name])=[MCC]
AND [MCC Buckets].[Equipment Number] Like "[!z]*"


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

 

[dksj67 via AccessMonster.com]

Thanks for your help

PARAMETERS [MCC] Text ( 255 );
SELECT [MCC / Buses].[MCC Name] AS [MCC NUMBER]
, [MCC Buckets].[Equipment Number]
, [MCC Buckets].Description
, [MCC Buckets].[CUB NO]
, [MCC Buckets].[SPACE FACTOR]
, [MCC Buckets].[Power QTY]
, [MCC Buckets].[Unit of Power]
, [MCC Buckets].[Front or Rear]
, [MCC Buckets].[Starter Size]
, [MCC Buckets].[Breaker Frame or Fuse Clip Type]
, [MCC Buckets].Phase
, [MCC Buckets].[Running Load]
, [MCC Buckets].[Fuse Size]
, [MCC Buckets].[Breaker AT Rating]
, [MCC Buckets].[Breaker AF Rating]
FROM [MCC / Buses] INNER JOIN [MCC Buckets]
ON [MCC / Buses].ID = [MCC Buckets].[MCC Name]

WHERE [MCC / Buses].[MCC Name])=[MCC]

AND LEFT([MCC Buckets].[Equipment Number], 1) <> "Z"

ORDER BY [MCC Buckets].[Front or Rear], [MCC Buckets].[CUB NO];

You seem to be missing an argument in the where clasuse in the LEFT
function. YOu should have a comma one before the parentheses.

Or change the where to

WHERE [MCC / Buses].[MCC Name])=[MCC]
AND [MCC Buckets].[Equipment Number] Like "[!z]*"

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

I would like to set multiple criteria for an SQL WHERE statement. I tried the
following code:

[quoted text clipped - 17 lines]

Dan Kreiling