Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004

    Exclamation 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

    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

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

    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts