Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Counting results and optimizing a query... Or something..

    Picking up the pieces...

    The below query is built dynamically in VBA.
    Code:
    SELECT FirstName, LastName, UniqueID 
    FROM SearchTable 
    WHERE (FirstName LIKE '*george*' AND LastName LIKE '*v*') 
    ORDER BY LastName
    The rowsource of a listbox is then set to the above SQL statement and requeried to display the results.

    Now, silly little question that I just can't get the answer to... I want to stick the number of records returned by the query into a textbox on the form - any suggestions?

    Secondly, because the above example is built dynamically - it does not handle blanks well (if at all...). Here's the current code
    Code:
        SQL = SQL & "SELECT FirstName, LastName, UniqueID "
        SQL = SQL & "FROM SearchTable "
        SQL = SQL & "WHERE (FirstName LIKE '*" & SearchStr1 & "*' "
        SQL = SQL & AndOr
        SQL = SQL & " LastName LIKE '*" & SearchStr2 & "*') "
        SQL = SQL & "ORDER BY " & OrderStr
    Now, if SearchStr1 (or 2) is blank, it returns all records apart from nulls/blanks. I'm having one of thsoe days today when I can't see the wood for the trees, could some kind soul put me out of my misery and point out the obvious?
    George
    Home | Blog

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    George,

    Hot damn! I think the 1st time I've answered a question of yours ... The recordCount is a prop of the recordset you're using (ADO or DAO). With ADO, you have to do the MoveFirst and MoveLast dance to get an accurate count ... So, open recordset, do the dance (no texas two step here RNG), capture the count for the text box, move to the 1st record again and process.

    What is your 2nd question???? Is it handling the blanks/nulls for the SQL?

    If so, ...

    Change: SearchStr1 & "*' "

    To: IIF(SearchStr1 & "" <> "",SearchStr1 & "*","") & "' "

    I'll leave the other 1 for you ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ummm, for the first one, is it too simple to use
    me.mylistbox.listcount

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I do have to say Izy, DOH!!!! I blame it on my allergy & cold meds for missing that obvious one ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    EDIT: I understand it now. *rolls eyes*

    But yeah, stll not returning the results I need...
    Code:
                SearchStr1 = IIf(Me.txtFirstName & "" <> "", Me.txtFirstName & "*", "") & "' "
                SearchStr2 = IIf(Me.txtLastName & "" <> "", Me.txtLastName & "*", "") & "' "
    
        SQL = SQL & "SELECT FirstName, LastName, UniqueID "
        SQL = SQL & "FROM SearchTable "
        SQL = SQL & "WHERE (FirstName LIKE '*" & SearchStr1
        SQL = SQL & AndOr
        SQL = SQL & " LastName LIKE '*" & SearchStr2 & ") "
        SQL = SQL & "ORDER BY " & OrderStr
    Quote Originally Posted by Result
    SELECT FirstName, LastName, UniqueID FROM SearchTable WHERE (FirstName LIKE '*' AND LastName LIKE '*' ) ORDER BY LastName
    Which doesn't return all the results (if I make it an OR statement it returns people with a blank first OR last name, but not ones with double blanks (don't ask me why they're there, they just are ))

    Last edited by gvee; 03-27-07 at 05:38.
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by georgev
    Which doesn't return all the results
    I'm pretty sure it returns everything that fits the condition

    What results aren't you getting?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well, yeah but no but

    Quote Originally Posted by Example
    UID LastName FirstName

    1 Smith John
    2 Smith Sarah
    3 Taylor Tim
    4 Mercury Freddy
    5 Minogue Kylie
    6 Dalton Timothy
    7 Willis Bruce
    8 Jackson Samuel L
    9 Sandler Adam
    10 Burne Domonic
    11 Moyles Chris
    12 Cox Sarah
    13 Jackson Michael
    14 Jackson Jermaine
    15 Thomas
    16 Thompson
    17
    18
    19
    20 Jones
    Basically, if the user enters nothing into either textbox (txtFirstName and txtLastName) then I want to return all results including blanks/nulls.

    The current SQL statement does not return the blanks.

    "" <> "*"
    Null <> "*"
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2002
    Posts
    272
    Aight. Use Nz() to convert your nulls to empty strings, like so:

    WHERE (Nz(FirstName, '') LIKE '*' AND Nz(LastName, '') LIKE '*')

    Empty string are Like '*'.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nice idea, but I still have the issue that

    this doesn't work: '' LIKE '*'
    Blank strings are not like '*'
    George
    Home | Blog

  10. #10
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by georgev
    Blank strings are not like '*'
    Yes they are!
    Select * from MyTable where '' like '*'
    returns all rows from MyTable. Do you get an empty resultset?

    I'm using Access 2000. Is this a setting maybe?

    <edit>
    The Help function tells me that Like '*' will return zero-length strings, but not nulls. (I could copy it here, but it's in Dutch.)
    Last edited by ivon; 03-27-07 at 07:44.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    LIKE '*' means with value
    Empty strings and nulls do not match this criteria.
    Somone back me up here

    EDIT: Just read your edit
    George
    Home | Blog

  12. #12
    Join Date
    Nov 2002
    Posts
    272
    Zero-length string are values, so they should match

    Seriously. What version are you using? Did you try the select query in my previous post?

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yes. I also tried:
    WHERE Null LIKE '*' which returned 0 records.

    I think I'm going to go back to the drawing board for a while, I've lost my head in all these changes...

    EDIT: Added an example mdb file with the search form. I'm having a bad day
    Attached Files Attached Files
    Last edited by gvee; 03-27-07 at 08:46.
    George
    Home | Blog

  14. #14
    Join Date
    Nov 2002
    Posts
    272
    The Nz() function, as in:
    Code:
        SQL = SQL & "SELECT FirstName, LastName, UniqueID "
        SQL = SQL & "FROM SearchTable "
        SQL = SQL & "WHERE (Nz(FirstName, '') LIKE '*" & Me.txtFirstName & "*' "
        SQL = SQL & AndOr & " "
        SQL = SQL & "Nz(LastName, '') LIKE '*" & Me.txtLastName & "*') "
        SQL = SQL & "ORDER BY " & OrderStr
    seems to work perfectly... I really don't know what could cause your problem.

    In fact, I'm not even sure anymore that I get what your problem is. Could you list some test cases: search strings, and/or selection and desired result?

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hmm, I swear I did that before - it's one of those days again!
    Quote Originally Posted by georgev
    having one of thsoe days today when I can't see the wood for the trees, could some kind soul put me out of my misery and point out the obvious?
    Thanks Ivon, you've been a star.
    George
    Home | Blog

Posting Permissions

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