How do I insert leading characters in a field?

[Guest]

I want to pad the field with leading zeros so it is 8 characters in length.
If the user enters 123, I want it to default to 00000123.

 

[PC Datasheet]

Let the user enter it and let your form store it in your table as 123.
Whereever, you then use this field, use the expression:

Format([MyField],"00000000")

 

[Tom Ellison]

Dear Man:

What PCD wrote may be a good solution. However, if you want the column to
sort correctly, you would need to change the data. I would use
Right("0000000" & CurrentValue, 8)

Tom Ellison

 

[StopThisAdvertising]

"PC Datasheet" <[email protected]> schreef in bericht
--
To Steve:
No-one wants your advertising/job hunting here!
'Resource ???? 1175 users ???? Are you kidding ????
==>> You mean that 1175 users have been spammed by you ??

To the original poster:
Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

He is known here as a *shameless liar*, with *no ethics at all*.
If you provided a valid email address he might even spam you with an offer for payed help

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html
Over 925 !! users (rapidly increasing..) from the newsgroups have visited the website to read about this fake 'resource'

Arno R

 

[Joseph Meehan]

I want to pad the field with leading zeros so it is 8 characters in
length. If the user enters 123, I want it to default to 00000123.

Is the filed a number type or a text type.

If you want to sort or compute you need to use a number type. With a
number type it is usually easy to just use formatting to display the number
with the desired number of leading zeros.

 

[Jamie Collins]

if you want the column to
sort correctly, you would need to change the data. I would use
Right("0000000" & CurrentValue, 8)

To finish the job properly:

Make the column fixed width 8 characters i.e. CHAR(8) rather than
VARCHAR(8).

Use a validation rule to disallow non-numeric characters (make it
implementation-independent, i.e. handle both 'ANSI mode' and other
mode, by avoiding wildcard characters):

my_col LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Jamie.

--

 

[PC Datasheet]

If you need to sort, include both the 3-digit field value and the formatted
8-character value. Sort on the 3-digit field value. Or, you can use this
expression in your query and sort on the same field:
ValueIWant:Val(Format([MyField],"00000000"))

 

