Eric, Thanks for the help but this expression works fine. AMCTRNo3_DS: IIf(IsNull([AM_CTRNo3]),'000000000',[AM_CTRNo3])
Thanks A.T. --- In [email protected], "Eric Douglas" <[EMAIL PROTECTED]> wrote: > > The same place you would enter your original expression - > > AMCTRNo3: Right("000000000" & [AM_CTRNo3], 9) > > And like Hal said, the destination field would need to be text. > > Eric > > > -----Original Message----- > > From: [email protected] > > [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 > > Sent: Monday, October 24, 2005 8:56 AM > > To: [email protected] > > Subject: [AccessDevelopers] Re: Help with query > > > > Eric, > > Where did you enter this expression? > > > > A.T. > > > > > > --- In [email protected], "Eric Douglas" > > <[EMAIL PROTECTED]> wrote: > > > > > > 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 > > --------------------~--> Most low income households are not > > online. Help bridge the digital divide today! > > http://us.click.yahoo.com/cd_AJB/QnQLAA/TtwFAA/q7folB/TM > > -------------------------------------------------------------- > > ------~-> > > > > > > Please zip all files prior to uploading to Files section. > > Yahoo! Groups Links > > > > > > > > > > > > > > > ------------------------ 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 <*> 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/
