Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: DoCmd.OpenReport WHERE Variables in List

    Hi, I have some problems with my VB

    This works fine,

    DoCmd.OpenReport _
    ReportName:=ReportName, _
    view:=acViewPreview, _
    WhereCondition:="Cascode = 'AL' "


    Now, I have created a listbox name list8 which contains all the cascode and allow users to Make Multiple Selection using Extended Selection Option in Access 97.

    Change my lines to

    DoCmd.OpenReport _
    ReportName:=ReportName, _
    view:=acViewPreview, _
    WhereCondition:="Cascode = & me.List8 & "

    But this don't work. Any Help Please?

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Try this hek78:

    Code:
    DoCmd.OpenReport _
    ReportName:=ReportName, _
    view:=acViewPreview, _
    WhereCondition:="Cascode = '" & Me.List8.ItemData(Me.List8.ListIndex) & "'"
    In other words:
    Code:
    DoCmd.OpenReport "myReportName", acViewPreview, ,"[Cascode] = '" & Me.List8.ItemData(Me.List8.ListIndex) & "'"

  3. #3
    Join Date
    Jan 2004
    Posts
    7
    Hihi,
    Thanks for your reply CyberLynx.
    It works but there is one big problem,
    it does not enable multiple selection.

    I tried to test the following code out as well but it turns out that every my whole report will show rather then filter by the Cascode

    DoCmd.OpenReport _
    ReportName:=ReportName, _
    view:=acViewPreview, _
    WhereCondition:="Cascode = 'AZ' OR 'AL'"

    As for your code, only one cascode will display although I have done multiple selection.

    Any advise CyberLynx?

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    You can try this but it's very limited and cumbersome:

    Code:
    Dim varItm As Variant
    Dim WhereStrg
    
    For Each varItm In Me.List8.ItemsSelected
           WhereStrg = WhereStrg & "[Cascode] = '" & Me.List8.ItemData(varItm) & "' OR " 
    Next varItm
    WhereStrg = Left$(WhereStrg, Len(WhereStrg) - 4) & Chr$(34)
    DoCmd.OpenReport "myReportName", acViewPreview, ,WhereStrg
    Sorry.

  5. #5
    Join Date
    Jan 2004
    Posts
    7
    Hi Thanks, I believe that will work well if the user did not choose too many items from the list.

    But something frustrate me.. I got

    Syntax error in string query expression '([Cascode] ='BE' OR [Cascode]='CI'")'.

    I have been experimenting with the " .. but I just could not get the correct one.

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    "[Casecode] = 'BE' OR [Cascode] = 'CI'"
    Last edited by CyberLynx; 01-16-04 at 03:27.

  7. #7
    Join Date
    Jan 2004
    Posts
    7
    Private Sub Command10_Click()
    Dim ReportName
    Dim varItm As Variant
    Dim WhereStrg

    StartDate = Format(StartDate, "mm/dd/yyyy")
    EndDate = Format(EndDate, "mm/dd/yyyy")
    ReportName = "DailyComSalesTCR"

    __________________________________
    I have commented out this working part
    __________________________________
    DoCmd.OpenReport _
    ReportName:=ReportName, _
    view:=acViewPreview, _
    WhereCondition:="Closing Between #" & _
    Me.StartDate & "# AND #" & _
    Me.EndDate & "#"
    __________________________________


    For Each varItm In Me.List8.ItemsSelected
    WhereStrg = WhereStrg & "[Cascode] = '" & Me.List8.ItemData(varItm) & "' OR "
    Next varItm
    WhereStrg = Left$(WhereStrg, Len(WhereStrg) - 4) & Chr$(34)
    DoCmd.OpenReport "myReportName", acViewPreview, ,WhereStrg

    End Sub


    The problem is having a " coming out from no where fromthe query. Don't know how to solve that.

    And Just wondering, how can I join the date query and the Cascode query together into one.

    This is my 3rd day using VBA. U believe it?? No choice.. have to learn while working.

  8. #8
    Join Date
    Nov 2003
    Posts
    1,487
    Code:
    For Each varItm In Me.List8.ItemsSelected
        'Make sure the line below is all ONE line.
        WhereStrg = WhereStrg & "(([Cascode] = '" & Me.List8.ItemData(varItm) & "' AND " & _
        "([Closing] >= #" & Me.StartDate & "# AND " & _
        "[Closing] <= #" & Me.EndDate & "#)) OR " 
    Next varItm
    WhereStrg = Left$(WhereStrg, Len(WhereStrg) - 4) & Chr$(34)
    DoCmd.OpenReport "myReportName", acViewPreview, ,WhereStrg

  9. #9
    Join Date
    Jan 2004
    Posts
    7
    Hi, thanks

    I'm sure the code works fine in your com, but I still can't get it work because " coming out from no where. Guess the problem lies with me. Gonna put it aside for a few days and get back to this problem again. will post the solution here if I'm able to get this done.

    Thanks for your help cyberlynx.

  10. #10
    Join Date
    Jan 2004
    Posts
    7

    Manage to solve it

    Hi,
    I manage to solve the problem and with your help of course. Thou' the solution have some problems when too many is selected from the list.


    For Each varItm In Me.List4.ItemsSelected
    WhereStrg = WhereStrg & "((Stonum = '" & Me.List4.ItemData(varItm) & "') AND " & _
    "(Closing Between #" & Me.StartDate & "# AND #" & Me.EndDate & "#))" & " OR "
    Next varItm
    WhereStrg = Left$(WhereStrg, Len(WhereStrg) - 4)

    For Each varItm1 In Me.List8.ItemsSelected
    WhereStrg1 = WhereStrg1 & "((Cascode = '" & Me.List8.ItemData(varItm1) & "') AND " & _
    "(Closing Between #" & Me.StartDate & "# AND #" & Me.EndDate & "#))" & " OR "
    Next varItm1
    WhereStrg1 = Left$(WhereStrg1, Len(WhereStrg1) - 4)

    WhereStrg2 = "(" & WhereStrg & ") AND " & "(" & WhereStrg1 & ")"


    DoCmd.OpenReport "DailyComSalesTCR", acViewPreview, , WhereStrg2

Posting Permissions

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