473,382 Members | 1,247 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

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

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
2 1410
PhilOfWalton
1,430 Expert 1GB
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

Phil
Nov 2 '17 #2
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

0
by: AJ Shankar | last post by:
Hi, In the C API, is there any way to execute a query, store all the results, and somehow find the maximum realized width for each of the fields before fetching each row? Otherwise there is no...
2
by: Mark | last post by:
I am trying to populate a field in a SQL table based on the values returned from using substring on a text field. Example: Field Name = RecNum Field Value = 024071023 The 7th and 8th...
7
by: Rick Caborn | last post by:
Does anyone know of a way to execute sql code from a dynamically built text field? Before beginning, let me state that I know this db architecture is built solely for frustration and I hope to...
5
by: Mike | last post by:
I'm using a script provided by e-mailanywhere, it's a little too big for me. There's 1 text field and 1 password field in a form. OnSubmit, I would like both fields to be validated to look for...
13
by: MLH | last post by:
I have a form with two controls: !! - combo box !! - text box A button on the form tries to run this SQL when clicked... INSERT INTO BodyMsgsSent (ToWhom, BodyText) SELECT DISTINCTROW !! AS...
5
by: sling blade | last post by:
Hi, I have an web app and I am using access 2002 and VB.net. I am using a data adapter to fill a dataset. I am retrieving product descriptions, some are long and i have used a field of memo...
4
by: steph | last post by:
Hello, I've got a text field in an access form (Microsoft Access 2002, SP3), that the user has to fill out. Now I don't want the user to leave this field (and this record) until he has filled...
3
by: JaRudzik | last post by:
Hello Try to add some blank text field and refresh window in IE it is ok but in firefox input hide_show works wrong , why ? Somebody can help me ? I'm not good in JS beacause I'm PHP coder ...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.