['69 Camaro]

Hi, Jamie.

To finish the job properly:

Make the column fixed width 8 characters i.e. CHAR(8) rather than
VARCHAR(8).

Use a validation rule to disallow non-numeric characters

Dang! You don't much like Access users and developers, do you? ;-)

You would rather the users type in all eight digits, including the leading
zeros? You would rather the developer store eight characters (or 16 if
Unicode compression is avoided), and create a validation rule to ensure that
only digits are stored in the column, instead of storing a four byte Long
that needs no such Validation Rule or leading zeros? All this so that the
developer doesn't have to write a simple formatting function that displays
the digits with leading zeros? (There's no sorting problem with the digits
not lining up properly, so one doesn't have to worry about this column's
sort order not being correct.)

my_col LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

I must have the buggy version of Access because when it's in ANSI mode this
syntax automatically gets rewritten as:

"my_col" ALike '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

.. . . which obviously doesn't work. I have Access 2003 SP-1 installed on
this computer.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

if you want the column to
sort correctly, you would need to change the data. I would use
Right("0000000" & CurrentValue, 8)

To finish the job properly:

Make the column fixed width 8 characters i.e. CHAR(8) rather than
VARCHAR(8).

Use a validation rule to disallow non-numeric characters (make it
implementation-independent, i.e. handle both 'ANSI mode' and other
mode, by avoiding wildcard characters):

my_col LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Jamie.

 

[Jamie Collins]

'69 Camaro wrote:
Hi Gunny,

You would rather the developer store eight characters...
instead of storing a four byte Long

Yes. It's a basic data modelling approach: if the data in the reality
is fixed width text then model it as fixed width text. I'm not against
exploiting the software to perform integer operations on text data
which contain only numeric characters, in which case store it as fixed
width text as cast it as integer at the developer's convenience.

Dang! You don't much like Access users and developers, do you? ;-)
You would rather the developer store eight characters (or 16 if
Unicode compression is avoided), and create a validation rule to ensure that
only digits are stored in the column, instead of storing a four byte Long
that needs no such Validation Rule or leading zeros?

If storage has become such an issue that you will consider modelling
fixed width text as integer to save disk space, it is probably time to
port to a platform more capable than Access/Jet.

All this so that the
developer doesn't have to write a simple formatting function that displays
the digits with leading zeros?

That's not my point. I always say that the developer(s) should write
both 'server side' procedures and 'client side' as appropriate e.g.
more appropriate to do text formatting on the 'client side'. As a
part-time developer myself, I'm not afraid of putting in the extra
effort

You would rather the users type in all eight digits, including the leading
zeros?

No. I would think it would be better to handle such formatting (e.g.
padding with zeros) on the 'client side', especially in Access/Jet
where you only get to perform one SQL command per procedure. However, I
do agree that the 'server side' procs should handle data without the
leading zeros. For example:

CREATE TABLE Accounts (
account_nbr CHAR(8) NOT NULL PRIMARY KEY,
CONSTRAINT account_nbr__pattern
CHECK (account_nbr LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)
;
CREATE PROCEDURE AccountAdd (
arg_account_nbr CHAR(8)
)
AS
INSERT INTO Accounts (account_nbr)
VALUES (RIGHT$('00000000' & arg_account_nbr, 8))
;
EXECUTE AccountAdd '5'
;
EXECUTE ACCOUNTADD 55
;
EXECUTE AccountAdd 'Will fail'
;

That last one generates an error which can be trapped on the 'client
side'. Here's an alternative approach where the error is avoided and
only the number of rows affected need be tested:

DROP PROCEDURE AccountAdd
;
CREATE PROCEDURE AccountAdd (
arg_account_nbr CHAR(8)
)
AS
INSERT INTO Accounts (account_nbr)
SELECT DISTINCT RIGHT$('00000000' & arg_account_nbr, 8) AS account_nbr
FROM Accounts
WHERE RIGHT$('00000000' & arg_account_nbr, 8)
LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
;

I must have the buggy version of Access

OK, here's a demo of my SQL code which doesn't use Access:

Sub testGunny()
Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb;"

With .ActiveConnection

.Execute _
"CREATE TABLE Accounts ( account_nbr CHAR(8)" & _
" NOT NULL PRIMARY KEY, CONSTRAINT" & _
" account_nbr__pattern CHECK (account_nbr LIKE" & _
" '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')" & _
" );"

.Execute _
"CREATE PROCEDURE AccountAdd ( arg_account_nbr" & _
" CHAR(8) ) AS INSERT INTO Accounts (account_nbr)" & _
" VALUES (RIGHT$('00000000' & arg_account_nbr," & _
" 8)); "

.Execute _
"EXECUTE AccountAdd '5';"

.Execute _
"EXECUTE AccountAdd 55;"

Dim lRows As Long
On Error Resume Next
.Execute _
"EXECUTE AccountAdd 'Will fail';", lRows
MsgBox _
"Error: " & _
IIf(Len(Err.Description) = 0, "(none)", _
Err.Description) & vbCr & vbCr & _
"Rows affected: " & CStr(lRows)
On Error GoTo 0

.Execute _
"DROP PROCEDURE AccountAdd;"

.Execute _
"CREATE PROCEDURE AccountAdd ( arg_account_nbr" & _
" CHAR(8) ) AS INSERT INTO Accounts (account_nbr)" & _
" SELECT DISTINCT RIGHT$('00000000' & arg_account_nbr," & _
" 8) AS account_nbr FROM Accounts WHERE RIGHT$('00000000'" & _
" & arg_account_nbr, 8) LIKE
'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';"

lRows = 0
On Error Resume Next
.Execute _
"EXECUTE AccountAdd 'Will fail';", lRows
MsgBox _
"Error: " & _
IIf(Len(Err.Description) = 0, "(none)", _
Err.Description) & vbCr & vbCr & _
"Rows affected: " & CStr(lRows)
On Error GoTo 0

End With
End With
End Sub

Jamie.

--