INNER JOIN and RIGHT problems

[PRMiller]

Good afternoon,

My company recently added an additional digit to the beginning of each employee's unique identifier. We have updated that index in our "master" table (tblCSAInfo.CSAExtension), but have not added the new number to the beginning of that same identifier in our various data tables.

The following query worked just fine prior to adding that sixth digit. I have updated all of my VBA code to look for "Right(CSAExtension, 5)", and this works successfully. I am looking to do the same in queries. I cannot, however, seem to get it to work. In the SQL listed below, I am looking for a way to search on the right 5 of tblCSAExtension. I attempted this:

INNER JOIN tblDataSkillProdMTDSorted ON (Right(tblCSAInfo.CSAExtension, 5) = tblDataSkillProdMTDSorted.AgentID

but received a syntax error. Any suggestions? Following is the existing SQL:




SELECT tblCSAInfo.CSAName, tblDataSkillProdMTDSorted.Bucket, tblDataSkillProdMTDSorted.ACDCalls, tblDataSkillProdMTDSorted.AHT, tblCSAInfo.CSAActiveState

FROM tblCSAInfo
INNER JOIN tblDataSkillProdMTDSorted ON tblCSAInfo.CSAExtension = tblDataSkillProdMTDSorted.AgentID

GROUP BY tblCSAInfo.CSAName, tblDataSkillProdMTDSorted.Bucket, tblDataSkillProdMTDSorted.ACDCalls, tblDataSkillProdMTDSorted.AHT, tblCSAInfo.CSAActiveState, tblCSAInfo.CSASupervisor

HAVING (((tblDataSkillProdMTDSorted.Bucket)=[Forms]![frmBucketMTDParameter]![txtBucketParameter]) AND ((tblCSAInfo.CSAActiveState)=-1) AND ((tblCSAInfo.CSASupervisor)=[Forms]![frmReportsProd]![txtSupName]))

ORDER BY tblCSAInfo.CSAName;


Thanks!
Paul

 

[PHV]

Have you tried this ?
ON Right(tblCSAInfo.CSAExtension, 5) = tblDataSkillProdMTDSorted.AgentID
If AgentID is defined as numeric in tblDataSkillProdMTDSorted, you may consider this to avoid type mismatch error:
ON CLng(Right(tblCSAInfo.CSAExtension, 5)) = tblDataSkillProdMTDSorted.AgentID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PRMiller]

Hi PHV,

Thanks for the reply. I'll give that a shot, because it definitely beats the workaround I put in place. For now, I built a query that I named "qryCSAInfo", queried the entire contents of my table by the same name, and inserted a new field "Right(CSAExtension,5) AS CSAExtension2" and modified all of my other queries to search off of that new field and the new table.

I'll let you know how your solution works once I try it.

Thanks!
Paul

 

[SonOfEmidec1100]

You have an open(left) bracket with no matching close, or is this just a typo.

INNER JOIN tblDataSkillProdMTDSorted ON [red]([/red]Right(tblCSAInfo.CSAExtension, 5) = tblDataSkillProdMTDSorted.AgentID