Adding a street number causes no results in a select query.

[UtilityIT]

This query

SELECT Addresses_by_route.ID, Addresses_by_route.Address, Addresses_by_route.Route
FROM Addresses_by_route
WHERE (((InStr(1,[Addresses_by_route]![Address],"Douglas",1))>"0"));

results in many matches of addresses on "Douglas Blvd", but this one:

SELECT Addresses_by_route.ID, Addresses_by_route.Address, Addresses_by_route.Route
FROM Addresses_by_route
WHERE (((InStr(1,[Addresses_by_route]![Address],"404 Douglas",1))>"0"));

results on no matches. There is an address "404 Douglas Blvd" in my table.

I do not understand why the digit should make any difference in the query, but it does. This means that a query for a particular address always fails.



I must be doing something wrong, but I do not know what that might be.

[pbaldy]

Should work offhand. It would be sensitive to spaces, so could there be more than one in the data?

[Bulzie]

Would a Like work?

SELECT Addresses_by_route.ID, Addresses_by_route.Address, Addresses_by_route.Route
FROM Addresses_by_route
WHERE ((([Addresses_by_route]![Address] Like "*404 Douglas*"));

[UtilityIT]

Should work offhand. It would be sensitive to spaces, so could there be more than one in the data?

There is only one of that address in the data. But if there was more than one, I would expect all of them to be listed in the query results.

[UtilityIT]

Would a Like work?

SELECT Addresses_by_route.ID, Addresses_by_route.Address, Addresses_by_route.Route
FROM Addresses_by_route
WHERE ((([Addresses_by_route]![Address] Like "*404 Douglas*"));

A "Like" exhibits the same behavior. In fact, if my criteria is "Douglas*", I get every address on "Douglas Blvd". But if I add a single digit, "*2 Douglas*", then I get no results in the query, not even addresses like "402 Douglas Blvd". The presence of a digit simply messes the query up. But I don't understand how. The digit is just a another character in a string.

[pbaldy]

Did you verify there were no extra spaces? Perhaps use the Trim() function? Can you attach the db here?

[Micron]

agree with the sentiment that there could be extra characters or spaces or some minor deviation that you're not seeing - or even a non printable character. The only way I know of to check that is to paste the string into NotePad and see if anything crops up. Also, I would remove the 1's from the function and let the defaults take over. Not sure Instr is suitable for strings that contain what Access may interpret as digits.

[ssanfu]

SELECT Addresses_by_route.ID, Addresses_by_route.Address, Addresses_by_route.Route
FROM Addresses_by_route
WHERE (InStr(1,[Addresses_by_route]![Address],"Douglas",1))>"0";

I took out the extra parentheses in the WHERE clause.

HELP states: INSTR Function "Returns a Variant (Long) specifying the position of the first occurrence of one string within another"

Lets say [Addresses_by_route]![Address] = "404 Douglas Ave". So the Instr function should return 5.
Replacing the Instr function in the Where clause, you would get

SELECT Addresses_by_route.ID, Addresses_by_route.Address, Addresses_by_route.Route
FROM Addresses_by_route
WHERE 5 > "0";

How can a number 5 be greater than a text 0 (zero)???? Should not be comparing a number to a text string.

Maybe try

SELECT Addresses_by_route.ID, Addresses_by_route.Address, Addresses_by_route.Route
FROM Addresses_by_route
WHERE (InStr(1,[Addresses_by_route]![Address],"404 Douglas",1))> 0;

No quotes around the Zero.

[Bulzie]

I think Steve has it with the quotes around the 0. But if not or for gins, maybe use Trim and Cstr:

SELECT Addresses_by_route.ID, Addresses_by_route.Address, Addresses_by_route.Route
FROM Addresses_by_route
WHERE (InStr(1,Trim(CStr([Addresses_by_route]![Address])),"404 Douglas",1))> 0;


Or using Like:

SELECT Addresses_by_route.ID, Addresses_by_route.Address, Addresses_by_route.Route
FROM Addresses_by_route
WHERE (((Trim(CStr([Addresses_by_route]![Address])) Like "*404 Douglas*"));

[pbaldy]

I noticed the quotes, but if that was the problem it I wouldn't think it would have worked with just the street name. I suggested Trim() in post 6.

[Micron]

I had already tried that prior to my post #7 but what with being on the road for a few days, didn't get to elaborate much.

For me, in the immediate window the function wouldn't work at all without the quotes around zero, hence my comment about the Instr function not being suitable for this. As was noted, the function returns a long denoting the position of the first character found in the string being searched based on the string to search for. Kinda hard to compare a long to text, methinks. Experience has taught some of us that when it comes to text, 50 comes after 100. I suggested removing the start position and compare options (the 1's) because I had better luck with it, meaning I did get the function to work against a limited set of records when I removed them. I could find the one record with "40 E" using that syntax, but would it always work as expected? I doubt it. Maybe if the Instr function was wrapped in CStr?