I'm not sure but try puting the "left(issues.owner, inStr(issues.owner, ',')-1)" up in the select as "left(issues.owner, inStr(issues.owner, ',')-1) as LastName". That should get rid of the error.
-----Original Message----- From: Jeff Chastain [mailto:[EMAIL PROTECTED]] Sent: Friday, January 17, 2003 11:19 AM To: CF-Talk Subject: RE: Access SQL Question - Round 2 No, that is along the lines of what I have been trying without any luck. Okay, I have the following tables and data: table: issues (id, owner) 1 Doe 2 Klein, Doe 3 James 4 Doe, James table: owners (name, email) Doe [EMAIL PROTECTED] James [EMAIL PROTECTED] Klein [EMAIL PROTECTED] The results I need to get back are ... (issue.id, owners.name, owners.email) 1 Doe [EMAIL PROTECTED] 2 Klein [EMAIL PROTECTED] 3 James [EMAIL PROTECTED] 4 Doe [EMAIL PROTECTED] Where the name and email come from the first (or only) name in the issues table. I would not think that this would be that hard, but for some reason it appears to be. Thanks -- Jeff ---------- Original Message ---------------------------------- From: "webguy" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Date: Fri, 17 Jan 2003 15:56:19 -0000 >No access guru, (anymore:-/) but try > >SELECT Firstinlist as (or "=" ???)left(issues.owner, inStr(issues.owner, >',')-1) >FROM issues, users >WHERE Firstinlist = users.name > >Something like that... > >WG > >> -----Original Message----- >> From: Jeff Chastain [mailto:[EMAIL PROTECTED]] >> Sent: 17 January 2003 15:44 >> To: CF-Talk >> Subject: WOT: Access SQL Question - Round 2 >> >> >> Okay, I am still having problems with this ... so here is an attempt to >> simplify the problem. >> >> 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-separated 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 >> single entry .... >> >> SELECT * >> FROM issues, users >> WHERE left(issues.owner, inStr(issues.owner, ',')-1) = users.name >> >> >> Thanks >> -- Jeff >> >> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
