Query Update based on Combo Boxes

[khashid via AccessMonster.com]

Hi, I am reposting this question, any help would be really appreciated.

I have a form with few combo boxes and a subform (query), i want the query
based on the combo boxes selection.

The issue i am facing is in the following line:

HAVING (((Outbound.CSR)=[Forms]![Outbound Test]![F_CSR]) AND ((Outbound.[Call
Type])=[Forms]![Outbound Test]![F_CallType]));

If I use "AND" then it asks me to select every combo box then it gives me
result, if i use "OR" then it gives me result depending on every option I
select in the combo box, it does not filter the query.

what I want is:
1- Query to be filtered on ever selection and user wouldnt have to use every
combobox.
2- If I want to see the whole query non filtered, whayt do I have to do so it
would delete the selections user made in the Combo Box
3- For date I want to have a text box insted of combo box, so the user can
enter the desired date, and that text box should work with the other combo
boxes to filter the query.

I hope I explained my question this time

 

[Michel Walsh]

You can test for a NULL in the form control:



WHERE ( ! FORMS!formNameHere!ControlNameHere IS NULL) IMP ( FieldName =
FORMS!formNameHere!ControlNameHere )


or


WHERE ( FORMS!formNameHere!ControlNameHere IS NULL) OR ( FieldName =
FORMS!formNameHere!ControlNameHere )




Since such a query is very poorly optimize-able, in general, you can 'wríte'
a specific SQL statement, at run time, into a string:


Dim strSQL as String
...
strSQL=strSQL & " WHERE true "
...
if ! IsNull(controlName ) Then
strSQL = strSQL & " AND fieldName = FORMS!formName!ControlName "
end if
...


and use that SQL statement. You will have to pay (time at run time) more to
get an execution plan, but it would be much better than the one you would
have got from the most generic precompiled statement, so, in the end, it may
run much faster.


Hoping it may help,
Vanderghast, Access MVP

 

[khashid via AccessMonster.com]

Am sorry I am not that good, i just want to have the query updated based on
combo box selection.

Following is my Query's SQL view:

SELECT Outbound.[Call No], Outbound.Date, Outbound.Campaign, Outbound.CSR,
Outbound.[Call Type], Outbound.[Card Type], Outbound.[Card No], Outbound.
[Customer Name], Outbound.[Customer Address], Outbound.City, Outbound.
[Customer Ph], Outbound.[Customer Cell], Outbound.Response, Outbound.[Sub
FollowUp], Outbound.[Sub NotInterested], Outbound.AppointmentDate, Outbound.
[Diverted to], Outbound.[Call Back], Outbound.Comments, Outbound.[Other
Comments]
FROM Outbound
GROUP BY Outbound.[Call No], Outbound.Date, Outbound.Campaign, Outbound.CSR,
Outbound.[Call Type], Outbound.[Card Type], Outbound.[Card No], Outbound.
[Customer Name], Outbound.[Customer Address], Outbound.City, Outbound.
[Customer Ph], Outbound.[Customer Cell], Outbound.Response, Outbound.[Sub
FollowUp], Outbound.[Sub NotInterested], Outbound.AppointmentDate, Outbound.
[Diverted to], Outbound.[Call Back], Outbound.Comments, Outbound.[Other
Comments]
HAVING (((Outbound.CSR)=[Forms]![Outbound Test]![F_CSR] Or (Outbound.CSR)=
[Forms]![Outbound Test]![F_CallType]) AND ((Outbound.[Call Type])=[Forms]!
[Outbound Test]![F_CallType] Or (Outbound.[Call Type])=[Forms]![Outbound Test]
![F_CallType]));

You can test for a NULL in the form control:

WHERE ( ! FORMS!formNameHere!ControlNameHere IS NULL) IMP ( FieldName =
FORMS!formNameHere!ControlNameHere )

or

WHERE ( FORMS!formNameHere!ControlNameHere IS NULL) OR ( FieldName =
FORMS!formNameHere!ControlNameHere )

Since such a query is very poorly optimize-able, in general, you can 'wríte'
a specific SQL statement, at run time, into a string:

Dim strSQL as String
...
strSQL=strSQL & " WHERE true "
...
if ! IsNull(controlName ) Then
strSQL = strSQL & " AND fieldName = FORMS!formName!ControlName "
end if
...

and use that SQL statement. You will have to pay (time at run time) more to
get an execution plan, but it would be much better than the one you would
have got from the most generic precompiled statement, so, in the end, it may
run much faster.

Hoping it may help,
Vanderghast, Access MVP

Hi, I am reposting this question, any help would be really appreciated.

[quoted text clipped - 23 lines]

I hope I explained my question this time

 

[khashid via AccessMonster.com]

Thanks It worked for me

Am sorry I am not that good, i just want to have the query updated based on
combo box selection.

Following is my Query's SQL view:

SELECT Outbound.[Call No], Outbound.Date, Outbound.Campaign, Outbound.CSR,
Outbound.[Call Type], Outbound.[Card Type], Outbound.[Card No], Outbound.
[Customer Name], Outbound.[Customer Address], Outbound.City, Outbound.
[Customer Ph], Outbound.[Customer Cell], Outbound.Response, Outbound.[Sub
FollowUp], Outbound.[Sub NotInterested], Outbound.AppointmentDate, Outbound.
[Diverted to], Outbound.[Call Back], Outbound.Comments, Outbound.[Other
Comments]
FROM Outbound
GROUP BY Outbound.[Call No], Outbound.Date, Outbound.Campaign, Outbound.CSR,
Outbound.[Call Type], Outbound.[Card Type], Outbound.[Card No], Outbound.
[Customer Name], Outbound.[Customer Address], Outbound.City, Outbound.
[Customer Ph], Outbound.[Customer Cell], Outbound.Response, Outbound.[Sub
FollowUp], Outbound.[Sub NotInterested], Outbound.AppointmentDate, Outbound.
[Diverted to], Outbound.[Call Back], Outbound.Comments, Outbound.[Other
Comments]
HAVING (((Outbound.CSR)=[Forms]![Outbound Test]![F_CSR] Or (Outbound.CSR)=
[Forms]![Outbound Test]![F_CallType]) AND ((Outbound.[Call Type])=[Forms]!
[Outbound Test]![F_CallType] Or (Outbound.[Call Type])=[Forms]![Outbound Test]
![F_CallType]));

You can test for a NULL in the form control:

[quoted text clipped - 31 lines]