Query runs from data given on form, how do you query all?

[Guest]

I didn't really know how to explain my question briefly enough for the
subject, so I hope people understand my subject enough to click this thread
if they can help. I was able to remember the answer to my question from
yesterday, but now there is something else I want to do, and I wondered if
anybody knows how.

Currently, I have several queries that will be run using the data the user
gives in certain forms. Now, what I want to be able to do is to allow the
users to either leave one of the fields in the form blank, or select some
kind of "Select all" type of option, and that would then give them all of the
records based on their other parameters.

For example: I may want to give our users a form where they are able to
enter the first and last name of the employee they are searching for, and the
name of the instructor who taught their training course. The goal would be
that they'd enter the employee's name and the name of the instructor in
question, and the query would then find all of the courses that "Employee A"
took that were taught by "Instructor 1."

However, what I'd also want to do is to allow users to choose NOT to supply
an instructor, and thereby simply get ALL of the courses this employee took.
How could I achieve this? I have several other similar queries I'd need, but
I think if anybody can explain to me just how to do one, I'd be able to apply
that to the others. Thanks for any help anybody can provide.

--
Have a nice day!

~Paul

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''''
ptaylor

 

[Guest]

For something like that, I'd build the query dynamically with VB and then run
it from VB. I don't know if this is what you are doing now. You need code
that builds the SQL WHERE clause off of the contents of the form. Most of the
code can be encapsulated in a function that you can use in all your projects.

Also, I would not have users enter anything to initiate the search, give
them drop-downs for everything. The drop downs can be fed off the actual
table contents.

-Dorian

 

[Guest]

Thanks for the help. Is this something that can't really be done in Access
then? I thought it sounded simple enough that I figured there had to be a way
to do it. To give you another example, the user would be able to enter the
employees first and last name and find them that way, or enter just their
last name, leaving the first name blank, and that would find everybody with
that last name.

For example: If Gomez Addams, Morticia Addams, and Wednesday Addams are all
in the table...

The user could enter "Gomez" in first name and "Addams" in last name. This
would give them all of Gomez's entries in the database.

If they so chose instead, they could also leave the first name blank and
type in "Addams" as the last name. Then, this should give them the entries
for Gomez, Morticia, and Wednesday.

Is there any way to do this? I know simply giving them a row of names to
select from might make that easier, but in our case, we will eventually have
so many different names that it may get too big. That is why we were thinking
it may be better, for our purposes, to allow them to search (query) for
entries given the criteria they wish.

Does anybody know of a way to do this in Access? I'm not as familiar with VB
as I am with Access and straight SQL.

--
Have a nice day!

~Paul

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''''''''
ptaylor

 

[steve]

If you Google for "cascading combo boxes" I think that may be at least part
of what you seem to need.

HTH

Steve