John B.
john at winhaven.net
Mon Oct 13 12:19:14 CDT 2003
Yes, but then you have to find the longest string first and feed it to the format function and you have the issue of leading zeros or (whatever), etc. I realize there are many reasons to type a field one way or the other and there are always gray areas... therefore I'll revise my previous statement - its cleaner (and easier) to sort your data if you first properly type your data fields. In general use a number type for items that indicate a numeric value and a text type for items that just happen to include numbers. > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Brock, > Christian T, HRC-Alexandria > Sent: Monday, October 13, 2003 11:14 AM > To: 'Access Developers discussion and problem solving' > Subject: RE: [AccessD] Number vs text data type > > > Sorting is not really an issue, except for the time to sort. To sort > variable length numerical text cleanly, sort it formatted to the maximum > number of digits. For an example, variable length serial numbers of up to > eight digits using Format(SN,"00000000") would all sort in proper > numerical > order. > > Christian Brock > > -----Original Message----- > From: John B. [mailto:john at winhaven.net] > Sent: Monday, 13 October 2003 12:02 > To: Access Developers discussion and problem solving > Subject: RE: [AccessD] Number vs text data type > > > Use a number type rather than a text type if the number was used in > calculations BUT it also sorts cleaner than a text field of numbers. > 1 > 2 > 3 > 10 > 20 > 100 > 200.. > > The native sorting of a text field containing numbers sorts it as text not > numbers: > 1 > 10 > 100 > 2 > 20 > 200 > 3... > > A number type does not require additional processing, a text type would. > Without getting technical :o? > When defined as a Long Integer 123,456 would be stored as one unit. > When defined as text 123,456, would be stored as six individual units that > have no idea what the next units means until combined with a function that > converts it to a number (one unit) and then processed. > > If a number is actually a label and not a number then you can > choose either > data type but beware of the above limitations. Also down the if the label > may include text then you must choose a text type. For instance > 100a, 100b. > > I generally use Long Number for items that are indicating a value and Text > for items that are identifying a value. For instance age > indicates a value - > phone#s identify a value. You wouldn't add, average or multiple a set of > phone numbers but you might wish to do that with age. > > HTH > JB > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of CYNTHIA SPELL > > Sent: Monday, October 13, 2003 9:42 AM > > To: accessd at databaseadvisors.com > > Subject: RE: [AccessD] Number vs text data type > > > > > > I will, that's a good idea. What I've heard so far is that logic > > tells you that if you don't need to calculate, you should use > > text. And that a number data type requires additional resources > > in order for the field to have the ability to do the calculating. > > Even if all that is true, I don't see the need to change what's > > there already. And, again, the sort will be faster using a > > number data type. > > > > > > >>> john at winhaven.net 10/13/03 10:18AM >>> > > Although I agree with all of the comments made so far (leave it > > alone), you > > might want to get the reason the DBA wants you to change it. > There may be > > something going on that hasn't been revealed to you yet. Every > > now nad then > > this can happen :o) > > > > JB > > > > > -----Original Message----- > > > From: accessd-bounces at databaseadvisors.com > > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of > CYNTHIA SPELL > > > Sent: Monday, October 13, 2003 7:58 AM > > > To: accessd at databaseadvisors.com > > > Subject: Re: [AccessD] Number vs text data type > > > > > > > > > Thanks, Andy. Actually, you're all giving me the answer I was > > > looking for. My database administrator wants me to make the > > > change and I've been resisting.... > > > > > > > > > > > > >>> andy at minstersystems.co.uk 10/13/03 08:43AM >>> > > > Cynthia > > > If you changed them to text and the numbers ever increased to the > > > point where some went to an 8th digit then you'll wish you > > > hadn't. Sorting of numbers of variable lengths when held as text > > > is a problem. You get ansers like: > > > > > > 1 > > > 10 > > > 11 > > > 12 > > > 2 > > > 20 > > > 200 > > > 21 > > > 3 > > > > > > and so on > > > > > > I agree with Gustav. With no good reason to do it I'd leave > well alone. > > > -- > > > Andy Lacey > > > http://www.minstersystems.co.uk > > > > > > > > > > > > --------- Original Message -------- > > > From: "Access Developers discussion and problem solving" > > > <accessd at databaseadvisors.com> > > > To: "accessd at databaseadvisors.com" <accessd at databaseadvisors.com> > > > Subject: Re: [AccessD] Number vs text data type > > > Date: 13/10/03 11:54 > > > > > > > > > Thanks, Gustav! > > > > > > >>> gustav at cactus.dk 10/13/03 07:36AM >>> > > > Hi CYNTHIA > > > > > > > I inherited a budget number database with the primary key being > > > a 7-digit number. The data type for the field is currently number > > > (long integer). Since the field requires no calculation, just > > > > some sorting, I'm considering changing the data type to text. > > > The budget number table links to quite a few other databases, so > > > I'll have to change the field in other places. Is there a benefit to > > > > or problem with using one or the other data type? I remember an > > > instructor once saying that if there's a choice between using a > > > text or number type, use number since numbers sort faster than text, > > > > but I'm not so sure he knew what he was talking about. > > > > > > He did. > > > > > > With no good reason for the change, I would spend my time on more > > > interesting tasks! > > > > > > /gustav > > > > > > _______________________________________________ > > > 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 > > > > > > > > > > > > > > > > > > > > > > > > ________________________________________________ > > > Message sent using UebiMiau 2.7.2 > > > > > > _______________________________________________ > > > 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 > > > > > > > _______________________________________________ > 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 > >