Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field format via VBA (Access 2000)

    I create and export tables using VBA. I have one particular field in a table that is a "Number" data type. I would like to format the field so that Its contents are "0000x" (five digits with zeroes as placeholders).

    Is there a way to do this in VBA, perhaps with ADO or DAO references? As a workaround, I've been going into the table in design view after it's created, but I'd like to automate it, and the various help files aren't very helpful.

    Thanks,
    Jim Peterkin

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

    Re: Field format via VBA (Access 2000)

    You can use DAO code like the following (of course, you must adapt it to your situation). It assumes that the table has already been created.

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("tblTest")
    Set fld = tdf.Fields("ID")
    Set prp = fld.CreateProperty("Format", dbText, "00000")
    fld.Properties.Append prp
    Set prp = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing

    You will need a reference to the Microsoft DAO 3.6 Object Library for this code to work.

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field format via VBA (Access 2000)

    Thank you, Hans! That worked perfectly.

    Jim

Posting Permissions

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