qryQuestion

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

I am constructing this qry to show on a report. There are
2 optional fields, ClientName and OverallClientStatus. I
am having trouble with getting the correct AND/OR in
place. What I would like to do is:

1) if both fields are blank, all clients will be
displayed.
2) if client name is selected, only that client will
show regardless of the status
3) no client name selected but a status is, those
clients with that status will show.

The following is not working that way. Can anyone see
what is missing or is wrong?

SELECT ClientName, ClientCode, ClientStreetAddress1,
ClientStreetAddress2, ClientStreetAddressCity,
ClientStreetAddressState, ClientStreetAddressZipCode,
GeneralTelephone, GeneralFax, AccountManagement,
BusinessUnit, AccountManagementPerson,
OverallClientStatus, SalesPerson, EffectiveDate, Broker
FROM usrtblClientInformation
WHERE (((ClientName)=[Forms]![usrfrmReportModule]!
[cbxClientName]) OR
((OverallClientStatus)=[Forms]![usrfrmReportModule]!
[cbxOverallClientStatus])) OR
(([Forms]![usrfrmReportModule]![cbxOverallClientStatus])
Is Null) OR
((([Forms]![usrfrmReportModule]![cbxClientName]) Is Null));

Thanks for any assistance.
*** John
 
Put the following expression in the criteria of ClientName:
Forms!usrfrmReportModule!cbxClientName Or
(Forms!usrfrmReportModule!cbxClientName Is Null)

Put the following expression in the criteria of OverAllClientStatus:
Forms!usrfrmReportModule!cbxOverallClientStatus Or
(Forms!usrfrmReportModule!cbxOverallClientStatus Is Null)
 
Unfortunately, it did not work.
*** John


-----Original Message-----
Put the following expression in the criteria of ClientName:
Forms!usrfrmReportModule!cbxClientName Or
(Forms!usrfrmReportModule!cbxClientName Is Null)

Put the following expression in the criteria of OverAllClientStatus:
Forms!usrfrmReportModule!cbxOverallClientStatus Or
(Forms!usrfrmReportModule!cbxOverallClientStatus Is Null)


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


I am constructing this qry to show on a report. There are
2 optional fields, ClientName and OverallClientStatus. I
am having trouble with getting the correct AND/OR in
place. What I would like to do is:

1) if both fields are blank, all clients will be
displayed.
2) if client name is selected, only that client will
show regardless of the status
3) no client name selected but a status is, those
clients with that status will show.

The following is not working that way. Can anyone see
what is missing or is wrong?

SELECT ClientName, ClientCode, ClientStreetAddress1,
ClientStreetAddress2, ClientStreetAddressCity,
ClientStreetAddressState, ClientStreetAddressZipCode,
GeneralTelephone, GeneralFax, AccountManagement,
BusinessUnit, AccountManagementPerson,
OverallClientStatus, SalesPerson, EffectiveDate, Broker
FROM usrtblClientInformation
WHERE (((ClientName)=[Forms]![usrfrmReportModule]!
[cbxClientName]) OR
((OverallClientStatus)=[Forms]![usrfrmReportModule]!
[cbxOverallClientStatus])) OR
(([Forms]![usrfrmReportModule]![cbxOverallClientStatus])
Is Null) OR
((([Forms]![usrfrmReportModule]![cbxClientName]) Is Null));

Thanks for any assistance.
*** John
Click to expand...


.
Click to expand...
 
John,

Either you mistyped something or there is something wrong in another part of
your query because if you put those two expressions in a query it will do
exactly what you want. One thing to check -- open your query in design view.
Right click in an empty area of the query window. A dialog should appear - click
on properties and another dialog should appear. Check the Unique Values and
Unique Records properties - they should both be NO.

If you haven't found the problem yet, create a new query with just the
ClientName and OverAllClientStatus fields. Put each of the two expressions in
the appropriate field's criteria. Run the query and see what you get.

Steve
PC Datasheet


JohnE said:
Unfortunately, it did not work.
*** John


-----Original Message-----
Put the following expression in the criteria of ClientName:
Forms!usrfrmReportModule!cbxClientName Or
(Forms!usrfrmReportModule!cbxClientName Is Null)

