List Comparison?

[jchastai]

I have a hand-me-down Access database which I am trying to get some data out of.

I have a table called issues with a field called owner. The owner field may contain a single name, or it may contain a comma-seperated list of names.

I have a table called users with a field called name. I am needing to join the two tables up based on the first or only name in the issues.owner field matched to the users.name field.

Whenever I try using inStr in the WHERE clause, access throws an invalid procedure error. Anybody got any suggestions?

This does not seem to work with Access and even if it did, it would only work for records with a list of users in the issues table, not just a sinlge enty ....

SELECT *
FROM issues, users
WHERE left(issues.owner, inStr(issues.owner, ',')-1) = users.name


Thanks
-- Jeff

 

[jebry]

Hi Jeff!

Put the InStr code in a public function:

Public Function basTestName(strNameField As String) As String

basTestName = left(strNameField, inStr(strNameField, ',')-1)

End Function

Then in your query use this field:

FirstName: basTestName(issues.owner)

Criteria = users.name

hth
Jeff Bridgham
bridgham@purdue.edu

 

[jchastai]

Okay ...

I am trying to run this magic query from a ColdFusion script and therefore, I am not sure how to setup or run an Access function.

A little more details please?

Thanks

 

[jebry]

Hi!

In Access you need to click on the Modules tab and make a new module. You will put the function above in the module and you can then run it from any form, report or query in Access. I have no idea if you can run it from ColdFusion since I have never tried it but as long as the actual query is in the Db, I see no reason why it wouldn't work.

hth


Jeff Bridgham
bridgham@purdue.edu