Need Like vs. In Query Help

  • Thread starter Thread starter Dana
  • Start date Start date
D

Dana

I want to find out how many equipment codes require a lift
vs. those that do not. I am using switch and it works
only for those conditions that are not combined with
others. For example it works for EC and codes it as Lift
but it codes EC,P,S as NoLift. How can I get the query to
recognize that EC appears anywhere in the field and to
include it in the lift category?

QUERY:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT count([TRIPARCHIVE].[TripId]) AS Trips,
[triparchive].[equipment], Switch([TRIPARCHIVE].
[Equipment] In
("PS","WC","X","EC","L","WT"),"Lift",True,"No Lift") AS
Condition
FROM CLIENT LEFT JOIN TRIPARCHIVE ON [CLIENT].[Id]=
[TRIPARCHIVE].[Clientid]
WHERE ((([TRIPARCHIVE].[Status])="s") And (([TRIPARCHIVE].
[Tripdate]) Between [start date] And [end date]) And
(([TRIPARCHIVE].[Clientid])<>3037782))
GROUP BY [triparchive].[equipment];

TABLE:
TripID Equipment
123 EC
134 EC,CN
145 EC,CN,OT
155 X
165 OX (not the same as above x)
 
You could try a calculated field that uses the InStr function to find
embedded strings:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT count([TRIPARCHIVE].[TripId]) AS Trips,
[triparchive].[equipment], (IIf(InStr([TRIPARCHIVE].[Equipment], "PS") +
InStr([TRIPARCHIVE].[Equipment], "WC") + InStr([TRIPARCHIVE].[Equipment],
"X") + InStr([TRIPARCHIVE].[Equipment], "EC") +
InStr([TRIPARCHIVE].[Equipment], "L") + InStr([TRIPARCHIVE].[Equipment],
"WT") > 0, "Lift", "No Lift") AS Condition
FROM CLIENT LEFT JOIN TRIPARCHIVE ON [CLIENT].[Id]=
[TRIPARCHIVE].[Clientid]
WHERE ((([TRIPARCHIVE].[Status])="s") And (([TRIPARCHIVE].
[Tripdate]) Between [start date] And [end date]) And
(([TRIPARCHIVE].[Clientid])<>3037782))
GROUP BY [triparchive].[equipment];
 
This looks like it should work but when I run it I get a
syntax error (missing operator) in query expression.
-----Original Message-----
You could try a calculated field that uses the InStr function to find
embedded strings:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT count([TRIPARCHIVE].[TripId]) AS Trips,
[triparchive].[equipment], (IIf(InStr([TRIPARCHIVE]. [Equipment], "PS") +
InStr([TRIPARCHIVE].[Equipment], "WC") + InStr ([TRIPARCHIVE].[Equipment],
"X") + InStr([TRIPARCHIVE].[Equipment], "EC") +
InStr([TRIPARCHIVE].[Equipment], "L") + InStr ([TRIPARCHIVE].[Equipment],
"WT") > 0, "Lift", "No Lift") AS Condition
FROM CLIENT LEFT JOIN TRIPARCHIVE ON [CLIENT].[Id]=
[TRIPARCHIVE].[Clientid]
WHERE ((([TRIPARCHIVE].[Status])="s") And (([TRIPARCHIVE].
[Tripdate]) Between [start date] And [end date]) And
(([TRIPARCHIVE].[Clientid])<>3037782))
GROUP BY [triparchive].[equipment];


--
Ken Snell
<MS ACCESS MVP>

Dana said:
I want to find out how many equipment codes require a lift
vs. those that do not. I am using switch and it works
only for those conditions that are not combined with
others. For example it works for EC and codes it as Lift
but it codes EC,P,S as NoLift. How can I get the query to
recognize that EC appears anywhere in the field and to
include it in the lift category?

QUERY:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT count([TRIPARCHIVE].[TripId]) AS Trips,
[triparchive].[equipment], Switch([TRIPARCHIVE].
[Equipment] In
("PS","WC","X","EC","L","WT"),"Lift",True,"No Lift") AS
Condition
FROM CLIENT LEFT JOIN TRIPARCHIVE ON [CLIENT].[Id]=
[TRIPARCHIVE].[Clientid]
WHERE ((([TRIPARCHIVE].[Status])="s") And (([TRIPARCHIVE].
[Tripdate]) Between [start date] And [end date]) And
(([TRIPARCHIVE].[Clientid])<>3037782))
GROUP BY [triparchive].[equipment];

TABLE:
TripID Equipment
123 EC
134 EC,CN
145 EC,CN,OT
155 X
165 OX (not the same as above x)
Click to expand...


.
Click to expand...
 
Sorry....one too many ( characters in front of the IIf:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT count([TRIPARCHIVE].[TripId]) AS Trips,
[triparchive].[equipment], IIf(InStr([TRIPARCHIVE].[Equipment], "PS") +
InStr([TRIPARCHIVE].[Equipment], "WC") + InStr([TRIPARCHIVE].[Equipment],
"X") + InStr([TRIPARCHIVE].[Equipment], "EC") +
InStr([TRIPARCHIVE].[Equipment], "L") + InStr([TRIPARCHIVE].[Equipment],
"WT") > 0, "Lift", "No Lift") AS Condition
FROM CLIENT LEFT JOIN TRIPARCHIVE ON [CLIENT].[Id]=
[TRIPARCHIVE].[Clientid]
WHERE ((([TRIPARCHIVE].[Status])="s") And (([TRIPARCHIVE].
[Tripdate]) Between [start date] And [end date]) And
(([TRIPARCHIVE].[Clientid])<>3037782))
GROUP BY [triparchive].[equipment];

--
Ken Snell
<MS ACCESS MVP>

Dana Brewer said:
This looks like it should work but when I run it I get a
syntax error (missing operator) in query expression.
-----Original Message-----
You could try a calculated field that uses the InStr function to find
embedded strings:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT count([TRIPARCHIVE].[TripId]) AS Trips,
[triparchive].[equipment], (IIf(InStr([TRIPARCHIVE]. [Equipment], "PS") +
InStr([TRIPARCHIVE].[Equipment], "WC") + InStr ([TRIPARCHIVE].[Equipment],
"X") + InStr([TRIPARCHIVE].[Equipment], "EC") +
InStr([TRIPARCHIVE].[Equipment], "L") + InStr ([TRIPARCHIVE].[Equipment],
"WT") > 0, "Lift", "No Lift") AS Condition
FROM CLIENT LEFT JOIN TRIPARCHIVE ON [CLIENT].[Id]=
[TRIPARCHIVE].[Clientid]
WHERE ((([TRIPARCHIVE].[Status])="s") And (([TRIPARCHIVE].
[Tripdate]) Between [start date] And [end date]) And
(([TRIPARCHIVE].[Clientid])<>3037782))
GROUP BY [triparchive].[equipment];


--
Ken Snell
<MS ACCESS MVP>

Dana said:
I want to find out how many equipment codes require a lift
vs. those that do not. I am using switch and it works
only for those conditions that are not combined with
others. For example it works for EC and codes it as Lift
but it codes EC,P,S as NoLift. How can I get the query to
recognize that EC appears anywhere in the field and to
include it in the lift category?

QUERY:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT count([TRIPARCHIVE].[TripId]) AS Trips,
[triparchive].[equipment], Switch([TRIPARCHIVE].
[Equipment] In
("PS","WC","X","EC","L","WT"),"Lift",True,"No Lift") AS
Condition
FROM CLIENT LEFT JOIN TRIPARCHIVE ON [CLIENT].[Id]=
[TRIPARCHIVE].[Clientid]
WHERE ((([TRIPARCHIVE].[Status])="s") And (([TRIPARCHIVE].
[Tripdate]) Between [start date] And [end date]) And
(([TRIPARCHIVE].[Clientid])<>3037782))
GROUP BY [triparchive].[equipment];

TABLE:
TripID Equipment
123 EC
134 EC,CN
145 EC,CN,OT
155 X
165 OX (not the same as above x)
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top