Query Criteria dependant on partial string in another field

[shelzmike]

I am pretty much a newb at creating my own queries but can understand most that are already put together.



Anyhow, I have a simply query that is pulling info from a couple of tables and I need help on determining how to specifiy the criteria (or understanding at least if this can be done).

Here is the code:

 
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [TBL_ACTIVE WO].[TECH NUMBER], [TBL_ACTIVE WO].[CSG LAST CHANGED DATE], [TBL_ACTIVE WO].[WORK ORDER TYPE], [TBL_ACTIVE WO].[CSG ACCT NUMBER], [TBL_ACTIVE WO].[WORK ORDER NUMBER], [TBL_ACTIVE WO].[CSG STATUS], [TBL_ACTIVE WO].[WO COMPLETION DATE], [TBL_ACTIVE WO].CONNECTIVITY, [TBL_ACTIVE WO].[SERVICE CODES]
FROM [TBL_ACTIVE WO] INNER JOIN [TBL_CUSTOMER ACCOUNT INFO] ON [TBL_ACTIVE WO].[CSG ACCT NUMBER] = [TBL_CUSTOMER ACCOUNT INFO].[CSG ACCT NUMBER]
WHERE ((([TBL_ACTIVE WO].[CSG LAST CHANGED DATE]) Between [Start Date] And [End Date]) AND (([TBL_ACTIVE WO].[WORK ORDER TYPE])="NC" Or ([TBL_ACTIVE WO].[WORK ORDER TYPE])="CH") AND (([TBL_ACTIVE WO].CONNECTIVITY) Is Not Null))
ORDER BY [TBL_ACTIVE WO].[CSG LAST CHANGED DATE];

What I want to be able to do is pull the Work Orders whose status is CH ONLY if the string of |Z8| is present in the Service Codes field. How do I accomplish a.) querying the Code field for just that part of the field, and b.) pull only CH's if that part is present?

By the way, here is what a code field looks like (granted, the codes can be diversely different, depending on the job)

.D|(5|/A|??|AA|HQ|}{|OR|PZ|P2|Y:|YI|ZH|Z8|1A|1D|1V|2U|4S| 4W|6P|78|AC|PZ|9:|CG|}T

Thanks!

Mike

[pbaldy]

Check out the InStr() function in help. Sounds like a criteria of

...(Status = "CH" AND InStr(...) > 0)...

would work for you.