passing a global variable to query

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • MMcCarthy
    Recognized Expert MVP
    • Aug 2006
    • 14387

    #16
    Should be quotation mark at the end of second example also.



    Originally posted by mmccarthy
    Sorry I was working on the wrong assumption:

    Code:
     
    "SELECT [tblSubProjectKeyWords].[ProjectNumber], " & _
    "[tblSubProjectKeyWords].[ProjectKeyWords] " & _
    "FROM tblSubProjectKeyWords " & _
    "WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]='" _
    & get_global("KeyWordsSelected") & "'));"
    And for the one that doesn't use get_global:

    Code:
     
    "SELECT [tblSubProjectKeyWords].[ProjectNumber], " & _
    "[tblSubProjectKeyWords].[ProjectKeyWords] " & _
    "FROM tblSubProjectKeyWords " & _
    "WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]='" _
    & [Forms]![frmKWtoStaff]![txtHoldSelected] & "'));"

    Comment

    • wjfraser
      New Member
      • Oct 2006
      • 21

      #17
      Originally posted by mmccarthy
      Sorry I was working on the wrong assumption:
      My apologies - I think I misunderstood your question. When you said query design, I thought you meant the Query Design window. I am using the SQL View of my query to work with it, not coding it elsewhere.

      Comment

      • MMcCarthy
        Recognized Expert MVP
        • Aug 2006
        • 14387

        #18
        Where did you put the get_global function. Is it in a module or in the code behind the form?



        Originally posted by wjfraser
        My apologies - I think I misunderstood your question. When you said query design, I thought you meant the Query Design window. I am using the SQL View of my query to work with it, not coding it elsewhere.

        Comment

        • wjfraser
          New Member
          • Oct 2006
          • 21

          #19
          Originally posted by mmccarthy
          Where did you put the get_global function. Is it in a module or in the code behind the form?
          It is in a module.

          Comment

          • MMcCarthy
            Recognized Expert MVP
            • Aug 2006
            • 14387

            #20
            Try this:

            SELECT [tblSubProjectKe yWords].[ProjectNumber], [tblSubProjectKe yWords].[ProjectKeyWords]
            FROM tblSubProjectKe yWords
            WHERE (([tblSubProjectKe yWords].[ProjectKeyWords]=get_global('Ke yWordsSelected' )));

            Comment

            • wjfraser
              New Member
              • Oct 2006
              • 21

              #21
              Same result - not working.

              Originally posted by mmccarthy
              Try this:

              SELECT [tblSubProjectKe yWords].[ProjectNumber], [tblSubProjectKe yWords].[ProjectKeyWords]
              FROM tblSubProjectKe yWords
              WHERE (([tblSubProjectKe yWords].[ProjectKeyWords]=get_global('Ke yWordsSelected' )));

              Comment

              • MMcCarthy
                Recognized Expert MVP
                • Aug 2006
                • 14387

                #22
                Re-examine how the Gobal value is being assigned

                In the function after

                Case "KeyWordsSelect ed"
                get_global = GBL_KeyWordsSel ected

                put

                Msgbox GBL_KeyWordsSel ect & " - " & get_global

                Just to check what values are showing for both.

                Comment

                • wjfraser
                  New Member
                  • Oct 2006
                  • 21

                  #23
                  Originally posted by mmccarthy
                  Re-examine how the Gobal value is being assigned

                  In the function after

                  Case "KeyWordsSelect ed"
                  get_global = GBL_KeyWordsSel ected

                  put

                  Msgbox GBL_KeyWordsSel ect & " - " & get_global

                  Just to check what values are showing for both.
                  It shows identical values:

                  'Air Quality' - 'Air Quality'

                  Comment

                  • MMcCarthy
                    Recognized Expert MVP
                    • Aug 2006
                    • 14387

                    #24
                    SELECT [tblSubProjectKe yWords].[ProjectNumber], [tblSubProjectKe yWords].[ProjectKeyWords]
                    FROM tblSubProjectKe yWords
                    WHERE (((tblSubProjec tKeyWords.Proje ctKeyWords)=get _global("KeyWor dsSelected")));

                    I've tested the above and the syntax works fine. I can't figure out why this is not working and the next one is:

                    SELECT [tblSubProjectKe yWords].[ProjectNumber], [tblSubProjectKe yWords].[ProjectKeyWords]
                    FROM tblSubProjectKe yWords
                    WHERE (((tblSubProjec tKeyWords.Proje ctKeyWords)="Ai r Quality"));

                    Comment

                    • wjfraser
                      New Member
                      • Oct 2006
                      • 21

                      #25
                      How frustrating. I pasted both of these statements into my query's SQL View, with the same results as ever (the get_global one didn't return anything but the hard-coded version worked fine).

                      Originally posted by mmccarthy
                      SELECT [tblSubProjectKe yWords].[ProjectNumber], [tblSubProjectKe yWords].[ProjectKeyWords]
                      FROM tblSubProjectKe yWords
                      WHERE (((tblSubProjec tKeyWords.Proje ctKeyWords)=get _global("KeyWor dsSelected")));

                      I've tested the above and the syntax works fine. I can't figure out why this is not working and the next one is:

                      SELECT [tblSubProjectKe yWords].[ProjectNumber], [tblSubProjectKe yWords].[ProjectKeyWords]
                      FROM tblSubProjectKe yWords
                      WHERE (((tblSubProjec tKeyWords.Proje ctKeyWords)="Ai r Quality"));

                      Comment

                      • MMcCarthy
                        Recognized Expert MVP
                        • Aug 2006
                        • 14387

                        #26
                        I can't understand it. There is nothing wrong with the syntax.

                        For some reason the get_global is not returning the string but based on what you've told me it should be.

                        Originally posted by wjfraser
                        How frustrating. I pasted both of these statements into my query's SQL View, with the same results as ever (the get_global one didn't return anything but the hard-coded version worked fine).

                        Comment

                        • wjfraser
                          New Member
                          • Oct 2006
                          • 21

                          #27
                          Well, thank you for trying to work on it with me. Is there any other way to conceptualize a solution to this? The basic issue is that I want to pass selected items from a list box (key words) as criteria to end up with a list of projects using those key words. Each project has a number, and each project number can be associated with one or more key words. So, I have been using a form to let the user select the key words and then constructing an "OR" criteria statement with the selected words. In my examples I've just been using one word, because I haven't even gotten it to function at that level yet.

                          Thanks,
                          Whitney

                          Originally posted by mmccarthy
                          I can't understand it. There is nothing wrong with the syntax.

                          For some reason the get_global is not returning the string but based on what you've told me it should be.

                          Comment

                          • wjfraser
                            New Member
                            • Oct 2006
                            • 21

                            #28
                            For the future reference of anyone who encounters this same problem, I have gotten the query to work properly now, by building it completely in the code behind the button calling the query.

                            Code:
                            Set MyDB = CurrentDb()
                            
                            strSQL = "SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords] " & _
                            "FROM tblSubProjectKeyWords " & _
                            "WHERE (([ProjectKeyWords]="
                            
                            strSQL = strSQL & GBL_KeyWordsSelected & "))"
                            
                            MyDB.QueryDefs.Delete "subqrySingleKW"
                            Set qdef = MyDB.CreateQueryDef("subqrySingleKW", strSQL)
                                
                            DoCmd.OpenQuery "subqrySingleKW", acViewNormal

                            Comment

                            Working...