[AccessD] RE: Welcome to the "AccessD" mailing list

Could you conditionally set the format length based on the field length. 
Something like this;

iif(len([LoanNumber]=7,format(LoanNumber, "0000000"),
iif(len([LoanNumber]=8,format(LoanNumber, "00000000"),
iif(len([LoanNumber]=9,format(LoanNumber, "000000000"),
format(LoanNumber, "0000000000"))))

Gary Kjos
garykjos at hotmail.com

>From: "Jeanine Scott" <jscott at mchsi.com>
>Reply-To: accessd at databaseadvisors.com
>To: <accessd at databaseadvisors.com>
>Subject: RE: [AccessD] RE: Welcome to the "AccessD" mailing list
>Date: Mon, 17 Mar 2003 12:37:03 -0600
>
>I've tried these option and both worked great in terms of Excel seeing my
>data as text - however ran into these problems:
>
>Format option:
>  my loan number varies in length and I need to keep the original length.
>
>Character attached to field:
>  The quote shows in Excel in front of my loan number.
>
>I don't want to spend the amount of time it would take to implement Drew's
>suggestion - although I'm sure it's a very good suggestion!
>
>I'm using the same query for export to Excel I use as the base for my 
>report
>and my form data.
>
>Any other ideas?
>
>
>Jeanine Scott
>Sr. Systems Analyst
>Spindustry Systems
>515-669-2074
>jscott at spindustry.com
>
>CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is
>for the sole use of the intended recipient(s) and may contain confidential
>information. Any unauthorized review, use, disclosure, or distribution is
>prohibited. If you are not the intended recipient, please contact the 
>sender
>by reply e-mail and destroy all copies of the original message including 
>any
>attachments.
>
>-----Original Message-----
>From: accessd-admin at databaseadvisors.com
>[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Mike and Doris
>Manning
>Sent: Monday, March 17, 2003 11:14 AM
>To: accessd at databaseadvisors.com
>Subject: RE: [AccessD] RE: Welcome to the "AccessD" mailing list
>
>In the query you use to generate the report, enter that column's 
>information
>as Format(LoanNumber, "0000000") as the source.  When you export it to
>Excel, Excel should interpret this as a label.
>
>Doris Manning
>Database Administrator
>Hargrove Inc.
>www.hargroveinc.com
>
>
>-----Original Message-----
>From: accessd-admin at databaseadvisors.com
>[mailto:accessd-admin at databaseadvisors.com] On Behalf Of Jeanine Scott
>Sent: Monday, March 17, 2003 11:38 AM
>To: AccessD at databaseadvisors. com
>Subject: [AccessD] RE: Welcome to the "AccessD" mailing list
>
>
>
>Hello,
>I have a very basic question that I'm even embarrassed to ask!
>
>I have a text field that consists of numeric characters. Loan Number. I 
>need
>it to be a text field because I have leading zeros on the loan numbers. I 
>am
>exporting a report to Excel with this information. I need to do it in a
>report because I want to keep the grouping levels intact when the data is
>exported to Excel.
>
>Excel interprets my loan number field as numeric - dropping leading zeros. 
>I
>have played with different things like exporting as a .txt file (doesn't
>work), concatenating a space to the loan number (doesn't work) and
>concatenating  a "_" at the end of the field. This last option works 
>because
>Excel obviously sees the field as text. I really cannot keep this as a
>viable option.
>
>Is my only option to use automation and open Excel after I've done the
>export and format the loan number field? If so, how do I find the range and
>how does that work with groupings?
>
>This seems like such a simple thing and I can't believe I've never had to
>solve this particular issue before!
>
>Thanks so much in advance for help!
>
>Jeanine
>
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com


_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail