One thing I've done to force the length of a string and add leading
zeros is:
Right("000000000" & [AM_CTRNo3], 9)
Eric
> -----Original Message-----
> From: [email protected]
> [mailto:[EMAIL PROTECTED] On Behalf Of
> [EMAIL PROTECTED]
> Sent: Monday, October 24, 2005 7:27 AM
> To: [email protected]
> Subject: Re: [AccessDevelopers] Help with query
>
> Two thoughts.
>
> First, the field being created should be text not numeric. If
> you are using a make table query, use the CStr function to
> make Access create this field as a text field. In this
> situation, I normally pre-create the table so I can control
> field definitions and the use a delete query to clear the
> table and an append query to populate the table.
>
> Second, concatenate the necessary 0's to the string by using
> an Iif statement and the Choose function such as
>
> AMCTRNO: Iif([AM_CTRNo3] Is Null, "000000000",
> CHOOSE(Len([AM_CTRNo3]),"00000000"&[AM_CTRNo3],"0000000"&[AM_C
> TRNo3],"000000"&[AM_CTRNo3],"00000"&[AM_CTRNo3]
>
> You will have to finish the above sequence but you should be
> able to see where this is going.
>
> Good Luck
> Hal
>
> Hal McGee
> Engineering Data Manager
> Group Engineering - Process and Compliance Seating Products
> Group B/E Aerospace Winston-Salem, NC
>
>
>
>
> "torres0157"
>
> <[EMAIL PROTECTED]
>
> >
> To
> Sent by:
> [email protected]
> AccessDevelopers@
> cc
> yahoogroups.com
>
>
> Subject
> [AccessDevelopers]
> Help with query
> 10/24/2005 10:04
>
> AM
>
>
>
>
>
> Please respond to
>
> AccessDevelopers@
>
> yahoogroups.com
>
>
>
>
>
>
>
>
>
> Good Morning,
> I am creating a table that will be exported and a field that
> is input into the query needs to be formated to 9 zeroes if
> the field contains zeroes or spaces. I used the following
> format expression but only one zero is placed in the
> destination field. if the input field in the table
> (AM_CTRNo3) does not contain valid data.
> AMCTRNo3: IIf(IsNull([AM_CTRNo3]),0,[AM_CTRNo3])
> can someone help
>
> Thanks
> A.T.
>
>
>
>
>
>
> Please zip all files prior to uploading to Files section.
>
>
> YAHOO! GROUPS LINKS
>
> Visit your group "AccessDevelopers" on the web.
>
> To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
>
> Your use of Yahoo! Groups is subject to the Yahoo!
> Terms of Service.
>
>
>
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~--> Fair play? Video games influencing
> politics. Click and talk back!
> http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/q7folB/TM
> --------------------------------------------------------------
> ------~->
>
>
> Please zip all files prior to uploading to Files section.
> Yahoo! Groups Links
>
>
>
>
>
>
>
------------------------ Yahoo! Groups Sponsor --------------------~-->
Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life.
http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/q7folB/TM
--------------------------------------------------------------------~->
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
