[SOLVED] IIf query decimal removal

IIf query decimal removal

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

Solution

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;