Table field display format

[dcx693]

I have a user with an Access database. He's got 50+ tables, and roughly 10 fields in each table. The fields are the Double data type. He'd like to have them display as "Standard" with 0 decimal places when the table is opened directly (purely a cosmetic preference on his part).

Anybody know the easiest way to achieve this? Obviously, he could go to each table, then to each field and change the Format and Decimal place fields, but that would be rather time-consuming.

I tried to devise a VBA method to do it, but I couldn't figure out how to manipulate the Format and Decimal place properties for fields in a table.

Help? Anyone?

 

[Pat Hartman]

Here's some code I wrote that changes the AllowZeroLengthString property for all text fields in all tables. You should be able to adapt it:

Public Function ChangeAllowZeroLengthProperty()
    Dim db As DAO.Database
    Dim tblLoop As DAO.TableDef
    Dim fldLoop As DAO.Field
    
    On Error GoTo Err_ChangeAllowZeroLengthProperty
    Set db = CurrentDb()
    db.Containers.Refresh
    
    ' Loop through TableDefs collection.
    For Each tblLoop In db.TableDefs
        ' Enumerate Fields collection of each
        ' TableDef object.
        If Len(tblLoop.SourceTableName) = 0 Then ' local table
            For Each fldLoop In tblLoop.Fields
                If Left(tblLoop.Name, 4) = "MSys" Or Left(tblLoop.Name, 2) = "xx" Or Left(tblLoop.Name, 2) = "zz" Or Left(tblLoop.Name, 1) = "~" Then
                Else
                    If fldLoop.Type = 10 Then 'text
                        Debug.Print tblLoop.Name & "-" & fldLoop.Name
                        fldLoop.AllowZeroLength = True
                    End If
                End If
            Next fldLoop
        End If
    Next tblLoop

Exit_ChangeAllowZeroLengthProperty:
    db.Close
    Exit Function

Err_ChangeAllowZeroLengthProperty:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & "-" & Err.Description
    End Select
    Resume Exit_ChangeAllowZeroLengthProperty
End Function

And here's a list of the type codes if you need them:

• TypeCode TypeDesc
  1 Boolean
  2 Byte
  3 Integer
  4 Long
  5 Currency
  6 Single
  7 Double
  8 Date/Time
  9 Binary
  10 Text
  11 LongBinary
  12 Memo
  15 GUID
  16 BigInt
  17 VarBinary
  18 Char
  19 Numeric
  20 Decimal
  21 Float
  22 Time
  23 TimeStamp

 

[dcx693]

Pat,
Thanks for the reply. I actually tried something very similar to what you suggested.

The problem is that the "AllowZeroLengthString" appears to be one of the field properties you can access (and set) using DAO, the "Format" property appears to be inaccessible.

I tried using a loop to enumerate all the properties of the fields and this is the list I got back:

ForeignName
DefaultValue
ValidationRule
ValidationText
Required
AllowZeroLength
FieldSize
OriginalValue
VisibleValue
ColumnWidth
ColumnOrder
ColumnHidden
DecimalPlaces
DisplayControl

Alas, it appears "Format" is not on the list.

 

[Pat Hartman]

InputMask and Caption also seem to be missing. Puzzling

 

[dcx693]

Oh well, thanks anyway for checking it out. It's not really worth more effort since it was just a cosmetic thing the guy wanted.

 

[jfly]

Same problem

I wanted to export data to excel and I wanted to know how to format each field. Until now, I'm using a trick but this is not efficient and clean: I open the form Access created and go check which format did he put into the textbox of the field. So if someone know how to read which format is used... Thx

 

[Pat Hartman]

You can't use the Format property to control how data is exported. The Format property of a column in a table only affects how the field is formatted when it is displayed in data sheet view. If you want to specifically format data for export, you will need to use the Format() function in a query and export the query rather than the table.

 

[jfly]

I rexplain

Here is the situation,
I'm a lazy programmer and I don't want to specify for each field if the format is Percent or not. Now I don't export with a query directly but with code.
The question is, how does Access know what format is specified in the field?
Because the form wizard knows it. And I want to know it too!!!
Another Microsoft's mystery.....