Using Left, Mid and Right function in a join within an SQL update

[Robotron]

I have 2 tables which I would like to join. However, one table has a 7 character field which when parsed, can be joined to 3 fields in the other table. I would prefer not to actually parse the field out into separate fields in the table design if possible.

I am thinking something like this:

UPDATE tblBrioData INNER JOIN tblMaddData ON (tblBrioData.Region = Left(tblMaddData.JRNLID,2)) AND (tblBrioData.District = Mid(tblMaddData.JRNLID,3,2) SET tblBrioData.MADDJRNLID = [tblMaddData]![JRNLID];

When I try this, I get "Syntax Error in Join Expression". I created just a simple select statement using the same join and I get results.

Thanks in advance for the help.

 

[SkipVought]

Post the Select SQL that works.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Robotron]

I have decided to parse out the field into 3 separate fields and join that way. Access seems to handle it easier.

Phil Edwards

 

[SkipVought]

Your example had only TWO where expressions.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Robotron]

It would help if I were more specific. In Access, when you try to do an update like this, you get a cartesian product type of effect, I fixed the update query. It was missing a close parenthesis. When I ran it, it said it would update 360,000 rows. There are only 3600 in the table. That's what I meant when I said Access doesn't handle it easily.

Phil Edwards

 

[SkipVought]

Maybe that's because you only joined on 2 out of 3 of the fields that you stated you previously had joined on.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244