Not Like Conundrum

[aas4mis]

I have a msaccess select query that needs some "rows" excluded. My rows are in the format AA0102AB (breakdown.. AA - building / 01 - row / 02 - bin / AB - slot(left or right)). I need to run a select excluding building GG and HH rows 15 and lower. Currently my query is

 SELECT DISTINCT cferr.nametx AS [Full Name], cferr.cfopid AS Initials, cferr.ptid AS [Pick Trans], cferr.itemno AS [Item No], cferr.binlocid AS [Bin Loc], cferr.cfdt AS [Date], cferr.cftm AS [Time], cferr.cfqq
FROM cferr
WHERE (((cferr.nametx)=[Forms]![frmSearch]![List1]) AND ((cferr.binlocid) Not Like "GG0*" And (cferr.binlocid) Not Like "GG10*" And (cferr.binlocid) Not Like "GG11*" And (cferr.binlocid) Not Like "GG12*" And (cferr.binlocid) Not Like "GG13*" And (cferr.binlocid) Not Like "GG14*" And (cferr.binlocid) Not Like "GG15*" And (cferr.binlocid) Not Like "HH0*" And (cferr.binlocid) Not Like "HH10*" And (cferr.binlocid) Not Like "HH11*" And (cferr.binlocid) Not Like "HH12*" And (cferr.binlocid) Not Like "HH13*" And (cferr.binlocid) Not Like "HH14*" And (cferr.binlocid) Not Like "HH15*") AND ((cferr.cfdt) Between [Forms]![frmSearch]![txtStartDate] And [Forms]![frmSearch]![txtEndDate]))
ORDER BY [cferr.cfdt], cferr.cftm;

This works but There has to be a better way of doing this. Any tips? VBA if possible.

Thanks!

[jaxjagfan]

I have a msaccess select query that needs some "rows" excluded. My rows are in the format AA0102AB (breakdown.. AA - building / 01 - row / 02 - bin / AB - slot(left or right)). I need to run a select excluding building GG and HH rows 15 and lower. Currently my query is

 SELECT DISTINCT cferr.nametx AS [Full Name], cferr.cfopid AS Initials, cferr.ptid AS [Pick Trans], cferr.itemno AS [Item No], cferr.binlocid AS [Bin Loc], cferr.cfdt AS [Date], cferr.cftm AS [Time], cferr.cfqq
FROM cferr
WHERE (((cferr.nametx)=[Forms]![frmSearch]![List1]) AND ((cferr.binlocid) Not Like "GG0*" And (cferr.binlocid) Not Like "GG10*" And (cferr.binlocid) Not Like "GG11*" And (cferr.binlocid) Not Like "GG12*" And (cferr.binlocid) Not Like "GG13*" And (cferr.binlocid) Not Like "GG14*" And (cferr.binlocid) Not Like "GG15*" And (cferr.binlocid) Not Like "HH0*" And (cferr.binlocid) Not Like "HH10*" And (cferr.binlocid) Not Like "HH11*" And (cferr.binlocid) Not Like "HH12*" And (cferr.binlocid) Not Like "HH13*" And (cferr.binlocid) Not Like "HH14*" And (cferr.binlocid) Not Like "HH15*") AND ((cferr.cfdt) Between [Forms]![frmSearch]![txtStartDate] And [Forms]![frmSearch]![txtEndDate]))
ORDER BY [cferr.cfdt], cferr.cftm;

This works but There has to be a better way of doing this. Any tips? VBA if possible.


Thanks!

SELECT DISTINCT cferr.nametx AS [Full Name], cferr.cfopid AS Initials, cferr.ptid AS [Pick Trans], cferr.itemno AS [Item No], cferr.binlocid AS [Bin Loc], cferr.cfdt AS [Date], cferr.cftm AS [Time], cferr.cfqq
FROM cferr
WHERE (cferr.nametx)=[Forms]![frmSearch]![List1])
AND Left(cferr.binl ocid,2) Not In ("GG",'HH")
AND Clng(Mid(cferr. binlocid,3,2)) >15
AND ((cferr.cfdt) Between [Forms]![frmSearch]![txtStartDate] And [Forms]![frmSearch]![txtEndDate]))
ORDER BY [cferr.cfdt], cferr.cftm;


Why not make a query and seperate the elements with column names?

SELECT DISTINCT cferr.nametx AS [Full Name], cferr.cfopid AS Initials, cferr.ptid AS [Pick Trans], cferr.itemno AS [Item No], cferr.binlocid AS [Bin Loc], cferr.cfdt AS [Date], cferr.cftm AS [Time], cferr.cfqq, Left(cferr.binl ocid,2) AS [Bldg], Mid(cferr.binlo cid,3,2) as [Row], Mid(cferr.binlo cid,5,2) as [Bin], Right(cferr.bin locid,2) as [Slot]
FROM cferr
ORDER BY [cferr.cfdt], cferr.cftm;

Make the query as the source for a form. This would allow you to easily use the built in filter capabilities by right-clicking on the column(s) you want to filter.

[jcopeland38053]

I have a msaccess select query that needs some "rows" excluded. My rows are in the format AA0102AB (breakdown.. AA - building / 01 - row / 02 - bin / AB - slot(left or right)). I need to run a select excluding building GG and HH rows 15 and lower. Currently my query is

 SELECT DISTINCT cferr.nametx AS [Full Name], cferr.cfopid AS Initials, cferr.ptid AS [Pick Trans], cferr.itemno AS [Item No], cferr.binlocid AS [Bin Loc], cferr.cfdt AS [Date], cferr.cftm AS [Time], cferr.cfqq
FROM cferr
WHERE (((cferr.nametx)=[Forms]![frmSearch]![List1]) AND ((cferr.binlocid) Not Like "GG0*" And (cferr.binlocid) Not Like "GG10*" And (cferr.binlocid) Not Like "GG11*" And (cferr.binlocid) Not Like "GG12*" And (cferr.binlocid) Not Like "GG13*" And (cferr.binlocid) Not Like "GG14*" And (cferr.binlocid) Not Like "GG15*" And (cferr.binlocid) Not Like "HH0*" And (cferr.binlocid) Not Like "HH10*" And (cferr.binlocid) Not Like "HH11*" And (cferr.binlocid) Not Like "HH12*" And (cferr.binlocid) Not Like "HH13*" And (cferr.binlocid) Not Like "HH14*" And (cferr.binlocid) Not Like "HH15*") AND ((cferr.cfdt) Between [Forms]![frmSearch]![txtStartDate] And [Forms]![frmSearch]![txtEndDate]))
ORDER BY [cferr.cfdt], cferr.cftm;

This works but There has to be a better way of doing this. Any tips? VBA if possible.

Thanks!

Accoring to Access help you could combine GG10*, GG11*, GG12*, GG13*, GG14*, and GG15* into a single ....Not like "GG[10-15]*" and the same for HH.

[aas4mis]

Wow! I've been a long time reader of forums but this was my first post. I was very surprised on the speed and accuracy of the responses. Exactly what I was looking for.
Thanks for the help!