Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    10

    Unanswered: Formatting query fields with code

    Does anybody know of a way to format a query field using VBA code?

    I would like to set the format of a particular query field to "#,##0.00" using code. Currently, I don't seem to be able to read from, or write to, the query field properties "Format" or "DecimalPlaces"

    Using Access 2002.

    Any and all help gratefully received.

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    queries do not have Format nor Decimalplaces properties. You can change your output though:

    Select TableA.CurrencyAmount, format(TableA.CurrencyAmount,"#,##0.00") As FormattedCurrency
    From TableA;

  3. #3
    Join Date
    Jul 2003
    Posts
    10
    Many thanks - will give this try. Also found some interesting VB methods on other forums. Seems like you can create properties and then append them to the querydef. Would you like the code?

    Cheers


    Originally posted by jmrSudbury
    queries do not have Format nor Decimalplaces properties. You can change your output though:

    Select TableA.CurrencyAmount, format(TableA.CurrencyAmount,"#,##0.00") As FormattedCurrency
    From TableA;

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    sure

  5. #5
    Join Date
    Jul 2003
    Posts
    10
    Code is:

    Sub CreateMyProperty(MyQDef As Object, MyFieldName As String, MyFormatName As String, MyDecimalPoint As Byte)
    Dim myfield As Object
    Dim myprp1 As Object
    Dim myprp2 As Object
    Set myfield = MyQDef.Fields(MyFieldName)
    Set myprp1 = myfield.CreateProperty("Format", dbText, MyFormatName)
    Set myprp2 = myfield.CreateProperty("DecimalPlaces", dbByte, MyDecimalPoint)
    myfield.Properties.Append myprp1
    myfield.Properties.Append myprp2
    Set myfield = Nothing
    Set myprp1 = Nothing
    Set myprp2 = Nothing
    End Sub

Posting Permissions

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