Eric,
Great solution! I'll put that in my bag of tricks!
Thanks
Hal
Hal McGee
Engineering Data Manager
Group Engineering - Process and Compliance
Seating Products Group
B/E Aerospace
Winston-Salem, NC
"Eric Douglas"
<[EMAIL PROTECTED]
tain.com> To
Sent by: <[email protected]>
AccessDevelopers@ cc
yahoogroups.com
Subject
RE: [AccessDevelopers] Re: Help
10/24/2005 12:30 with query
PM
Please respond to
AccessDevelopers@
yahoogroups.com
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
>
>
>
>
>
>
>
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
<*> 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/
