Parameter query using 'OR'

[cmcdonald]

I am trying to run a parameter query which prompts for input, but I want to be able to enter more than one criteria separated by 'Or' and with no limit on the number of criteria.

It was suggested to me that I might try a looping sequence to achieve this, but, try as I might, I cannot figure out how to do this using my own limited knowledge.

Can someone help me out?

Cara

 

[MichaelRed]

I THINK the parameter query will not work for the multiple selection. The "Parameter" is a single valued "where". You can achieve the effect using code in conjunction with a combo/list box on a form.

To provide specific soloution(s) requires some additional information. In general, you need a multiselect combo/list box. This will allow/permit the selection of any/all items in the "list". You can then use the Items Selected Collection of the combo/list box to retrieve the item value(s).

The Selected Items would be used to generate the "where" clause for a SQL statement. The entire SQL statement is assigned to be the SQL property of a querfdef object, whic becomes the recordset for you process/app.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[LarryDeLaruelle]

You can do multiple parameters with prompts but each paramater prompt must be on a separate criteria line. This only works if the number of prompts remains static and you indicate that this is not the case in your situation.

Michael is pointing you to the right solution of using a form to allow the user to select a variable number of parameters.

Good luck.

Larry De Laruelle
larry1de@yahoo.com

 

[cmcdonald]

The problem with using a combo/list box is that the list of possible parameters is derived from a table of Order Numbers, which, as such, is large and dynamic.

Cara

 

[MichaelRed]

Cara,

The problem with [red]NOT[/red] using a combo/list box is that the list of possible parameters is derived from a table of Order Numbers, which, as such, is large and dynamic.

In a large collection, users WILL make 'typos' in entering the information. This WILL generate 'errors'. You WILL be blamed. It WILL take a lot of effort to prove the problem is with the data entry. You WILL be 'charged' with the problem of correcting the system to prevent the errors. It (the error/typo) WILL not be obvious in many instances, thus causing either a lot of manual effort to check each execution or some problem in the application of the info generated.

Yes, I'm belaboring the point. Let's just say " ... been there - done that ... ". Not to the enhancement of my 'carrear' in database programming. Users/data entry ARE the problem and the issue with databases. Humans are neither fast or accurate with the keyboard. Usually when the User Human errs, the 'Knee-Jerk' reaction is to blame the system/computer - but THAT IS YOU. One of my 'lessons learned' is to avoid user input - especially when this is the traditional keyboarding.

However you get the user input, it still cannot be entered as the traditional "parameter" in a parameter query, so you will need to 'build' the SQL statement in code, with the where clause containing the "list" of items.

While I can 'imagine' a number of alternative approaches to generating the 'list' of Items, I can't seem to find one where the "user" does a lot of typing - and the system performs properly.

The closest I can come is - perhaps - a text box where the user enters an Item. The system could "accumulate" individual entries (each Carriage Return denoting an Item entry - an "enpty" entry or a Tab character could indicate the end of the List). When the User was 'finised' (entering items), have the system generate TWO lists. A) the valid entries and B) the Invalid entries. This approach would permit you to "collect" the Item, the User ID, the Dat/Time stamp ... you could maintain an activity log to be able to see where problems occurred.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over