Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2012
    Location
    Middle Georgia
    Posts
    11

    Unanswered: Parameter query or not

    Hello,

    I am in a quandary deciding my plan of attack. I have a database (8000 records) that has to be accessed through SharePoint. My first attempt of using filters on the main table have resulted in very slow response times (maybe it was set up incorrectly). A parameter query with multiple parameters is my next choice. Users would query the table with their user codes and whether their items are priced or not. The main form has a combo box for user codes and a check box for the pricing (if USP>0 or not).

    Scenarios
    no parameters
    User selected (returns both priced and unpriced)
    User chosen and priced selected

    This is as far as I got.

    Code:
        If Not IsNull(Me.cboFilterUser) Then
        
        If Me.chk_priced.Value = True Then
            strWhere = strWhere & "([User] Like """ & Me.cboFilterUser & "*"") AND [USP]>0 AND "
        ElseIf Me.chk_priced.Value = False Then
            strWhere = strWhere & "([User] Like """ & Me.cboFilterUser & "*"") AND "
        End If
        End If
    
            
        If IsNull(Me.cboFilterUser) = True Then
            If Me.chk_priced.Value = True Then
            strWhere = strWhere & "[USP]>0 AND "
            Else
            strWhere = strWhere & " AND "
            End If
        End If
    Am I on the right track? Where do I go from here?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That's the path I would follow too. However, I would create each part of the criteria separately, then assemble the StrWhere variable, which could remain empty (zero-length string). This would prevent the unnecessary use of wildcards:
    Code:
    SELECT *
    FROM SomeTable
    WHERE SomeColumn = '*';
    Becomes:
    Code:
    SELECT *
    FROM SomeTable;
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    i'd try
    Code:
        strWhere ="WHERE =1' use a trick know we always have a valid where clause
        If Not IsNull(Me.cboFilterUser) Then
            If Me.chk_priced.Value = True Then
                strWhere = strWhere & " AND ([User] Like """ & Me.cboFilterUser & "*"") AND [USP]>0"
            ElseIf Me.chk_priced.Value = False Then
                strWhere = strWhere & " AND ([User] Like """ & Me.cboFilterUser & "*"")"
            End If
        End If
        If IsNull(Me.cboFilterUser) = True Then
            If Me.chk_priced.Value = True Then
                strWhere = strWhere & " AND [USP]>0"
            End If
        End If
    the 1=1 dodge means the where clause will alwasy be valid, granted it doesn't matter too much in this case as you have an if with both logical outcomes covered but its a neat trick none the less
    shift the logical operators to the start of each logical element, the reason being then you no longer have hanging operators at the end of the line. personally I find it easier to read (the logic to me reads better if its at the start of the line, its also easier to spot if there are issues. also if there are a series of clauses/conditions then the and/or is added when its needed. semantically what you are doing is fine with a trailing AND you could just add the 1=1 to the end of your where clause and it would be jsut as valid SQL as at the beginning


    neither of the above make any significant difference, in my books they just tidy up the flow of code and arguably make it easier to read/debug.

    one thign I would strongly advocate when trying to debug SQL is that you examine what is actually sent to the SQL engine, whether you do that through a msgbox or put a watch on the code and step through it is up to you. you may well have valid VBA code that the VBA runtime is happy with, you may well have valid SQL that the parser is happy with, but its another thing to know that the SQL you are sending tot he parser is actually valid and doing what you think it is.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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