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_CTRNo3],"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
<*> 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/
