Using Right() and if the results have the first digit as a 0 change to a 1

[breakingme10]

We have fuel cards and for PIN numbers I set them as the last four digits of the DL number. But the PIN cannot begin with a zero, so I just changed all the ones that began with 0 to a 1.

Now how can I get a query to calculate a field that will do the same in my database.

EXAMPLE::

DL Number	Last 4 Digits via Right([DLN], 4)	PIN Number ACTUAL Digits
J083573698	3698	3698
K080850789	0789	1789

The second column I know how to get with the Right() function, but how can I get it to give me the third column.
I know I'm going to have to do it in VBA but I'm still so timid with VBA.

[ssanfu]

How are you generating the PIN now? VBA? Formula?
And are you using a form?

Please show the formula or code.

[John_G]

Hi -

A suitable query expression might be:

iif(mid([DL],7,1)="0","1",mid([DL],7,1)) & mid([DL],8)

This assumes all the DL Numbers are 10 characters long as in your example; it will be a bit more complicated if that is not the case.

John

[orange]

breakingme10,

Sometimes it's a lot simpler to just put 1 fact in 1 field.(KISS method)
1 field for DL Number and 1 field for PIN.

If no PINs can start with 0, how did you end up with a DL like K080850789 in the first place?

What happens to DL Numbers if you have to change PIN for any reason?

[breakingme10]

OK I thought that was a universal abbreviation, apparently maybe its only around Oklahoma that DL stands for "Driver's License" so the DLNumber field is their driver's license number, which cannot change! I originally just used an excel spreadsheet and manually typed them in to send the PIN numbers to the fuel card company. I was just thinking since the data was derived from another field in the database that I should use VBA or a calculated query field to generate the PIN in the database.
As I said in the first post, I was trying to use the

PIN: Right([DLNumber], 4)

calculation in a query.
But if the first digit of those 4 is a 0 I need to substitute a 1 in it's place.

[JoeM]

But if the first digit of those 4 is a 0 I need to substitute a 1 in it's place.

Did John_G's answer solve your problem?
It works for me when I test it out on your data.

[breakingme10]

YES it worked great! All OK DL's are 1 letter + 9 digits!