Workaround to limit in space in the criteria box in query design view?

To what kind of field does this criterion apply? Is it a big messy text field with the company name embedded in it (inconsistantly no doubt, e.g. Philips Inc., Philips AG, Philips Electronics), or with multiple company names in the field?

 

What you may be able to do is set up a (permanent or temporary) Exclusions table with one record per company to exclude; you could then use a query like

 

SELECT <whatever>
FROM mytable LEFT JOIN Exclusions
ON mytable.myfield LIKE "*" & Exclusions.CompanyName & "*"

WHERE Exclusions.CompanyName IS NULL

AND <any other criteria>

 

But certainly, a normalized design with a numeric CompanyID is going to be a lot easier to manage both for you and your users.

 

 

Eventually I'd like to build something that the users can perhaps use pulldown boxes (or whatever) to draw upon the names already in the db and that way I and other users will be able to filter out companies using a very easy method without all the typing and real possible user errors that can happen with the method I had to use today.

Take a look at the file MultiSelect.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In the Click event procedure of the frmEmployeeDlg form which is opened from the 'Filter Report by WhereCondition Argument' button on the opening form, change the string expression for the criteria to exclude rather than include the selected employees from:

        strCriteria = "EmployeeID In(" & strEmployeeIDList & ")"
to:
        strCriteria = "EmployeeID Not In(" & strEmployeeIDList & ")"

The report will then report the employees not selected.

Note that when building the value list for the In operator when looping through the ItemsSelected collection earlier in the code in the same procedure you will need to delimit each value with quotes characters if the values are of text data type, rather than a number data type as in my example.

To what kind of field does this criterion apply? Is it a big messy text field with the company name embedded in it (inconsistantly no doubt, e.g. Philips Inc., Philips AG, Philips Electronics), or with multiple company names in the field? 

Well, it's not quite so bad.  It's a large text field with one company per record but, yes, your example is spot on - i.e., Philips Inc., Philips AG, Philips Electronics, that sort of thing.  And then there are multiple members per each company ... so quite a task to filter out different sets of companies twice a week!

Eventually I'd like to build something that the users can perhaps use pulldown boxes (or whatever) to draw upon the names already in the db and that way I and other users will be able to filter out companies using a very easy method without all the typing and real possible user errors that can happen with the method I had to use today.

Take a look at the file MultiSelect.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

The only alternative I can suggest is to switch to SQL View and tpe your string directly into there.

The only alternative I can suggest is to switch to SQL View and tpe your string directly into there.

I've never gone into that view, so that was pretty neat seeing how that works.

The syntax adds some extra wording to accommodate the table name.  However, no worries.  Until such time as my boss approves having a proper and bona-fide developer come in to help with this db (if at all), I've got an external gui written in a scripting language where I just type in the company names to filter out and will get a text string that can then be copied to the clipboard with the push of a button.  Then that can just be dumped into the criteria box.  That'll save a lot of typing time and will help eliminate user error during the typing process.  If there are any missing companies in the filter due to the maximum-character limitation in the criteria field, I'll just add manually to the SQL view.  Will still save loads of time since the exported data in the Excel sheet will be ready to use with no editing needed after the fact.

To that end, my script apparently can have a set ceiling on the maximum number of characters entered.  It would be nice to know what is the maximum allowable (including spaces between words, I imagine) in the criteria box in any query design view - does anyone know?

Thx much!!

If you have a file (or a spreadsheet, or best of all a Table) of the names you want to exclude, you can create a Query to do so. Note that this Query JUST would use a JOIN - no criteria at all! Did you try the Exclusions table I posted upthread?

 

I do have one big red warning though. Any time you have uncontrolled vocabulary and wildcards you WILL - not may, but WILL - get erroneous results. Suppose you wanted to exclude a company named API with a criterion of

 

NOT LIKE "*API*"

 

This criterion will also exclude "Apiculture" and "Rapid Graphics" and "Capitol Press" and any other name containing that text string!

 

You can get around this by making the criterion a lot more complex:

 

NOT LIKE "Api *" AND Not Like "* API *" AND NOT LIKE "* API"

 

to set the name off with blanks and allow for it to be the first substring ("API Limited"), embedded with flanking blanks ("Boise API Inc.") or at the end of the string preceded by a blank... but this isn't perfect (there might be other punctuation, not just blanks); and the query will be inefficient and a pain to maintain!

 

I do have one big red warning though. Any time you have uncontrolled vocabulary and wildcards you WILL - not may, but WILL - get erroneous results. Suppose you wanted to exclude a company named API with a criterion of

 

NOT LIKE "*API*"

 

This criterion will also exclude "Apiculture" and "Rapid Graphics" and "Capitol Press" and any other name containing that text string! 

Yes, thank you - good to point that out as other people use these boards as research, like I do.  Good to know the pitfalls.

I did run into that when I first did the exclusions when I ran across a completely different company than the one that I needed to exclude and it got dumped in <g>.  With 18,000+ records, it's bound to be an issue but I'm confident that once I learn the data better, I'll be able to keep that to a minimum.  Fortunately, when in doubt, we can minimize the errors but putting in more specific characters to get the results we need like when I knew we had subsidiaries that needed to remain even as the main were to be excluded (since the parent company was a sponsor and the subsidiary was not - needed to exclude the sponsor).

Thank you!

If you have a file (or a spreadsheet, or best of all a Table) of the names you want to exclude, you can create a Query to do so. Note that this Query JUST would use a JOIN - no criteria at all! Did you try the Exclusions table I posted upthread?

<sheepish grin> Um, sorry, no.  As I posted on Jun.10, since some of the things I couldn't wrap my brain around yet, I'm tabling that idea for a bit.  I have no extra time at work to do this and I'm already putting in 1-2 hours daily of overtime to clear up the backlog my predecessor left since he left without notice and things languished until I was brought on board.  Soon I'll stay after work to try that idea out and hopefully I'll be able to make heads or tails of it then when I'm actually working on it (I don't have A10 at home).

Thank you!

It would be nice to know what is the maximum allowable (including spaces between words, I imagine) in the criteria box in any query design view - does anyone know?

Can anyone tell me the maximum number characters the criteria box allows in a query design view?  I've continued to google but haven't found an answer.  The answers go off on all sorts of tangents with nothing revealing the answer to this specifically  ... <g>.