How format data to add zeros to alpha numeric data in query

's Computer Specifications

Need to add zeros to data like MCRD-472 to show MCRD-00472 and like NBC-2 to
show NBC-00002 and like NBC-M14 to show NBC-00M14. To Query text box.

's Computer Specifications

If YourField always has a dash which you want to follow with filler zeros
then this --
Left([YourField], InStr([YourField], "-")-1) & Right("00000" &
Mid([YourField], InStr([YourField], "-")+1)

--
Build a little, test a little.

"CS" wrote:

> Need to add zeros to data like MCRD-472 to show MCRD-00472 and like NBC-2 to
> show NBC-00002 and like NBC-M14 to show NBC-00M14. To Query text box.

's Computer Specifications

I left out an argument in the last --
, "-")+1), 5)

--
Build a little, test a little.

"CS" wrote:

> Need to add zeros to data like MCRD-472 to show MCRD-00472 and like NBC-2 to
> show NBC-00002 and like NBC-M14 to show NBC-00M14. To Query text box.

's Computer Specifications

One mo time --
Left([YourField], InStr([YourField], "-")-1) & Right("00000" &
Mid([YourField], InStr([YourField], "-")+1, 5)

--
Build a little, test a little.

"KARL DEWEY" wrote:

> I left out an argument in the last --
> , "-")+1), 5)
>
> --
> Build a little, test a little.
>
>
> "CS" wrote:
>
> > Need to add zeros to data like MCRD-472 to show MCRD-00472 and like NBC-2 to
> > show NBC-00002 and like NBC-M14 to show NBC-00M14. To Query text box.

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Ignore Leading Zeros in Mixed Alpha-Numeric Text Fields Select Query	excelCPA	Microsoft Access Queries	5	14th Oct 2009 08:54 PM
How to sort numeric/alpha name by alpha then number in WindowsExplorer	Mel	Windows XP General	4	6th Apr 2009 07:54 PM
The colums changed from alpha to numeric how do you make it alpha	=?Utf-8?B?d29ybGRtYWRl?=	Microsoft Excel Misc	2	26th May 2005 03:44 PM
Display only the alpha characters in a field w/both alpha&numeric.	=?Utf-8?B?QXJibyBN?=	Microsoft Access Queries	1	16th Dec 2004 09:19 AM
Re: Extracting numeric data from an alpha numeric field	Pattie	Microsoft Access	3	17th Sep 2004 08:21 AM