Padding out reference numbers

[Amanda]

Hi,
I think this is an Excel query, although I'll be linking
the table to an Access database. I've encountered a
problem regarding reference numbers (which is the primary
key in the Access database) not being in the same format.

My objective is to be able to consolidate a number of
records where the primary keys are not equal. For
example, data in the format:

Ref Company Date Amount
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500
12/101/002LT Smith & Co 15/11/03 £1000
12/101/002LC Smith & Co 15/11/03 £3000

to become:
Ref Company Date Amount
456/100/001 Bloggs & Co 12/12/03 £7500
12/101/002 Smith & Co 15/11/03 £4000

My problem is that I can't use the Left() function in
Access because of the inequalities. So I was wondering if
there's a way of identifying the length of the ref number
within Excel and then padding with zeros at the beginning
in order to use the Left() function?

BUT, there is also the issue that a reference can vary in
length from:
####/###/###??? to #######??# or ##/###/###?
where '#' indicates a number and '?' indicates a character.

Has anyone got any ideas on how this can be achieved (if
it is possible)? I hope so!

Amanda

 

[Izar Arcturus]

Hello Amanda,

Assuming that your longest Ref number is 15 characters
(including slashes) and assuming that just getting the
length to 15 by adding zeros is sufficient to solve your
problem. Also assuming that because you are using the Left
() function, that means that the zeros for padding should
be added to the right side of the Ref number not the left.

Here's an idea for your consideration:

Private Sub PadRefNumForAccess()
Ref = Range("B1").Value
Length = Len(Ref)
Select Case True
Case Length = 15
Ref = Ref
Case Length = 14
Ref = Ref & "0"
Case Length = 13
Ref = Ref & "00"
Case Length = 12
Ref = Ref & "000"
Case Length = 11
Ref = Ref & "0000"
Case Length = 10
Ref = Ref & "00000"
Case Length = 9
Ref = Ref & "000000"
Case Length = 8
Ref = Ref & "0000000"
Case Length = 7
Ref = Ref & "00000000"
Case Length = 6
Ref = Ref & "000000000"
Case Length = 5
Ref = Ref & "0000000000"
Case Length = 4
Ref = Ref & "00000000000"
Case Length = 3
Ref = Ref & "000000000000"
Case Length = 2
Ref = Ref & "0000000000000"
Case Length = 1
Ref = Ref & "00000000000000"
End Select
End Sub

-IA

 

[Amanda]

Thank you for your suggestion IA, I see what you're
saying, but if I'm appending zeros to the end of the
reference number how do I then consolidate the records?

I need to eliminate the letters currently at the end of
the reference number in order to consolidate the data.

Or am I missing something?

Amanda

 

[onedaywhen]

I offer some alternative code:

Option Explicit

Sub test()
Dim strTest As String
strTest = "four"
PadRefNumForAccess strTest, 15
Debug.Print strTest
End Sub

Private Function PadRefNumForAccess(ByRef Value As String, _
ByVal Length As Long, _
Optional ByVal PadCharacter As String = "0") _
As Boolean
Dim lngLenth As Long
lngLenth = Length - Len(Value)
If lngLenth > 0 Then
Value = Value & String(Length - Len(Value), "0")
PadRefNumForAccess = True
End If
End Function

BTW My notes say the rule for comparing strings in SQL is that the
short string is padded out with blanks. Maybe MS Access is different
due its ANSI non-compliance?

--

 

[Izar Arcturus]

Hello Amanda,

Sorry about not addressing the letters in the Refs in my
last post. The following code should cover that now also.

Private Sub PadForAccess(Ref)
Length = Len(Ref)
Do While IsNumeric(Right(Ref, Length - (Length - 1)))
= False
Ref = Left(Ref, Length - OneAtATime)
OneAtATime = OneAtATime + 1
Loop
Select Case True
Case Length = 15
Ref = Ref
Case Length = 14
Ref = Ref & "0"
Case Length = 13
Ref = Ref & "00"
Case Length = 12
Ref = Ref & "000"
Case Length = 11
Ref = Ref & "0000"
Case Length = 10
Ref = Ref & "00000"
Case Length = 9
Ref = Ref & "000000"
Case Length = 8
Ref = Ref & "0000000"
Case Length = 7
Ref = Ref & "00000000"
Case Length = 6
Ref = Ref & "000000000"
Case Length = 5
Ref = Ref & "0000000000"
Case Length = 4
Ref = Ref & "00000000000"
Case Length = 3
Ref = Ref & "000000000000"
Case Length = 2
Ref = Ref & "0000000000000"
Case Length = 1
Ref = Ref & "00000000000000"
End Select
End Sub

-IA

 

[Amanda]

Thank you both for your help.

Amanda

 

[Guest]

Thank you both for your help.

Amanda