Problems with Data

[knightwest]

Hi,

I am new to this forum, and hope I can get the help with Access here that I need.

The problem I am facing is some of the data I am using. Let me explain:

I work with for an insurance company. When claims are made, customer support enter the following into their system:
AA1111111111AA (where A could be any letter and 1 could be any number) the claimants name and other Info.

In 99% of the time the claim number is the first thing in the cell, so i have used a function that takes the left of the data up to the first space and transposes this into a new column. This works fine.

The 1% exceptions to this cause me problem:
1.) In a few cases the policy number is the last thing entered into the field. I thought in those cases I could just take the same funtion as above and change it to 'right', rather than 'left'. But this would have to be in yet another column, and how can Access recognise to take these from the right rather than from the left?
2) Some have a space before them as in ' AA11111AA'. Using the formula I have used above, this will transpose a blank.
3.)Some have no space Ie 'BonusAA111111AA'. How can i isolate the claim number from that.

I would be very happy if anyone could help me

Knightwest

 

[FoFa]

Trim will remove leading or trailing spaces. When you call your function, use TRIM(ClaimNumber) how ever you are passint Claim Number to your function. That should solve that problem.
Not sure what to tell you about the rest. You have to have some unique something to allow your function to detect where to break. It might be something like identifying those in error and fixing the data issues.

 

[amerifax]

First off I am the idea guy at my company. If I need something done my staff can usually produce it when it comes to programming. But let me throw this at you. I'm not exactly sure what you are saying as far as your data. But when you typed in the word BONUS I saw a difference between that and the "AA's", "Bo" & "AA" it seems to me logically a program should be able to search by two wildcard characters looking for case, then you can determine where your claim number starts and stops. Whether or not Access can do this, I am as interested as you are.

Bob