Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    3

    Unanswered: Listbox with a parameter

    Hello all,

    Got a couple of questions here, first one...can someone enlighten me as to why using a parameter in a query is so much faster than using a variable?

    Only difference in this is using an entered parameter vs passing it a variable. Same results, parameter is instant, passed variable takes about 45 seconds.

    Entered parameter:

    str_current = "SELECT distinct(ucase([Patient name])) as [PT Name], [PT SSN] FROM [Raw Data] WHERE [pt SSN] in (SELECT [Raw Data].[Pt SSN]
    FROM (SELECT DISTINCT [pt ssn], [" & query_type & "] FROM [raw data])
    GROUP BY [Raw Data].[Pt SSN]
    HAVING Count([Raw Data].[" & query_type & "]) > [Greater than how many?]) Order by ucase([Patient name])"

    Variable:

    str_current = "SELECT distinct(ucase([Patient name])) as [PT Name], [PT SSN] FROM [Raw Data] WHERE [pt SSN] in (SELECT [Raw Data].[Pt SSN]
    FROM (SELECT DISTINCT [pt ssn], [" & query_type & "] FROM [raw data])
    GROUP BY [Raw Data].[Pt SSN]
    HAVING Count([Raw Data].[" & query_type & "]) > " & p_num & ") Order by ucase([Patient name])"

    me.lisboxname.rowsoure = str_current
    Entered value is 4, passed variable is also 4. So Access must do something totally different with a user entered parameter and I'd like to learn what that is.





    I was attempting to do the above using querydef and not totally understanding it all. Here is my code:

    Dim str_current As String
    Dim qDef As QueryDef
    Dim db As Database
    DoCmd.DeleteObject acQuery, "Mult1"
    Set db = CurrentDb

    str_current = "SELECT distinct(ucase([Patient name])) as [PT Name], [PT SSN] FROM [Raw Data] WHERE [pt SSN] in (SELECT [Raw Data].[Pt SSN]
    FROM (SELECT DISTINCT [pt ssn], [" & query_type & "] FROM [raw data])
    GROUP BY [Raw Data].[Pt SSN]
    HAVING Count([Raw Data].[" & query_type & "]) > [Greater than how many?]) Order by ucase([Patient name])"

    Set qDef = db.CreateQueryDef("Mult1", str_current)
    qDef.Parameters("Greater than how many?") = p_num
    Me.lstMembers.RowSource = "Mult1"
    Me.lstMembers.Requery


    This runs, however, I have to enter the parameter when the listbox's rowsource is being set to the query which I was trying to pass with p_num (an input box to the user). How can I set this parameter successfully with the value of the input box so I do not have to enter it when the listbox is being set?

    Thanks

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums

    I know that for linked tables it helps to run stored queries rather than dynamic queries, but I'm not sure about variable vs. parameter queries. I did find this article rather interesting upon a google search.

    Have you done time trials on your two different approaches? What's the comparison?

    Also, does it let you set the query without the semi-colon (";") at the end of the SQL string?
    Me.Geek = True

  3. #3
    Join Date
    Dec 2009
    Posts
    3
    Nice article thanks. If I have time to do some kind of benchmark on the queries to see the difference i'll post it. It's pretty ridiculous though, only about 8k rows of data in my table and 5 are returned with the query. Parameter built in is instantaneous...change it to a passed in variable and it's 45 secs to return the same 5 lines. I can understand if I was changing the subquery but to simply change (count > [param value]) to (count > 4 ) and have that much time added blows my mind.


    About the semi-colon, i normally don't use them when writing vba in access. I guess since i HAVE to use it when writing pl/sql that it's fun to cheat :P

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    If you prompt for the variable instead, what code do you use for that? Specifically, how do you dim "p_num"?

    I'm curious to figure this one out...
    Me.Geek = True

  5. #5
    Join Date
    Dec 2009
    Posts
    3
    It's pretty basic, something like this with the other code removed:

    Private sub cmdRun_Click()

    dim input_num as integer

    input_num = inputbox("Enter value: ")
    Call multiples(input_num)

    end sub

    private sub multiples(p_num as integer)

    str_current = 'query that pulls the data using p_num

    me.lstMembers.Rowsource = str_current

    end sub

    Soon as it hits that list box rowsource is when it hangs.

Posting Permissions

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