Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    formatting in table (Office 2007)

    Have an access db with a phone field formatted (xxx) xxx-xxxx. However, when data was imported it is in the format xxx-xxx-xxxx. How to change the formatting with an ALTER TABLE command?
    Thanks

    Kim

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formatting in table (Office 2007)

    I don't think you can use ALTER TABLE for that.
    Do you just want to change the display format, or do the data themselves have to be converted?

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formatting in table (Office 2007)

    Thanks for the response Hans. Yes, the data needs to be converted.
    Thanks

    Kim

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formatting in table (Office 2007)

    ALTER TABLE changes the structure of a table. To convert data, you can use an update query. The expression in the Update to line would look like
    <code>
    "(" & Left([FieldName],3) & ") " & Mid([FieldName],5)
    </code>
    where FieldName is the name of the relevant field.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formatting in table (Office 2007)

    Thanks, Hans. That did what I wanted.

    Is it possible to change a date field from x /xx/xxxx to 0x/xx/xxxx. If the month is < 10 but the month is in the first spot, move the month (i.e. 1-9) to the right and replace the first spot with a 0.
    Thanks

    Kim

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formatting in table (Office 2007)

    If you have values of the form x /xx/xxxx, they are text values, not real date values. You could add a date/time field to the table, and populate it in an update query using the following expression in the Update to line:
    <code>
    DateValue([FieldName])
    </code>
    where FieldName is the name of the text field. If you prefer to keep on using the text field, you could update the text field to
    <code>
    IIf(Mid([FieldName],2,1)=" ","0" & Left([FieldName],1),Left([FieldName],2)) & Mid([FieldName],3)</code>

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formatting in table (Office 2007)

    Hans,

    If i put the IIF statement into a query, I do not see any changes.
    Thanks

    Kim

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formatting in table (Office 2007)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formatting in table (Office 2007)

    Thanks, Hans.
    Attached Files Attached Files
    Thanks

    Kim

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formatting in table (Office 2007)

    The birthdate field in the table is already a date/time field, so there are no values of the form "x /xx/xxxx" <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  11. #11
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formatting in table (Office 2007)

    Apparently I forgot to change it back to text. Either way you will note that there is a space after the month before the / where the format should be either xx/xx/xxxx or x/xx/xxxx at least that is the format which does not cause an error in VB. Is there a way to change the records without doing it 1 by 1?
    Thanks

    Kim

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: formatting in table (Office 2007)

    Set the format property of the birthdate field to "mm/dd/yyyy" if you want the months first, or "dd/mm/yyyy" if you want days first.
    Regards
    John



  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formatting in table (Office 2007)

    As John says, you don't have to do anything with the records, just set the Format property of the field. Apparently your default date format is a bit strange - on my PC (with dd-mm-yyyy as default format) I didn't see any spaces in the dates.

  14. #14
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formatting in table (Office 2007)

    Thanks to all.
    Thanks

    Kim

Posting Permissions

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