Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005
    Posts
    8

    Unanswered: Adding column format using ALTER TABLE

    Is there a way to change the format of a field while using the ALTER TABLE statement?

    I'm trying to make an integer field default to 2 characters so that if it is populated with a single digit, it still displays 2. i.e (2 would show as 02).

    Can i add something to this statement, so it does it when i add the field?

    ALTER TABLE tablename ADD COLUMN fieldname Integer

    If not, how would i go about doing this so that it doesn't have to be done manually? I'm trying to take a few manual steps out of a process i run monthly.

    Thanks!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ummmmmm

    fields in tables only have imaginary formats in Access-dreamworld.

    forget the problem that you are worrying about, leave the table to look after itself (getting the datatype correct, of course) and concentrate on having the user interface (the forms, reports) display what you want the user to see...

    ...or [shock-horror] are you letting the user play with the tables directly! [/shock-horror]

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    May 2005
    Posts
    8
    Actually, i'm the only user, so yeah i just look it up in the table view.

    but the root of this question, is that always have to export this file to text and need it to come up with the 2 digits for a vendor.

    i mean, doing it manually takes only a few seconds, i'm just trying to see if i can eliminate a step. that way i can leave less room for error.

    Thanks

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    well, you correctly understood the flavour of my point of view.
    at no time, ever, under any circumstances are my users allowed to play with tables.

    meanwhile: as 'db-God' you can do anything you like (and for sure i mess with my db at the table level (how else to correct the stupidities of users that i forgot to allow for in the design)).

    but your export direct from table should ignore your 'cosmetic' formatting at Access-dreamworld table-level.

    you can write individual rows to a text file including a format()
    you can use a query to reformat the numeric to string using format() and export the query return to text.
    ...and there are probably a dozen other routes.

    how many fields per row need this treatment?
    how many rows are you exporting?
    is a query already involved in the export?

    ...maybe post an example row (and the SQL generating it, if any)

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •