If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Like and Multi-criteria with Multi-Values
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 |
#2
|
|||
|
|||
Like and Multi-criteria with Multi-Values
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.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "roccogrand" wrote in message ... 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 |
#3
|
|||
|
|||
Like and Multi-criteria with Multi-Values
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 "Allen Browne" wrote: 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.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "roccogrand" wrote in message ... 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 |
#4
|
|||
|
|||
Like and Multi-criteria with Multi-Values
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 "roccogrand" wrote: 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 "Allen Browne" wrote: 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.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "roccogrand" wrote in message ... 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 |
#5
|
|||
|
|||
Like and Multi-criteria with Multi-Values
Replies in-line
"roccogrand" wrote in message ... 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? 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. |
Thread Tools | |
Display Modes | |
|
|