Hello. Access 2007.
A colleague wants to use a combo-box as search criteria to display a second form. The second forms' recordsource is a query that use the criteria
IIf([Forms]![frmSwitchboard]![CboStage] Is Null,([Investors].[Stage].[Value]) Like "*",[Forms]![frmSwitchboard]![cboStage])
It's using [Stage].[Value] as [Stage] is a multi-valued field.
Stage is a number field so I tried amending the expression to [Stage].[Value]>0 but it still shows no records if no value is selected from the combo box.
It does work, however, if a value is selected from the combo box.
Is it possible to get this to work? Is it because the field is multi-valued that the above expression(s) won't work? Thanks, Andy.
Search multi-valued field
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Search multi-valued field
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Search multi-valued field
What is the purpose of the expression ([Investors].[Stage].[Value]) Like "*" ? In this form, it's not valid, whether Stage is a multivalued field or not.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Search multi-valued field
Hello.
If nothing is selected from the cboStage combo box we want to display all the records. Andy.
If nothing is selected from the cboStage combo box we want to display all the records. Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Search multi-valued field
Try this as criteria:
[Forms]![frmSwitchboard]![CboStage] Or [Forms]![frmSwitchboard]![CboStage] Is Null
[Forms]![frmSwitchboard]![CboStage] Or [Forms]![frmSwitchboard]![CboStage] Is Null
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Search multi-valued field
Great, that works.
He also wants to allow (somehow) the choice of two stages to use in the search criteria. That is, to have an option "This Or That" to appear in the combo box.
I am aware that I can do this using a Union Query to add a 'fake' record to the drop-down list. However, am I likely to encounter substantial difficulties because
this field is multi-valued? Thanks, Andy.
He also wants to allow (somehow) the choice of two stages to use in the search criteria. That is, to have an option "This Or That" to appear in the combo box.
I am aware that I can do this using a Union Query to add a 'fake' record to the drop-down list. However, am I likely to encounter substantial difficulties because
this field is multi-valued? Thanks, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Search multi-valued field
You can't select multiple items from a combo box.
One option would be to use two combo boxes. Another would be to use a multi-select list box, but that would require some VBA.
One option would be to use two combo boxes. Another would be to use a multi-select list box, but that would require some VBA.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Search multi-valued field
Thanks.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Search multi-valued field
How about setting the default value of the combo box to "*".
-
- Administrator
- Posts: 78626
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Search multi-valued field
That's possible too; in that case, the criteria should be
Like [Forms]![frmSwitchboard]![CboStage]
Like [Forms]![frmSwitchboard]![CboStage]
Best wishes,
Hans
Hans