Gasman
Enthusiastic Amateur
- Local time
- Today, 08:41
- Joined
- Sep 21, 2011
- Messages
- 17,329
Hi all,
In my charity DB I have decided to extract the CMS ref (6 digits) from the client name, as I would now like to use it to automatically supply the balance for a client.
The DB is currently just used to generate emails to the case workers and my Divisional Secretary (boss).
I copy and paste the Client data from Quicken payee field and separate the client name from the CMS account number with a ':'
So I thought it should be easy enough to find the position of the ':' and get the next 6 digits.?
However I seem to have something in some of the fields that is throwing my logic out of the window. Record with ID 34 is an example.
I've looked at Trim() which does not do anything, so it is not a space. If I click in the field at the end and backspace it tales out a valid character and still keeps the 'space' at the end.
I have not counted these yet as the Len() functions thinks they are all 6 digits in length, but would like to be able to clean up the data automatically rather than manually in case this occurs again?
I've attached a DB with the Emails table, and the query I was trying to use to update the CMS field in the table (qryCMSCalc) and another query trying to get to the bottom of this (qryCheckCMS)
How can I clean up the data so that I get the last 6 digits of the Client field please.?
TIA
In my charity DB I have decided to extract the CMS ref (6 digits) from the client name, as I would now like to use it to automatically supply the balance for a client.
The DB is currently just used to generate emails to the case workers and my Divisional Secretary (boss).
I copy and paste the Client data from Quicken payee field and separate the client name from the CMS account number with a ':'
So I thought it should be easy enough to find the position of the ':' and get the next 6 digits.?
However I seem to have something in some of the fields that is throwing my logic out of the window. Record with ID 34 is an example.
I've looked at Trim() which does not do anything, so it is not a space. If I click in the field at the end and backspace it tales out a valid character and still keeps the 'space' at the end.
I have not counted these yet as the Len() functions thinks they are all 6 digits in length, but would like to be able to clean up the data automatically rather than manually in case this occurs again?
I've attached a DB with the Emails table, and the query I was trying to use to update the CMS field in the table (qryCMSCalc) and another query trying to get to the bottom of this (qryCheckCMS)
How can I clean up the data so that I get the last 6 digits of the Client field please.?
TIA
Last edited: