Clean data? (1 Viewer)

[Gasman]

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

 

[Ranman256]

ClientNum= mid(field,instr(field,":")+1,6)

Instr locates the ":"
Mid extracts the next 6 digits.

 

[Gasman]

Ranman256,

Thank you for the reply.

That is what I am using, but it is not producing the correct results on some of the data? You can see that code in the query qryCMSCalc

CMSRefCalc: Mid([emails.Client],InStr(1,[emails.Client],":")+1,6)

ClientNum= mid(field,instr(field,":")+1,6)

Instr locates the ":"
Mid extracts the next 6 digits.

 

[CJ_London]

would the square brackets have something to do with it? or is it a typo when you posted it to the thread?

I would have expected

[emails].[Client]

rather than [emails.Client]

 

[Gasman]

OK, found the problem. It is not at the end of the data as it appeared, but there is a Tab char in the data.

My fault when copying and pasting.

 

[The_Doc_Man]

If you have a systematic inclusion of tab characters, you might be able to use the REPLACE function, which lets you search for a substring in a longer string and replace one or more instances of that substring with a different substring - and the "different substring" could be an empty string.

 

[Gasman]

The_Doc_Man,

Thanks. Yes, I created a query to replace Tab with "" and then ran my qryCMSCalc query.
Values are now in the field, and I'm in the processing of DSumming the amounts using the new field.

What confused me was a backspace was not removing the previous character, but the one previous to that.

 

[Gasman]

would the square brackets have something to do with it? or is it a typo when you posted it to the thread?

I would have expected

[emails].[Client]

rather than [emails.Client]

Mistake on my part there, as I typed that into the SQL view,

Did not seem to complain though.

 

[Gasman]

Hi Sneuberg,

Your post disappeared. Did you delete it.?

I did use Replace and then the Mid functions. Just did it in two steps, rather than one.

I know how it occurred as I copy and paste from another system, sometimes Quicken, sometimes Excel, but you are right, it would be easy enough to remove any Tab after pasting and do it via code rather than taking care when copying and pasting.

 

[sneuberg]

Hi Sneuberg,

Your post disappeared. Did you delete it.?

Yes, After I submitted it I saw your post #7 and figured you didn't need it.

 

[Gasman]

Yes, After I submitted it I saw your post #7 and figured you didn't need it.

Well it prompted me to put code in to clean the pasted data, which I would not have thought of previously. I would have just made a note to paste clean in future. Now I have no need to, so a worthwhile post. Thank you.

Just tested on a new entry and works a treat.

Thank you all for the replies. This forum is a Godsend for people like me.