Help on Reports..

[Guest]

Hi,

I am creating a report based on form criteria. And on the form I have few
fields like combo boxes, checkboxes..etc. Now I want the records to appear in
my report which would be the combination of criteria..like

one combobox is: job location
2nd if for: Job staus and others are checkboxes.

Now when I pick my option for job location and for status and for other
check boxes..its giving me records based on the query (where I am using 'and'
under criteria). So it gves me records which meet all these criteria.
say:
job location is: virginia
Job staus: full-time
education (check boxes) say user picks: BA and MS

This is working great. BUt user has to pick all the the options..

Now On the other hand..I want to get a report which has :

only RN degree. Here I don't want to see other employees for job location
status etc..

Because right now the way my wuery is I will get all the virginia
employees+full-time+BA and MS employees PLUS its going to give me RN
employees also. But
I want just the employyess with RN degree

How can I fix my query for this

Please help me..I would appreciate your help.
Thanks in advance

 

[Guest]

Try having the criteria in your query say

Control OR Control IS NULL

Where Control is the field on your form.

Hope that helps,
Melinda

 

[Guest]

hi,
without seeing your query and form i can only guess but
any time you have multiple criteria, you should think of
the possibility of needing multiple queries and multiple
report. sometimes this is unavoidable.
for example.
you have a history table containing all part numbers.
you want to sometimes see all of one part.
other times you want to see all parts between certain
dates.
it is not possible to do this with one query from a form.
(maybe doing it manually it's possible)
so you have 2 queries.(maybe one report but that depends
sometimes.)
you may be is such a situation. i don't know. i'm guessing.

 

[Wayne Morgan]

Are each of these combo boxes and check boxes represented by different
fields in the recordset you're trying to filter? In other words, you have a
field for "Job Location", one for "Job Status", one for "BA" (yes/no field,
checkbox), one for "MS", one for "RN", etc. Is this correct? From what I
understand you are wanting to do, if you leave any of the selections blank,
you don't care if the answer for that field is Yes or No, but if you make a
selection, you only want to see records that have that value in the field.
For example, if you're looking for someone with a BA degree, you don't want
someone who doesn't have one, but you don't care if they also have an MS or
RN degree. Is this correct? If not, you will need the checkboxes to be set
for Triple State (Yes, No, and Null) to be able to distinguish between when
you care (you don't want them to have it) and when you don't (they may or
may not have it). These will need to be unbound checkboxes.

Next, one of the easiest (semi easy but very, very tedious) ways I've found
to get this mix of criteria into a query is to rewrite the query in code.
Click on Ok button on your form after you make your last selection and have
it concatenate together your selections then open the report. You would need
to go through one step at a time, concatenating together the WHERE clause of
the query, then assign the entire SQL statement to the query.

Example:
CurrentDb.QueryDefs("qryMyQuery").SQL = strSQL & " " & strWHERE