By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,265 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

ms access - how to extricate a substring from a variable length text field

P: 1
We get a hotch-potch of info in a long, variable length, text field, eg
"FroCash: Transfer Confirmation. USD 103.00 from SHAN CHAD Approval Code: PP17106.2251.C027342. New wallet balance: USD 105.61."
I need the balance as a standard number ie 105.61 for this record.
I have thousands to process.
Have tried combinations of Left, Right and Instr (which refuses to recognize " USD " as a substring)
Am using MS Access queries and SQL to append/update the masterfile.
I do not use VB.
Thanking you in anticipation.
Nov 2 '17 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,430
Instr should find USD, but in your example it occurs twice.

You will need to work out some sort of rule to determine what you are looking for.

So in the example you gave why did you hit on USD 105.61 rather than USD 103.00.

I am afraid you will need some VBA almost certainly

Nov 2 '17 #2

Expert Mod 15k+
P: 31,307
Try turning off any intelligence you have and processing your computer as a computer would. You'll find yourself lost as it doesn't explain clearly how to recognise the part of the string you want. Remember, we can only help you if you ask your question fully and clearly.
Nov 3 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.