Query Bug? Not returning consistent results

[trb5016]

I have a pop up search form that adjusts a list box query as users type.





The columns are: Person ID (Hidden), Person Name (obscured), Building, Phone (obscured)

When I type in a Building name the first 2 characters sort appropriately as seen in the pic above. However once I enter a third character this happens:



As you can see it continues to populate the list box except for the Peoples names. It is also filling in the Persons ID because the form is still usable (when I click go to record it still works)

Is there some sort of weird length limit going on?

Working Query Example (Two characters):

SELECT DISTINCTROW Directory.ID_Person, [txt_First Name] & ' ' & [txt_Last Name] AS Person, [txt_Building] & ' ' & [txt_Room Prefix] & [num_Room Number] & [txt_Room Suffix] AS Room, Phone.txt_Telephone AS Phone FROM ((People RIGHT JOIN Directory ON People.ID_Person = Directory.ID_Person) LEFT JOIN Phone ON Directory.ID_Phone = Phone.ID_Phone) LEFT JOIN Rooms ON Directory.ID_Room = Rooms.ID_Room WHERE ((([txt_Building] & ' ' & [txt_Room Prefix] & [num_Room Number] & [txt_Room Suffix]) Like '*da*') And ((Phone.txt_Telephone) Like '**'))  ORDER BY People.[txt_Last Name], People.[txt_First Name], Rooms.[txt_Building], Rooms.[txt_Room Prefix], Rooms.[num_Room Number], Rooms.[txt_Room Suffix]

Not Working Query Exaple (two plus characters):

SELECT DISTINCTROW Directory.ID_Person, [txt_First Name] & ' ' & [txt_Last Name] AS Person, [txt_Building] & ' ' & [txt_Room Prefix] & [num_Room Number] & [txt_Room Suffix] AS Room, Phone.txt_Telephone AS Phone FROM ((People RIGHT JOIN Directory ON People.ID_Person = Directory.ID_Person) LEFT JOIN Phone ON Directory.ID_Phone = Phone.ID_Phone) LEFT JOIN Rooms ON Directory.ID_Room = Rooms.ID_Room WHERE ((([txt_Building] & ' ' & [txt_Room Prefix] & [num_Room Number] & [txt_Room Suffix]) Like '*dav*') And ((Phone.txt_Telephone) Like '**'))  ORDER BY People.[txt_Last Name], People.[txt_First Name], Rooms.[txt_Building], Rooms.[txt_Room Prefix], Rooms.[num_Room Number], Rooms.[txt_Room Suffix]

This issue occurs if I paste the SQL text into a normal query as well.

Any ideas? Thanks


(edit: sp)

[trb5016]

Just wanted to add additional info that I noticed today:

This is not a consistent event. Today I used it for a little and the query worked as intended. Then I went back and it was broken, then I went back again and it worked.

Could this have something to do with record locks? I have Page Level locking on. This doesn't make any sense to me as its a query that's only reading data. Also I don't think any users are actually using the records.

Any thoughts would be appreciated... even if they're crazy ideas!

[pbaldy]

What is your actual methodology? I assume you're not using saved queries, as you'd have to account for every possible combination. Can you post the db?

[trb5016]

I can't immediately post the DB. Privacy issues and the tables are linked ODBC to an SQL Server.

If I get a chance I'll try and mockup the tables and post the relevant forms and what not.

Anyways, here's how it "works":

There are 4 tables of interest. Persons, Rooms, Phone and Directory.

The directory table simply consists of:
ID_Directory (Autonumber PK)
ID_Person
ID_Room
ID_Phone

As you can see a person can have room and then a phone that's in that room. This is all set

So now when you click a "search" button on this main form it opens up another popup form for searching. (as seen in first post)

In this form there is a Sub called UpdateList

Private Sub UpdateList(RoomName As String, Phone As String)
    Dim SQL As String
    
    SQL = "SELECT DISTINCTROW Directory.ID_Person, [txt_First Name] & ' ' & [txt_Last Name] AS Person, [txt_Building] & ' ' & [txt_Room Prefix] & [num_Room Number] & [txt_Room Suffix] AS Room, Phone.txt_Telephone AS Phone " & _
          "FROM ((People RIGHT JOIN Directory ON People.ID_Person = Directory.ID_Person) LEFT JOIN Phone ON Directory.ID_Phone = Phone.ID_Phone) LEFT JOIN Rooms ON Directory.ID_Room = Rooms.ID_Room " & _
          "WHERE ((([txt_Building] & ' ' & [txt_Room Prefix] & [num_Room Number] & [txt_Room Suffix]) Like '*" & RoomName & "*') And ((Phone.txt_Telephone) Like '*" & Phone & "*'))  " & _
          "ORDER BY People.[txt_Last Name], People.[txt_First Name], Rooms.[txt_Building], Rooms.[txt_Room Prefix], Rooms.[num_Room Number], Rooms.[txt_Room Suffix]"

    'MsgBox (SQL)
    ResultList.RowSource = SQL
    ResultList.Requery
    
    
End Sub

This sub takes a string for room and for phone and inserts them into the SQL statement so the where condition becomes "Like '*BuildingName*'"

This sub is called once when the form opens so the where condition for phone and room is Like '**' resulting in all records being show in the list box.

The OnChange events are set for each search textbox. As each character is entered the sub is called and the SQL updates and filters the proper values.

However here comes the issue. After entering 3 or more characters into the building search box, the query randomly stops returning any data for the People column. The other columns still fetch data fine.

As noted before this occurs only randomly and sometimes does work.
Also, even stranger At the very bottom of the list the query does return 1 user! I have scoured this users info and there is nothing different between her and any others.

If msgbox out the SQL statement and paste it into a normal query the same behavior happens. After 3 characters in the Like '*Dav*' it won't return any people.



Wow that's a book.

Thanks for your time. I'm almost ready to scrap this feature but it's so frustrating not to know why it doesn't work lol.


I'll try and make a sample DB to upload that replicates the issue.

[trb5016]

Well I Copied the relevant forms and tables over to a new DB.

I erased all names and numbers for privacy and deleted some columns that weren't related. Everything should be exactly the same except the tables are no longer SQL linked and are instead local.

Also there's a ton of unused code because the whole DB isn't there

The label "Physics Directory" is actually clickable to bring up the search form.

Naturally I can't reproduce the error with this though. *sigh*

Take a look though to get the gist of what I've done if you'd like

Thanks