Change Characters in a String by Adding Zeros

[Phil F]

I have a text field that will be the primary key with data that needs to be
in the format
00000-00000 (example: 00010-00035). However, the data has been entered
without the appropriate zeros (example: 10-35). Is there a way to convert
the data to the appropriate format?

Phil F

 

[Douglas J. Steele]

Is it always supposed to be 5 digits in front and 5 digits after the dash?
Assuming you're using Access 2000 or newer, try:

Function FixKey(IncomingKey As String) As String

Dim varParts As Variant

varParts = Split(IncomingKey, "-")
If UBound(varParts) = 1 Then
FixKey = Format(CLng(varParts(0)), "00000") & _
"-" & Format(CLng(varParts(1)), "00000")
Else
FixKey = IncomingKey
End If

End Function

 

[Phil F]

Total of 5 digits on each side of the dash (examples: 00555-00559 or
08891-08895 or 10005-10045).
This field represents a range from start to end separated by the dash. I am
categorizing a range of objects named by these numbers rather than the
individual objects. The range will not likely exceed 99999 but it is
possible.

I do not know code so any tips on where to place the appropriate code would
be helpful.

Thanks Doug

Phil F

 

[Douglas J. Steele]

I would strongly suggest storing the values as two separate fields, then.
That will let you join that table to other tables, returning all of the
impacted rows.

I gave you a function: you'd copy it into a new module (not a class module,
nor the code associate with a form or report). Make sure you do not name the
module FixKey: modules cannot have the same name as functions or subs. You'd
then use the function the same way you'd use any of the built-in functions.

 

[Phil F]

Doug,

Your function works perfectly. Thank you.

I don't understand why I should store the beginning of the range and the end
separately. The beginning of the range and the end would be in the same
record anyway. I am essentially storing the range as one record in a table
rather than one record for each member of the range.

Thanks for all of you help.

Phil F

 

[Douglas J. Steele]

I may have misinterpretted what you're trying to do, but I read it as
00001-00005 indicated that that record corresponded to the 5 records 00001
to 00005 in another table. If that's the case, then having RangeStart and
RangeEnd as separate fields in TableA will let you extract the 5 related
records from TableB if you need them:

SELECT TableA.FIeld1, TableA.Field2,
TableB.Field1, TableB.Field2, TableB.Field3
FROM TableA INNER JOIN TableB
ON TableB.ID BETWEEN TableA.RangeStart AND TableB.RangeEnd