Query results

[BWGreen]

I think I'm probably missing something here - been looking at this too long!

I have a database that I am trying to query from a form and return a subset of records. Basically the database has a number of fields - the searched for data could be in any one of them. If I do a basic SELECT (in the SQL editor) that works, showing me all the records. But when I put in a WHERE clause, things start to go awry - the matches don't get returned. Right now, I have a search form with an unbound text box and a button - clicking the button opens the query and SHOULD execute it using the contents of the text box as the search criteria. It does open the query, but no records.

Any ideas?

Thanks,



Brian

[JoeM]

Can you post the pertinent data?
The SQL code of your query, and any VBA code, if any is involved?

[BWGreen]

I have to sanitize it a bit because of where I am, but:

SELECT *
FROM tableA
WHERE ((tableA.[field 1] = [forms]![SearchForm]![Text147]) OR (tableA.[field 1] = [forms]![SearchForm]![Text147]));

as an example - Text147 is the TextBox with the input data on SearchForm.

[JoeM]

Why are you checking for the same condition twice (with an OR statement between them)?
What is if "Field 1"?
What data type is it?

[BWGreen]

Oops typo - after the OR it should be tableA.[field 2] = [forms]![SearchForm]![Text147]

Both Text147 and the database fields are type TEXT.

[JoeM]

Hmmm...

It seems to work for me. Are sure you don't have any trailing spaces or anything like that at the end of your text entries?
Try creating a manually query to return what you want, just to make sure it will work.

Note that you can upload a copy of your database up here for analysis (just be sure to remove any sensitive information first).
I cannot download it from my current location, but should be able to tonight. Or someone else may be able to take a look at it in the meantime.

[BWGreen]

I just did some more testing here - I created a new table with test data and things worked - until the data had non-alphanumeric characters (specifically ":","." and "-"). Any idea how to get these interpreted as strings and not evaluated?

[JoeM]

I tried entering values like "x.y", and it found them just fine without issues for me.

Can you post some real examples that don't work for you, both what your data looks like, and what you are putting in the text box?

[BWGreen]

I just tried it again using ., -, and : - they all seem to work individually now. If I combine them, then I get no hits. For example, the data field to match has 3 rows - "AAA", "BBB", and "AAA.-DDD". If I enter "AAA" in the text box and use that for a search, I get one match. If I use "AAA.-DDD" - I get nothing.

The other thing I'm trying to get going - partial matches. if the data is "AAA" and "AAA.BBB" and the query string is "AAA" I would like BOTH items returned - right now all I see is the first one.

Any thoughts? I've tried both = and LIKE in the SQL - they seem to look for an exact match.

[JoeM]

If I use "AAA.-DDD" - I get nothing.

It works for me. That tells me that you may have something more than just "AAA.-DDD" in that cell.
If you use the LEN function on that field, does it return 8 or something more than that?

If you want partial matches, you need to use this syntax for your criteria:
Like "*" & [forms]![SearchForm]![Text147] & "*"

[BWGreen]

This is getting weirder and weirder. When, in the database table view, I put a filter on the field, I see no rows - even though the filter is built using data in the table! I'm just not sure what to make of this now!

[JoeM]

Where exactly is the data coming from?
Did you do the length check I suggested?

I have seen issues with data coming from certain programs or the web where they have "special characters" (like non-breaking spaces) which can cause issues.
That is why I suggested using the LEN function. If there are some odd characters on the end you aren't seeing, that might help detect them (like if it says the length of "AAA-.DDD" is 9 and not 8).

As I mentioned yesterday, another option is to upload a copy of your database for us to look at.

[BWGreen]

I did check the length - and the data in the cell was typed in, not imported.

I think I'm going to try creating a new table to see if the original is corrupt in some way that is affecting the query.

[BWGreen]

I think I figured it out -the field has a format string that inserts multiple : characters - these do not seem to be stored in the database. Adding this format string to the form for searching seems to have corrected things. Now I have to figure out the substring part!

[JoeM]

Now I have to figure out the substring part!

I posted it at the bottom of reply #10.