Update Query

[jwcclarke]

Hello
Hoping someone will be able to help me with this one,
I’m currently in the process of upgrading several Excel spread sheets into a Access Database (v 2010) there is some info that comes from a web portal and is paste into a sheet and then we use several formulas to pull out info from one of the cells that we require.
Example
Cell 1 : A Report was created by Sarah-Jane Smith (12345) for Mr John Smith (111963) on 30 Mar 2013
The formulas will then pull out
(12345)
Mr John Smith
31 Mar 2013
And to do it we use the below formula (this is for pulling out the customer’s name)
=LEFT(RIGHT(E7651,(LEN(E7651)-FIND(")",E7651,1)-5)),(FIND("(",RIGHT(E7651,(LEN(E7651)-FIND(")",E7651,1)-5)),1)-2))
My Question is this, Is it possible to create an update query that will do the Access version of the Excel formula above?

thanks for you help

 

[plog]

Yes. Here's a list of common functions in Access: http://www.techonthenet.com/access/functions/

The 'String Functions' category are the ones you want. You can also make your own functions in a module. Right(), Left() and Len() are all the same between Excel and Access. For Find() the equivalent is probably Instr().

 

[jwcclarke]

Hi Plog,

Thanks for that, i haven't been able to make it as neet and i do have three or four update queries running at the same time it make it work, but it work and when put in as a VBa button its does go though quite nicely, so thank you for pointing me at that site!