[AccessD] Re: Number vs text data type

John B. john at winhaven.net
Mon Oct 13 14:51:03 CDT 2003


Thanks,
I couldn't (and still can't) find that anywhere in the A97 help.

Problem is if padded with spaces it doesn't sort numerically correct as it
does when padding with 0s.
Cstr sorts the same as the text field.

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Heenan,
> Lambert
> Sent: Monday, October 13, 2003 1:24 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] Re: Number vs text data type
>
>
> Format *always* pads with one space, to get more just include
> spaces in the
> format string, thus...
>
> format(2,"0") will display (without the quotes) " 2"
> format(2," 0") will display "  2"
>
> if you don't want any padding use Cstr instead of Format.
>
> Lambert
>
> > -----Original Message-----
> > From:	John B. [SMTP:john at winhaven.net]
> > Sent:	Monday, October 13, 2003 2:06 PM
> > To:	Access Developers discussion and problem solving
> > Subject:	RE: [AccessD] Re: Number vs text data type
> >
> > How do you indicate to pad with a space(s) in Format()?
> >
> > > -----Original Message-----
> > > From: accessd-bounces at databaseadvisors.com
> > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Robert L.
> > > Stewart
> > > Sent: Monday, October 13, 2003 12:17 PM
> > > To: accessd at databaseadvisors.com
> > > Subject: [AccessD] Re: Number vs text data type
> > >
> > >
> > > List,
> > >
> > > Simple rule...
> > >
> > > If you are not going to do math with it, store it as text,
> > > except when using it as a dumb primary key/foreign key.
> > >
> > > All of the reason cited in the posts are correct.  But
> > > if you need a list of numbers stored as text sorted
> > > properly, just pad it with 0 or space on the left side.
> > > Everything will be sorted correctly.
> > >
> > > The reason the keys should stay numeric is the speed of
> > > the search for a record.  It is a few milliseconds faster.
> > >
> > > Robert
> > >
> > > At 12:00 PM 10/13/2003 -0500, you wrote:
> > > >Date: Mon, 13 Oct 2003 08:47:57 -0700
> > > >From: "Charlotte Foust" <cfoust at infostatsystems.com>
> > > >Subject: RE: [AccessD] Number vs text data type
> > > >To: "Access Developers discussion and problem solving"
> > > >         <accessd at databaseadvisors.com>
> > > >Message-ID:
> > > >
> > > <E61FC1D4B1918244905B113C680BEA8631236E at infoserver01.infostat.local>
> > > >Content-Type: text/plain;       charset="us-ascii"
> > > >
> > > >Same holds true for social security numbers in the US and
> for telephone
> > > >numbers everywhere.  There are good reasons NOT to use
> numbers for some
> > > >kinds of numeric data, which is probably where the argument
> comes from
> > > >in the first place, that and the old approach of squeezing everything
> > > >into the smallest possible datatype to shave storage bytes.
> > > >
> > > >Charlotte Foust
> > > >
> > > >-----Original Message-----
> > > >From: Susan Harkins [mailto:ssharkins at bellsouth.net]
> > > >Sent: Monday, October 13, 2003 7:32 AM
> > > >To: Access Developers discussion and problem solving
> > > >Subject: Re: [AccessD] Number vs text data type
> > > >
> > > >
> > > >What about Zip Codes, etc... what possible purpose would you have for
> > > >treating such an entry as a number?
> > > >
> > > >I'm afraid this isn't old school -- it's still very alive
> and with us.
> > > >
> > > >Susan H.
> > > >
> > > >
> > > > > That sounds like one of my co-workers who comes from the
> > > > > old school early-days C and COBOL programming. He makes everything
> > > > > text unless an actual calculation must be performed, even
> when that
> > > > > doesn't necessarily make sense in context. I believe that if its a
> > > > > number it should be treated as a number unless there is a very
> > > > > compelling reason not to, for all of the reasons given so far and
> > > > > probably others I haven't thought of. To me, logic tells
> > > > > you that if the data is numeric you should use a number
> > > > > data type, using text just muddies things up. As far as a
> > > > > number field needing "additional resources", especially
> > > > > for calculations, that makes no sense at all to me. Some
> > > > > number types may take additional storage space as compared
> > > > > to holding the same digit characters in a text type, and
> > > > > that can become an issue for DBAs when they are working
> > > > > under short drive space conditions. Other than, I can't
> > > > > think of a resource reason.
> > > > >
> > > > > Ron
> > >
> > >
> > > _______________________________________________
> > > 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
>




More information about the AccessD mailing list