ListBox Problem

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • g diddy
    New Member
    • Sep 2009
    • 54

    #16
    The error says:

    Code:
    This expression is typed incorrectly,or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
    This error doesn't show when the quotes are not around the numbers though. I'm going to leave them off from now on (thanks for letting me know that by the way...i would have been forever trying to correctly place these quotes when they are not needed!!!). The only problem now is that the report still shows errors so the multiselect box must not be passing the values to the parameter correctly. I'm really not sure why. It used to work with single values, I would select the invigilators name (e.g. Anderson, David) and it would pass their id (1458) to the query which then created the report with the results on. The only thing I have done differently is change it from a combo box to a multi select (simple) list box. Would that have changed anything at all? The row source is still the same too

    (n.b. when 4 were selected strWhere contained:

    Code:
    [Invigilator no] IN(1458,1492,221,369)
    which looks like it is in the correct format...well to me anyway but i'm a novice!!)

    Comment

    • g diddy
      New Member
      • Sep 2009
      • 54

      #17
      I've found this webpage which seems really relevant but I can't make head nor tail of it. You might find it useful though

      Comment

      • ChipR
        Recognized Expert Top Contributor
        • Jul 2008
        • 1289

        #18
        Thanks for the link. According to that, the strWhere you have looks fine. What errors do you get on the report? What do you mean by "pass their id to the query"?

        Comment

        • g diddy
          New Member
          • Sep 2009
          • 54

          #19
          it just says #error which is because the invigilator no has not been recognised.
          in the query it has for the field Invigilator no the criteria:

          [Forms]![SelectWeek]![Combo46]

          which takes the value from the multi select list box for use as a parameter

          so when the user selects an invigilator from the multi select list box (named Combo46) it should pass their ID to the query which is then used to produce the report. When the list box only allowed the user to select single values it worked correctly it is only since trying to change it to multiple select that it failed. I hope that helps.

          Comment

          • ChipR
            Recognized Expert Top Contributor
            • Jul 2008
            • 1289

            #20
            You may not be able to reference the actual value selected in a multi-select list box that way. Notice in the code you have to reference the Combo46.ItemsSe lected collection. What you could do is whenever an item is selected in the list box, use code to generate the string in another (hidden) text box, and then reference that text box in your query.

            Comment

            • g diddy
              New Member
              • Sep 2009
              • 54

              #21
              that sounds like a plan! how would I go about doing this though? I know how to create text boxes but I'm not entirely sure on how to generate the string for use as a reference
              (eternally grateful!!)

              Comment

              • ChipR
                Recognized Expert Top Contributor
                • Jul 2008
                • 1289

                #22
                Are you using that reference in the query to filter the query results, then using the strWhere to do the exact same thing? If that is the case, you just remove that condition in the query, let it return all the records, and use the where condition in the DoCmd.OpenRepor t to see only the ones you want.

                Comment

                • g diddy
                  New Member
                  • Sep 2009
                  • 54

                  #23
                  YES!! that was the problem all along!! Thank you so much for all your help mate. I really appreciate it thank you!!

                  Comment

                  • ChipR
                    Recognized Expert Top Contributor
                    • Jul 2008
                    • 1289

                    #24
                    Glad to hear you got it working. I learned a little something too.

                    Comment

                    • NeoPa
                      Recognized Expert Moderator MVP
                      • Oct 2006
                      • 32633

                      #25
                      Just to clarify for anyone reading who gets confused by all the to-ing & fro-ing :

                      The best way to apply filtering to a report is to design a query (or Record Source) which returns all records, then apply the filter to the report itself. This is most easily done in the call that opens the report itself using the WhereCondition parameter.

                      It's possible to apply criteria to the underlying query (as the OP did in this case) only to find that flexibility later is severely reduced.

                      BTW Sorry that I didn't help in time guys. By the time I had caught up with the problem (Post #14 gave the clue - but wasn't specific about what type of Criteria it was working with so I missed it. It was only at post #19 I realised the OP was trying to applying the criteria within the query) Chip had already supplied the solution.

                      As the query was already saying "WHERE [Invigilator] = [Value from form]" the use of IN() could never work (Equivalent to [Invigilator = IN(...)). Only one equality operator can be used at a time. Either "=" OR "IN()" but not both.

                      Comment

                      Working...