Results 1 to 4 of 4

Thread: Query question

  1. #1
    Join Date
    Sep 2002
    Posts
    21

    Unanswered: Query question

    I have a global string (glCriteriaStr) that contains the criteria for the following query:
    Code:
    SELECT DISTINCT Orders.[Job ID]
    FROM Orders
    WHERE ((Orders.[Job ID]) Like (Left(glCriteriaStr,7)+"*"))
    ORDER BY Orders.[Job ID];
    My form has a combo box (cboJobs). The RowSource for the combo box is set to the query name. When the form is loaded (and the query executes), a dialog box pops up asking me to "Enter Paramter Value" for the glCriteriaStr variable. But I know the variable is set because I set it in code before I call DoCmd.Openform. I also display it with msgbox() on the form_load() event and it contains the correct data. If I leave the dialog empty and just click OK, my combo box is empty. If I enter a valid criteria string in the dialog, the query works and the combo box is populated with the correct results.

    My query is supposed to return all job ids that match the first 7 characters of glCriteriaStr.

    Question: Why does the dialog pop up asking me to enter a value for glCriteriaStr?

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Use a function to return the value of your global variable.
    -Matt


    Place this function in a module
    ----------------------------------------------------
    Public Function JobID_Criteria() As Long

    On Error GoTo PROC_ERR

    JobID_Criteria = glCriteriaStr

    PROC_EXIT:
    Exit Function

    PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT

    End Function
    ----------------------------------------------------

    New query
    ----------------------------------------------------
    SELECT DISTINCT [Orders].[Job ID]
    FROM Orders
    WHERE ((([Orders].[Job ID]) Like (Left(JobID_Criteria(),7)+"*")))
    ORDER BY [Orders].[Job ID];
    ----------------------------------------------------

  3. #3
    Join Date
    Sep 2002
    Posts
    21

    Thanks Rockey

    Thank you Rockey, works great! Next question, why does that work?

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Just the way it is as far as I know. - When you want to use a variable in a query you must use a function which returns the value off that variable.

    No problem - Matt

Posting Permissions

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