criteria from combo box

  • Thread starter Thread starter Jiayou Liu
  • Start date Start date
J

Jiayou Liu

I have a query which refers to a combo box on a form as one criteria.
The value of the combo is from a table.
There is one value of the field (Department) of the table is as: in ("IT", "HR")
I have thought it will retrieve all records with department as IT and HR.
However, I found the criteria is set to "in ("IT", "HR")", which make no records returned for the query.
What shall I do to make the criteria is in ("IT", "HR") instead of "in ("IT", "HR")" ?

Thanks,
Jiayou
 
Remove the extra quotes and save the query.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have a query which refers to a combo box on a form as one criteria.
The value of the combo is from a table.
There is one value of the field (Department) of the table is as: in ("IT",
"HR")
I have thought it will retrieve all records with department as IT and HR.
However, I found the criteria is set to "in ("IT", "HR")", which make no
records returned for the query.
What shall I do to make the criteria is in ("IT", "HR") instead of "in
("IT", "HR")" ?

Thanks,
Jiayou
 
Jiayou,

I'm afraid I haven't found the answer to this one to date. However, in a
standard design a user-filtered query usually serves as the recordset
for a form or a report, in which case there is a workaround. The
workaround is to not impose the filtering on the query, but let it
return all records, and filter upon opening the ofrm or report instead.
If you use a command button on a form to open another form or report,
there will be a DoCmd.OpenForm or DoCmd.OpenReport command in the
button's code; both of them have a WhereCondition argument, which can be
used with the text string returned from your combo, like:

vWHERE = "Department " & Forms![MyForm]![cboDepartment]
DoCmd.OpenReport "MyReport", , acViewPreview, , vWHERE

likewise for a form.

If that works for you, you might also consider this: instead of a combo
box with predefined combinations of departments (and the In()
statement), you could use a muslti-select listbox with the departments,
allow the user to select as many as they wish, and construct the WHERE
clause with some simple code like:

If Me.cboDepartment.ItemsSelected.Count = 0 Then
Msgbox "Please make a selection!"
Exit Sub
End If
vWHERE = "Department In ('"
For Eack itm In Me.cboDepartment.ItemsSelected
vWhere = vWhere & Me.cboDepartment.ItemData(itm) & "','"
Next
vWHERE = Left(vWHERE, Len(vWHERE) - 2) & ")"

DoCmd.OpenReport "MyReport", , acViewPreview, , vWHERE

HTH,
Nikos
 
Nikos,

I used filter properties of the form instead as I need to automatically
e-mail the query result to pre-defined recepients.

It works fine.

Much appreciate your help on it.

Thanks,
Jiayou

Nikos Yannacopoulos said:
Jiayou,

I'm afraid I haven't found the answer to this one to date. However, in a
standard design a user-filtered query usually serves as the recordset
for a form or a report, in which case there is a workaround. The
workaround is to not impose the filtering on the query, but let it
return all records, and filter upon opening the ofrm or report instead.
If you use a command button on a form to open another form or report,
there will be a DoCmd.OpenForm or DoCmd.OpenReport command in the
button's code; both of them have a WhereCondition argument, which can be
used with the text string returned from your combo, like:

vWHERE = "Department " & Forms![MyForm]![cboDepartment]
DoCmd.OpenReport "MyReport", , acViewPreview, , vWHERE

likewise for a form.

If that works for you, you might also consider this: instead of a combo
box with predefined combinations of departments (and the In()
statement), you could use a muslti-select listbox with the departments,
allow the user to select as many as they wish, and construct the WHERE
clause with some simple code like:

If Me.cboDepartment.ItemsSelected.Count = 0 Then
Msgbox "Please make a selection!"
Exit Sub
End If
vWHERE = "Department In ('"
For Eack itm In Me.cboDepartment.ItemsSelected
vWhere = vWhere & Me.cboDepartment.ItemData(itm) & "','"
Next
vWHERE = Left(vWHERE, Len(vWHERE) - 2) & ")"

DoCmd.OpenReport "MyReport", , acViewPreview, , vWHERE

HTH,
Nikos

Jiayou said:
I have a query which refers to a combo box on a form as one criteria.
The value of the combo is from a table.
There is one value of the field (Department) of the table is as: *in
("IT", "HR")*
I have thought it will retrieve all records with department as IT and HR.
However, I found the criteria is set to *"in ("IT", "HR")"*, which make
no records returned for the query.
What shall I do to make the criteria is *in ("IT", "HR")* instead of
*"in ("IT", "HR")"* ?

Thanks,
Jiayou
**
**
Click to expand...
Click to expand...
 
Back
Top