Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Posts
    33

    Unanswered: Criteria for a report

    I have an unbound form which uses a multiselect listbox to collect criteria data. The report is bound to a query and I am trying to pass the listbox .ItemsSelected as the criteria for the query behind the report.

    I have the following code on the On_click event of the preview button:
    Private Sub cmdPreview_Click()
    On Error GoTo Err_cmdPreview_Click

    Dim stDocName As String
    Dim varItem As Variant
    Dim strCriteria As String


    strType = ""
    For Each varItem In Me!List2.ItemsSelected
    strCriteria = Me!List2.Column(0, varItem)
    strType = strType & "," & strCriteria
    Next varItem


    stDocName = "rptChangeOrderSummary"
    DoCmd.OpenReport stDocName, acPreview


    Exit_cmdPreview_Click:
    Exit Sub

    Err_cmdPreview_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreview_Click
    End Sub



    The criteria in the query is :
    where CORType in (RtnstrType())

    RtnstrType is a global function which sets rtnstrType = strType

    However it doesn't work and I can't figure out where I'm going wrong or another solution. Any help would be greatly appreciated.

    Thank you,
    gina

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Gina:
    Your idea is very good, i don't see nothing wrong in your code, check step by step the fields when you run the program.
    Saludos
    Norberto

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Gina,

    Where do you instantiate the strType variable? How do you make the global assignment of rtnstrType? Check those things 1st. As an aside, look carefully at you selection criteria. You have a leading comma there that I don't see code to remove ...

    You state it doesn't work ... WHAT doesn't work? The report? What is it doing/not doing that is incorrect?

  4. #4
    Join Date
    Jul 2003
    Posts
    33
    The report previews but it is coming up with no data when there should be.

    The strType as well as the function is defined in the Global code module.

    Thank you for your help.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ok. Well look to removing that leading comma then ... Something like:

    strType=right(strType,len(strType)-1)

  6. #6
    Join Date
    Jul 2003
    Posts
    33
    Thank you, after I read your first reply I took out the leading , and it still was not filtering correctly. So, I took out the function and the global string. Instead I made a field on my form that was not visible and had the it update after each selection of the list. Then I used the form field as the criteria for my report. It seems to work just fine.

    I appreciate the help, thank you.
    gina

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Gina,

    Per your private message, what is the type for CORType in your query? Make certain that your IN list matches that type example:

    ... IN ('ABC', 'DEF', 'GHI', ...) [Strings]

    ... IN (1,2,3,4,5,...) [Integers]

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Gina,

    Since your PM states that the field is type String then your IN clause should have each member in quotes or single quotes (like in my previous example) ...

  9. #9
    Join Date
    Jul 2003
    Posts
    33
    Thanks I've tried it with and without the quotes and neither seems to work. If i have the quotes none of the selections work. without the quotes I can get the selection to work but only when there is a single selection.

    thanks

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Gina,

    Post the resulting code that is generated ... And ideally the SQL string you're trying to run.

  11. #11
    Join Date
    Jul 2003
    Posts
    33
    I'm not actually running SQL. The selection results are written to a hidden unbound txtfield. Then I am passing the txtfield to the query behind the report using the following:

    CORType in (Forms!frmPCOSummary.txtPreviewType)

    What about my initial way of using a function as the criteria. I couldn't figure out why the strType was null when the function was called from the query.

    Thanks,
    Gina

Posting Permissions

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