Check the length of a field value in the table

[Derek]

Hi Guys

I have a table that stores ID,PolicyNumber,NewPolNo fields.It has millions of records. The field NewPolNo is empty. I need vba code that will check the following for each record:

1. If the length of the PolicyNumber is over 14 characters long then store that PolicyNumber value in NewPolNo field.

2. If the length of the PolicyNumber is less than 14 characters long thenagain store that PolicyNumber value in NewPolNo field.

3. But if the length of the policyNumber equals to 14 digits long then check the following:
a) If the last 7 digits are 0's of that PolicyNumber. If yes then store first 7 digits in NewPolNo field.
b) If the last 7 digits are not 0's then store the whole PolicyNumber value in NewPolNo field.

We need to check these conditions for each record of the table.

Can anyone please help me with the code?

Thanks

 

[TJPoorman]

The fastest way to accomplish this would be to create a simple UPDATE query in SQL.
Using IIF statements to get your final result, then updating the field to that result.

I could help write the SQL if you could provide some dummy information and desired results.