Jeanine Scott
jscott at mchsi.com
Tue Mar 18 11:11:01 CST 2003
This worked perfectly!! I totally forgot about that function. Thanks! Jeanine -----Original Message----- From: accessd-admin at databaseadvisors.com [mailto:accessd-admin at databaseadvisors.com]On Behalf Of Jim DeMarco Sent: Monday, March 17, 2003 2:59 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] RE: Welcome to the "AccessD" mailing list Would this work for you? Format(LoanNumber, String(Len([LoanNumber]),"0") The String function takes two arguments, the number of times to repeat and the character to repeat. HTH, Jim DeMarco Director of Product Development HealthSource/Hudson Health Plan -----Original Message----- From: Gary Kjos [mailto:garykjos at hotmail.com] Sent: Monday, March 17, 2003 3:26 PM To: accessd at databaseadvisors.com Subject: RE: [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 _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com **************************************************************************** ******* "This electronic message is intended to be for the use only of the named recipient, and may contain information from HealthSource/Hudson Health Plan (HS/HHP) that is confidential or privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited. If you have received this message in error or are not the named recipient, please notify us immediately, either by contacting the sender at the electronic mail address noted above or calling HS/HHP at (914) 631-1611. If you are not the intended recipient, please do not forward this email to anyone, and delete and destroy all copies of this message. Thank You". **************************************************************************** ******* _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com