Like and Multi-criteria with Multi-Values

[roccogrand]

I have a form in Access 2007 that uses a query with multi-value fields. The
query has multiple criteria that uses two parameters (on two different lines
in design view).

The query works fine but it automatically adds a .Value field that causes
the form to not display the values of one of the critical fields (a drop
down, but not a multi-value field). The drop-down field is there but the
values are missing.

The same field is on many other forms in the application. This is the only
one where it doesn't work properly.

Is there a way to fix this?

Thanks.

David

 

[Allen Browne]

In the Field row in query design, you have the choice to use:
a) just the name of the multi-valued field, e.g.:
FieldX
or b) to use its Value:
FieldX.Value

If you choose (a), you can use your criteria as you expect. The query
yields one row for each record in the table.

If you choose (b), the query returns a row for each Value in the
multi-valued field, so the primary key will be repeated on multiple rows.
The query should still work though.

(In general, I don't find MVFs useful, because Access hides the lookup data.
Better to design tables where you can get at the relationships properly.)

 

[roccogrand]

Thanks Allen,

I have been trying choice (a) but if I delete the .Value field from the
grid, A2007 puts it back into the query every time.

Choice (b) doesn't work because then the dropdown for one of the other
fields shows as blank.

As stated, this is the only form where this is a problem in an application
with many forms. The problem must be related to the parameter query so I am
still looking for a work around.

(I would love MVFs but they don't seem to work in complex apps because A2007
tries to out think the user and this user isn't smart enough to out think it
yet. This will probably be the last time that I use them in a real app.)

David

 

[roccogrand]

P.S.

Let me know when this happens to you.

You delete a parameter and then A2007 adds it to another parameter with an
Or operator.

David

 

[Allen Browne]

Replies in-line

Thanks Allen,

I have been trying choice (a) but if I delete the .Value field from the
grid, A2007 puts it back into the query every time.

I'm not seeing Access 2007 add the '.Value' back in again.

Is there another multi-valued field (MVF) where you have criteria in this
query?

Choice (b) doesn't work because then the dropdown for one of the other
fields shows as blank.

Perhaps this is the other MVF?