Thread: help on reports..
12-09-04, 16:20 #1Registered User
- Join Date
- Jul 2004
Unanswered: help on reports..
I am creating a report based on the selection criteria in the formwhere I have controls 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 checkboxes for education.
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.
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 if put 'or' in between ((joblocation and status) OR education) 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 employee with RN degree only
How can I fix my query for this
Please help me..I would appreciate your help.
Thanks in advance
12-11-04, 04:32 #2Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
build yourself some SQL:
dim strSQL as string
strSQL = "SELECT * FROM yourTableName WHERE ((1=1) "
if not Isnull(thisCombo) then
strSQL = strSQL & "AND (someField = " & thisCombo & ") "
if not isnull(thatCombo) then
strSQL = strSQL & "AND (anotherField = " & thatCombo & ") "
...and so on until you have checked all the possible criteria and added to the SQL for each one that exists. note that there is a trailing <space> on each of the above SQL lines.
finally, stick the tail on the SQL...
strSQL = strSQL & ");"
so now you have SQL that does what you want.
one way to use it is to stick it in the query that the report looks at.
here is DAO-how (ADO equivalent exists):
dim dabs as dao.database
dim qdef as dao.querydef
set dabs = currentdb
set qdef = dabs.querydefs("nameOfSavedQueryUsedByTheReport")
qdef.SQL = strSQL
set qdef = nothing
set dabs = nothing
...and run the report.
consider setting your front-end to compact on close: editing querydefs can cause the file to grow.
izycurrently using SS 2008R2