Query with Instr returns unexpected results

[Kowalski]

Hi
I have Access 2010, and found an interesting thing. Any explanations?

If I run this query

SELECT Reference,
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField
FROM MyTable
WHERE 
(Reference ='ShouldBeOut'
OR 
Reference = 'ShouldBeIn1'
OR 
Reference = 'ShouldBeIn2')

It returns 3 rows, with values in MyField of 0, 23 and 355.

Now I add 1 more where clause to filter on MyField and the query looks like this:

SELECT Reference,
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField
FROM MyTable
WHERE 
(Reference ='ShouldBeOut'
OR 
Reference = 'ShouldBeIn1'
OR 
Reference = 'ShouldBeIn2')
AND
(
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) > 0
)

So, you would think that it should return 2 values (ShouldBeIn1 and 2), but it doesn't. It only return the value where the Instr returns value of 23. The one with the value of 355 also dissapears. Why would that be?
Surely Instr does not return a byte?

 

[jdraw]

Please show the code for function FunctionThatReturnsLongString() and show us where ResponsiblePerson is DIMmed and some sample records.

 

[Kowalski]

Hi
The code for the function will be difficult to provide as it uses a whole number of sub functions and calls to other functions. I hope it will suffice to say that the value it returns looks something like:
'123456','547993','753214','956156','966201','156844','112554'.......

Responsible Person is a field in MyTable that contains a value like 156844.

So the values that it returns might be something like:

Reference , MyField
AA001 , 0 (ResponsiblePerson not in the long string)
AA002 , 23 (ResponsiblePerson in the long string)
AA003 , 315 (ResponsiblePerson late in the long string)

I have not tested thoroughly, but it almost looks like if the substring appears after position 255 the WhereClause throws it away.

 

[jdraw]

Text fields have a 255 char limit.
I have not use Instr in the context you have shown.
Also, I'm not following why you need the function to get fields from your table within an SQL query.
Seems with proper structure you could process this without Instr.

My suggestion is to mock up a situation with only 3 or 4 entries and step through the code to see if it is actually doing what you think.

Can you post the layout(design) of MyTable?

 

[Kowalski]

MyTable contains 30 odd columns of which we are only concerned about 2 columns so I believe showing everything might just complicate the matter.
The function also does return the expected results. The first query in my post confirms that the field ResponsiblePerson does indeed occur in the long string. It's only when filtering on it that it behaves inconsistently.

 

[jdraw]

My point is that you are using a function to create a horizontal list of concatenated strings; then you are using Instr() to see if another string occurs with your horizontal string.

You do not normally need to create the horizontal string.

If you have a field in your table that represents one of the entries in your horizontal, comma separated string, you can find it via SQL.

General format:
SELECT field1,...fieldX FROM mytable
WHERE theField = theResponsible

The criteria could be a Like..construct.. or theResponsible could come from a control on a form... or entries in another table.

If you have a list of theResponsibles in a table say TblResponsibles, then you might use a query such as

Select field1,..fieldX FROM
Mytable INNER JOIN tblResponsibles
ON MyTable.TheField =tblResponsible.Responsible

However, I don't know the specifics of what you are trying to accomplish, so I may be way off base. Again, I'm just saying I haven't used, nor seen a use of Instr() as you have shown.

Perhaps if you described What you are trying to accomplish in plain English, not showing How you have done something, there may be other options and comments. It's possible that the table design is not Normalized.

In any event, good luck with your project.

 

[vbaInet]

I've just recreated something similar to your scenario and I can't replicate the problem. I think that somewhere along the line your function is not returning the full string or you have applied a GROUP BY clause which you didn't include in your original post.

In any case, I'm with jdraw and his point about using JOINs to accomplish the same thing with greater accuracy and improved performance.

 

[Galaxiom]

It appears that you are concatenating values from multiple locations all of which hold the same kind of information.

If so then your problem isn't the limit on the field size but a serious error in your table structure.

 

[Kowalski]

Thanks for the replies. Just a bit of background of what I'm trying to accomplish.
We have a list of personnel that all belongs to a Department. The Departments table have a field ParentDepartment in which a hierarchical structure can be accomplish. So if affect we have an organogram with people sitting on different levels in the structure. So the long string returns a list of all people from a certain parent node down to the lowest leave. I.e. all people falling below department NorthernSection and subsidiaries.
So now the test is a security control to see that if the ResponsiblePerson are within a certain tree node, then access is granted.

 

[Galaxiom]

OK. It isn't your data structure, just the way you are retrieving the information.

Change the function to directly return the Responsible Person. Much tidier and more efficient.

 

[vbaInet]

The organisational structure is hierarchical but your tables are relational so you have people grouped into certain departments. If you want to find those in a certain department you do that in a query by joining the tables and writing appropriate criteria.