qryQuestion

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
 
P

PC Datasheet

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)
 
J

JohnE

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...
 
P

PC Datasheet

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...
 
J

John

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

needing query help 1
sorting problem 3
getting the right query info 1
filtering one form from another 1
update form after adding to combo box 20
Sum in Report Errors 3
apply filter to master form 9
Insert into instead of add to the HttpModules pipeline 3

Top