Expresion

A

Alexandra504

I have a field that contains either 5 or 6 digits. I need an expression for a
query that looks at the number of digits in the field. If it has 5 or 6
digits it is fine and should leave the same number, but if it has 4 digits I
would like a 0 to be placed in front of the 4 digits.

Example:

Field
126585
16985
1236
569825
2695

Should return

Field
126585
16985
01236
569825
02695

Is there an expresion that does this?
 
K

KARL DEWEY

Create a select query with field like this --
Len([YourFieldName])
Use criteria <5
Run the query to verify proper selection of records.
Change to update query and update [YourFieldName] like this --
Right("000" & [YourFieldName], 5)
This will also add leading zeros to those that may have only three digits.
 
J

John Spencer

What type of field is this? Text or number
Do you want to do this permanently or just in a select query or somewhere
else?

Two possible expressions that you could use

IIF(Len([TheField] &"") = 4, Format([TheField,"00000"),[TheField])

or

IIF(Len([TheField] &"") = 4, "0" & [TheField],[TheField])



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top