Why won't my Excel formular work in Access?

Confirm Colum equals value and if yes
insert contents from another column eg CS-CV-A-0040 less the A

=IF([Check]="Confined Space",CONCATENATE(LEFT([Confined Space Audit Number,6]),RIGHT([Confined Space Audit Number,4])),"")
This is the data base formular which I cant get to run
=IF(G2="Yes",CONCATENATE(LEFT(B2,6),RIGHT(B2,4)),"")
This is the formular which works in excel
What have I done wrong
James ClementsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Don ThomsonCommented:
I would try it with the proper syntax  

IIf («expr», «truepart», «falsepart»)

Which version of Access are your using?

Not the two "I" s


IIF(G2="Yes",LEFT(B2,6)&RIGHT(B2,4)),"")

Also Concatenate is not valid in Access (at least up to Vers 2007
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How are you running this in Access? Are you automating Excel from Access and using this, or are you trying to do this directly in Access?
0
PatHartmanCommented:
If you don't use embedded spaces and special characters which force you to encase the name in square brackets, you will have fewer opportunities for typos.

=IF([Check]="Confined Space",CONCATENATE(LEFT([Confined Space Audit Number],6),RIGHT([Confined Space Audit Number],4)),"")

You had the ending square brackets in the wrong place.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

James ClementsAuthor Commented:
Still unable to run formular in MS Access 2010
1 [Check] column is "Confine Space" if not leave blank
2 IF true
3 Generate number form column [Confine Space Audit Number} eg CS-CV-A-0040
4 Remove the A from [Confine Space Audit Number} eg CS-CV-0040

This what I am trying to achieve should CONCATENATE work
0
PatHartmanCommented:
Still unable to run formular in MS Access 2010
What does that mean?  Are you getting an error message?  What is it?  Are you getting the wrong result?  What are the ALL the inputs and what are you ending up with?  Paste the exact code that is failing.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James ClementsAuthor Commented:
I had a spelling mistake in 1 of my columns thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.