Put the following expression in the criteria of OverAllClientStatus:
Forms!usrfrmReportModule!cbxOverallClientStatus Or
(Forms!usrfrmReportModule!cbxOverallClientStatus Is Null)


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


I am constructing this qry to show on a report. There are
2 optional fields, ClientName and OverallClientStatus. I
am having trouble with getting the correct AND/OR in
place. What I would like to do is:

1) if both fields are blank, all clients will be
displayed.
2) if client name is selected, only that client will
show regardless of the status
3) no client name selected but a status is, those
clients with that status will show.

The following is not working that way. Can anyone see
what is missing or is wrong?

SELECT ClientName, ClientCode, ClientStreetAddress1,
ClientStreetAddress2, ClientStreetAddressCity,
ClientStreetAddressState, ClientStreetAddressZipCode,
GeneralTelephone, GeneralFax, AccountManagement,
BusinessUnit, AccountManagementPerson,
OverallClientStatus, SalesPerson, EffectiveDate, Broker
FROM usrtblClientInformation
WHERE (((ClientName)=[Forms]![usrfrmReportModule]!
[cbxClientName]) OR
((OverallClientStatus)=[Forms]![usrfrmReportModule]!
[cbxOverallClientStatus])) OR
(([Forms]![usrfrmReportModule]![cbxOverallClientStatus])
Is Null) OR
((([Forms]![usrfrmReportModule]![cbxClientName]) Is Null));

Thanks for any assistance.
*** John
Click to expand...


.
Click to expand...
Click to expand...
 
Steve, I had No for the 2 property settings. Since it
still didn't work, I scrapped the query and redid it with
placing your suggestions in the criteria. Redid the
report and now it works. I have no idea why it wasn't
working before and is now.
Thanks.
*** John

-----Original Message-----
John,

Either you mistyped something or there is something wrong in another part of
your query because if you put those two expressions in a query it will do
exactly what you want. One thing to check -- open your query in design view.
Right click in an empty area of the query window. A dialog should appear - click
on properties and another dialog should appear. Check the Unique Values and
Unique Records properties - they should both be NO.

If you haven't found the problem yet, create a new query with just the
ClientName and OverAllClientStatus fields. Put each of the two expressions in
the appropriate field's criteria. Run the query and see what you get.

Steve
PC Datasheet


Unfortunately, it did not work.
*** John


-----Original Message-----
Put the following expression in the criteria of ClientName:
Forms!usrfrmReportModule!cbxClientName Or
(Forms!usrfrmReportModule!cbxClientName Is Null)

Put the following expression in the criteria of OverAllClientStatus:
Forms!usrfrmReportModule!cbxOverallClientStatus Or
(Forms!usrfrmReportModule!cbxOverallClientStatus Is Null)


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


I am constructing this qry to show on a report.
Click to expand...
There
are
2 optional fields, ClientName and
Click to expand...
OverallClientStatus.
I
am having trouble with getting the correct AND/OR in
place. What I would like to do is:

1) if both fields are blank, all clients will be
displayed.
2) if client name is selected, only that client will
show regardless of the status
3) no client name selected but a status is, those
clients with that status will show.

The following is not working that way. Can anyone see
what is missing or is wrong?

SELECT ClientName, ClientCode, ClientStreetAddress1,
ClientStreetAddress2, ClientStreetAddressCity,
ClientStreetAddressState, ClientStreetAddressZipCode,
GeneralTelephone, GeneralFax, AccountManagement,
BusinessUnit, AccountManagementPerson,
OverallClientStatus, SalesPerson, EffectiveDate, Broker
FROM usrtblClientInformation
WHERE (((ClientName)=[Forms]![usrfrmReportModule]!
[cbxClientName]) OR
((OverallClientStatus)=[Forms]![usrfrmReportModule]!
[cbxOverallClientStatus])) OR
(([Forms]![usrfrmReportModule]! [cbxOverallClientStatus])
Is Null) OR
((([Forms]![usrfrmReportModule]![cbxClientName]) Is Null));

Thanks for any assistance.
*** John


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top