 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2007A nice quick easy one...
...though I'm not so sure myself! Hello, From the following list in a query product ALS ALS/24 CDWS CDWS/4 CDWS/OSS CDWS/RSR DWS DWS/4 DWS/OSS DWS/HER DWS/RSR DWS/BUNDLE I would like just the following ALS CDWS DWS thanks In the criteria of the product you can use In ("ALS","CDWS","DWS")  SignatureGood Luck BS"D > ...though I'm not so sure myself! > [quoted text clipped - 24 lines] > > thanks Hello, thanks for the reply. I didn't think that queries could be filtered the way you suggested so I wasn't too clear with my question. I don't want to filter the records, just get rid of the / and everything to the right. So this... ALS ALS/24 CDWS CDWS/4 CDWS/OSS CDWS/RSR DWS DWS/4 DWS/OSS DWS/HER DWS/RSR DWS/BUNDLE OSS/OSS should become this... ALS ALS CDWS CDWS CDWS CDWS DWS DWS DWS DWS DWS DWS OSS cheers You can use a calculated field to get the result you want. Field: NoSlash: Left([Your TableName].[Your Field Name] & "/", Instr(1,[Your TableName].[Your Field Name],"/")-1) NoSlash can be replaced with the name of your field or with any other name you want to give the calculated field. SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > Hello, > [quoted text clipped - 39 lines] > > cheers that is the kind of thing I was thinking but there is a problem. Every line that doesn't have a "/" I am getting an "#Error" > You can use a calculated field to get the result you want. > [quoted text clipped - 47 lines] > > > > cheers I guess the code is assuming that every line has a "/" ?? > You can use a calculated field to get the result you want. > [quoted text clipped - 47 lines] > > > > cheers My error, appended the slash in the wrong place. Field: NoSlash: Left([Your TableName].[Your Field Name] , Instr(1,[Your TableName].[Your Field Name] & "/","/")-1) SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > I guess the code is assuming that every line has a "/" ?? > [quoted text clipped - 52 lines] >> > >> > cheers Cheers > My error, appended the slash in the wrong place. > [quoted text clipped - 57 lines] > >> > > >> > cheers Get the basic data with something like: SELECT product As root, (product & "/*" ) As mask FROM tableName WHERE NOT(product LIKE "*/*") GROUP BY product then, to 'replace' the product by its root: SELECT a.*, b.root FROM tableName As a INNER JOIN previousQuery AS b ON a.product LIKE b.mask Hoping it may help, Vanderghast, Access MVP > ...though I'm not so sure myself! > [quoted text clipped - 24 lines] > > thanks WHERE Product Not Like "*/*" That should return all records that don't contain a "/" SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > ...though I'm not so sure myself! > [quoted text clipped - 24 lines] > > thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.