Dear Brendan:
It is difficult to communicate in a "text based" newsgroups using a
graphic interface like the Query Design Grid. A query can also be
viewed in the SQL View, where it is all text. My code is ready for
use in there.
You seemed to have a partially completed query which needs a little
modification to perform as you want. This may already have some
criteria into which the code I sent must be merged. This must be done
in a logically correct way.
I suggest you post the SQL (text) View of your existing query. I'll
try to modify it in one step to what will work better for you. This
would probably be the best approach at the moment. You can then
switch to the design grid to view how it would look if you had entered
it this way. However, when the logic starts to get a bit complex,
such as using both AND and OR, the design grid is often a confusing
way to see it (let alone what Access may do to the query after you
save it!)
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
I am confused as to where I am to input this code. Do I put it in the Query
Criteria under either city or sector?
Thanks,
Brendan
Dear Brendan:
From what I now know about what you're doing, I would suggest you use
something like this for your criteria:
WHERE (City = [Forms]![SwitchBoard]![ListCitySelect] OR
Nz([Forms]![Switchboard]![ListCitySelect, "") = "")
AND (Sector = [Forms]![SwitchBoard]![ListSectorSelect] OR
Nz([Forms]![SwitchBoard]![ListSectorSelect], "") = "")
Your idea of using a wildcard in the Nz() is questionable. Were you
using LIKE instead of "="?
Anyway, that may be where the problem is. The logic of what I suggest
above should do what you want. Please let me know how this does for
you and if I can be of any other assistance.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Fri, 19 Dec 2003 11:46:24 -0800, "Brendan MAther"
On the first form you select the City and Sector then Click on the view
Button. The view Button is attached to a macro that opens a new form
that
is based on a Query. In the city Criteria of the query i have
"Nz([Forms]![SwitchBoard]![ListCitySelect],"*")" and in the Sector
Citerie
of the query I have "Nz([Forms]![SwitchBoard]![ListSectorSelect],"*")" I
am
not using Parent/Child Links. I'm unclear as to what a subform is, but I
do
not believe this is a subform. It opens up an entirely different form.
I
do not have much SQL or VBA coding skills. I understand it once I see it
for the most part, but do not have much experience with these at all.
Thanks,
Brendan
Dear Brendan:
"nothing shows up in the next form."
Is this for a subform? Are you using Parent/Child Links?
If so, you could change to writing the SQL for the RecordSource in
code. The code could then be made to filter by one or the other or
both, or possibly neither when that is the case.
I will try to answer more specifically when you give just a bit more
direction. Are my assumptions close? Do you have SQL and VBA coding
skills? Such information will help me proceed more efficiently.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Fri, 19 Dec 2003 11:05:03 -0800, "Brendan MAther"
I have a form with two choices, select city and sector, the way I have
it
set up right now is that the user selects the city and then a sector
and
clicks view it it shows all the contacts in that city from the chosen
sector. I'd like to be able to change this so that if the user does
not
select a sector it will just show all the contacts from that city and
visa
versa. Right now if I only select one choice and not the other, then
nothing shows up in the next form.
Thanks,
Brendan
