Expresion

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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
..
 
Back
Top