IIf query decimal removal

Question

Trying to attempt the following in MS Access.

Convert data in one field to an 18 digit number starting with 01 in another field.

There are also some conditions that have to be met:

• the first dash should become double zeros
• the second dash should be removed
• the third and fourth dash should be a single zero
• the decimal must also be replaced with a zero

My query works fine until the decimal is the 15th character in the data.

Here is the query:

SELECT MasterVacant.ParcelIdNumber,
    "01" + Mid([ParcelIdNumber],1,2) + "00" + Mid([ParcelIdNumber],4,2) + Mid([ParcelIdNumber],7,1)
        + IIf(Mid([ParcelIDNumber],11,1) = "", "0"+Mid([ParcelIDNumber],9,2), Mid([ParcelIDNumber],9,3))
        + IIf(Mid([ParcelIDNumber],14,1) = ".", "0"+Mid([ParcelIDNumber],12,2), Mid([ParcelIDNumber],12,3))
        + Mid([ParcelIDNumber],15,3) AS ParcelNumber
FROM MasterVacant;

Here is a start and finish example...

'12-06-1-00-50.000-RR'  should become '011200061000050000'
'12-06-3-07-09.000-RR'  should become '011200063007009000'
'13-35-1-01-129.000-RR' should become '011300035100112900'

However, instead of getting `0113000351001129000' I get '013000351001129.00'.

The issue is how do I remove the decimal when the decimal is the 15th character like in the third set of example?

I receive the data as a single column. Some of it is below....

1. 13-35-1-07-29.000-RR 
2. 13-35-1-01-112.000-RR (Removing the decimal when the data is like this is the issue)
3. 13-35-4-01-01.000-RR
4. 13-35-4-02-04.000-RR
5. 13-35-1-13-17.000-RR

The output for the above data should be

1. 011300351007029000
2. 011300351001112000
3. 011300354001001000
4. 011300354002004000
5. 011300351013017000

Answer 1

Use a custom function:

Public Function Make18(ByVal Value As String) As String

    Const Head  As String = "01"
    Const Tail  As String = "000"
    Const Lead  As String = "00"

    Dim Parts   As Variant
    Dim Part    As Integer
    Dim Result  As String

    Parts = Split(Split(Value, ".")(0), "-")

    For Part = LBound(Parts) To UBound(Parts)
        Select Case Part
            Case 0
                Parts(Part) = Head & Parts(Part)
            Case 1
                Parts(Part) = Lead & Parts(Part)
            Case 3, 4
                Parts(Part) = Right(Lead & Parts(Part), 3)
        End Select
    Next

    Result = Join(Parts, "") & Tail

    Make18 = Result

End Function

and your query becomes:

SELECT 
    MasterVacant.ParcelIdNumber,
    Make18([ParcelIdNumber]) AS ParcelNumber
FROM 
    MasterVacant;

Answer 2

I am assuming you meant the opposite where:

12-06-1-00-50.000-RR should become 011200061000050000 
12-06-3-07-09.000-RR should become 011200063007009000 
13-35-1-01-129.000-RR should become 0113000351001129.00

I would recommend the REPLACE() in MSACCESS to strip the dashes out. Once you have the dashes out you can MID()

Unfortunately your attempted code does something different with the 3rd row because 3 zeros are being put in when there should be only two in my opinion.

Try in a text box:

=Replace("13-35-1-01-129.000-RR","-","")

will return 1335101129.000RR and see if that assists you in making your code.

Maybe go one step further and put it in a function.