Padding with zeros with a dash

You either have to create a new virtual fiewld , or else you have to run an update query against the table to change the stored values..

newvalue: "0" & left (fieldname, 6) & "00" & right(fieldname, 4)

use this function (alt+F11, copy paste)

Function myFormat(s As String)
  Dim n1 As String, n2 As String
  Dim ns() As String
  ns = Split(s, "-")
  n1 = "000000" & ns(0)
  n2 = "000000" & ns(1)
  myFormat = Right(n1, 6) & "-" & Right(n2, 6)
End Function

SELECT myFormat("1234-5678")
001234-005678

Select allOpen in new window

Select query:

SELECT Table1.FIELD0 AS FIELD0Before, Format(Replace([FIELD0],"-",""),"000000-000000") AS FIELD0After
FROM Table1
WHERE (((Nz([FIELD0],"")>"")=True));

Update Query - make a BACKUP first:

UPDATE Table1 SET Table1.FIELD0 = Format(Replace([FIELD0],"-",""),"000000-000000")
WHERE (((Nz([FIELD0],"")>"")=True));

mx

MX,

If you try that on the sample input 12345-1234, the result you get back is 000123-451234.

:)

Patrick

yep ... missed that