Multivalued Lookup Fields in Access 2007 and using > in a paramete

[Accessorise]

Hi

I have created a database to facilitate my work. Now I have been asked to
produce a similar one that people without access knowledge can use.

I put [Enter X data] in the underlying queries so the user when they open a
form are prompted for the data they want to filter on etc. However I have
come across several problems:

* The parameter box does not accept > coupled with a number eg >10. It will
accept 10 but not > 10. Normally I just specify the criteria in the design
view of the query and this works, but they have requested to produce it so
that this isn't necessary for the user to look behind the scenes.

* I have implemented multivalued lookup fields. Which work well and allow
the selection of more than 1 item. But to search for those combinations
although it will allow it in the query design view, in a parameter box it
does not work trying to specify two variables eg 1 or 2. I have tried
various variations such as 1, 2 (this is what it looks like after you have
selected both variables from the lookup) and I have also tried 1 and 2.

My knowledge of code is pretty pathetic. If there is an easy way that does
not require the user to go behind the scenes can you please let me know.

 

[Allen Browne]

A1: Parameter with operator
A parameter is a value (such as 10.)
It cannot be an operator as well as a value (such as >10.)

It is possible to design an interface that runs some code that creates a
query string to use as a filter, and to build the operator and value into
the string. For this kind of thing I usually provide a combo box where the
user can select the operator, and a text box alongside that where they can
enter the value to match. This makes it really easy for the end user, but
you (as developer) will need some coding skills to achieve this.

A2 Multiple criteria for Multi-Valued Fields (MVF)
Again, a parameter is a value, not a list of values with the embedded list
separators.

To be honest, it's a quite frustrating interface when a series of parameter
dialogs pop up one after the other, so you don't know what your choices are
ahead of time, you don't know which ones you might want to leave blank, and
you have to answer the whole series. A much better interface would be to
provide a form where the user can see all the choices, enter the values they
want, and the fire off the result.

Again, you will be building the filter string in code, but you have more
ways to make it really easy for the user. You can use a text box where the
user can enter multiple values, and then process them on code. Or you could
use a multi-select list box, and then loop through its ItemsSelected as you
build up your filter string.

If you really want to let the user enter multiple values in a parameter
dialog, there is a convoluted and inefficient way to achieve it. A sample of
this amusing approach looks like this:
PARAMETERS WotNum Text ( 255 );
SELECT tClient.ClientNum
FROM tClient
WHERE "," & Replace([WotNum], " ", "") & "," Like "*," & [ClientNum] & ",*";

Finally, MMFs have the initial appearance of making things easy for you, but
are actually clumsy, non-discrete (so non-normalized), and involve
super-hidden tables that Access does not expose to you. In the long term,
you are probably better of to create the related tables yourself, where you
can get at the values, query and process them in the normal way.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have created a database to facilitate my work. Now I have been asked to
produce a similar one that people without access knowledge can use.

I put [Enter X data] in the underlying queries so the user when they open
a
form are prompted for the data they want to filter on etc. However I have
come across several problems:

* The parameter box does not accept > coupled with a number eg >10. It
will
accept 10 but not > 10. Normally I just specify the criteria in the
design
view of the query and this works, but they have requested to produce it so
that this isn't necessary for the user to look behind the scenes.

* I have implemented multivalued lookup fields. Which work well and allow
the selection of more than 1 item. But to search for those combinations
although it will allow it in the query design view, in a parameter box it
does not work trying to specify two variables eg 1 or 2. I have tried
various variations such as 1, 2 (this is what it looks like after you have
selected both variables from the lookup) and I have also tried 1 and 2.

My knowledge of code is pretty pathetic. If there is an easy way that
does
not require the user to go behind the scenes can you please let me know.