Results 1 to 7 of 7
  1. #1
    jadog is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    5

    Unable to change Field Properties for a column using VBA


    I have a table named horizon that is imported from an excel file. I have automated the process using the "DoCmd.TransferSpreadsheet" function in a macro. There is a column that imports with a Data Type as currency. I have found no way to change that with the DoCmd.TransferSpreadsheet import. So at this point, I have attempted to instead change the column Data type to a number and then from there, change the field property of the column to be a "fixed" format. The code below changes the data type from Currency to Number without a problem, but it does nothing to change the field properties. After running it, I go to Design View and the Data Type has changed, but the Field Properties are still set to Currency.

    Code:
        'Change the column Data type from "Currency" to "Number
        DoCmd.RunSQL "ALTER TABLE Horizon ALTER COLUMN [Cost] Double;"
    
        'Change the column field properties from Currency to Fixed
        With db.TableDefs("Horizon").Fields("Cost")
            .Properties.Append .CreateProperty("Format", dbText, "Fixed")
            .Properties.Append .CreateProperty("DecimalPlaces", dbDouble, 2)
        End With
    My end goal here is to import the excel spreadsheet and format the columns correctly, and then export it as a csv file - all through a vbs macro.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why is setting the format property important? Are you using export wizard? Does the wizard use format setting to actually force place holder zeros in decimals? Interesting.

    Review http://www.access-programmers.co.uk/...d.php?t=148284
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jadog is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    5
    Quote Originally Posted by June7 View Post
    Why is setting the format property important? Are you using export wizard? Does the wizard use format setting to actually force place holder zeros in decimals? Interesting.

    Review http://www.access-programmers.co.uk/...d.php?t=148284
    Currently, when I export as a csv file (using the wizard), I am choosing Advanced and this is where I would normally get the opportunity to change the field type. However, the Specification area shows the Field Name only. So I was only assuming this functionality does not exist.

    The problem here is that one of my fields contains a $ before each number. It's Data Type is currency and it's imported this way from Excel. If I change it to text, then I get numbers like 347.5622. I want it to be out to only two decimals. If I change the Data Type to a number, then it rounds to 348.

    The only way I've found it to display correctly is by going to Design View and changing the Data Type to a Number and then setting the "Format" in Field Properties as Fixed.

    Again, I'm trying to use a button on a form to import from an excel (xlsx) file, change a column so it does not include the $ before each number, and then export as a csv file.

  4. #4
    SteveF is offline Generally AccessAble
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2010
    Location
    Fourth Corner
    Posts
    123
    If I needed the CSV output formatted in a specific way, and formatted to exactly two decimals every time, I'd use a query as my row source for export rather than exporting directly from a table. In the query, I would control the appearance of that field within the query using the Format function (look it up in Visual Basic Help; it works the same in a query). Something like

    Format([Fieldname],"#,##0.00")

    Would be a good place to start.

    Bear in mind that the Format function returns a string/text value, so your CSV export will surround it with quotes unless you turn that off.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Here are some interesting references on topic
    http://answers.microsoft.com/en-us/o...d-c794d0e8fb97
    http://allenbrowne.com/AppPrintMgtCo...SetPropertyDAO

    This is working for me. If there is already a Format property set, must delete it then append the revised setting.
    Code:
    Public Sub ChangeTableFieldFormat()
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        Dim prp As DAO.Property
        DoCmd.RunSQL "ALTER TABLE Table1 ALTER COLUMN [test] Double;"
        With CurrentDb
            Set tdf = .TableDefs("Table1")
            Set fld = tdf.Fields("test")
            If HasProperty(fld, "Format") Then fld.Properties.Delete ("Format")
            Set prp = fld.CreateProperty("Format", dbText, "Fixed")
            fld.Properties.Append prp
            fld.Properties.Delete ("DecimalPlaces")
            Set prp = fld.CreateProperty("DecimalPlaces", dbByte, 2)
            fld.Properties.Append prp
            Set tdf = Nothing
            Set fld = Nothing
            Set prp = Nothing
        End With
    End Sub
    
    Public Function HasProperty(obj As Object, strPropName As String) As Boolean
        'Purpose: Return true if the object has the property.
        Dim varDummy As Variant
        On Error Resume Next
        varDummy = obj.Properties(strPropName)
        HasProperty = (Err.Number = 0)
    End Function
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    jadog is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    5
    Quote Originally Posted by June7 View Post
    Here are some interesting references on topic
    http://answers.microsoft.com/en-us/o...d-c794d0e8fb97
    http://allenbrowne.com/AppPrintMgtCo...SetPropertyDAO

    This is working for me. If there is already a Format property set, must delete it then append the revised setting.
    Code:
    Public Sub ChangeTableFieldFormat()
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        Dim prp As DAO.Property
        DoCmd.RunSQL "ALTER TABLE Table1 ALTER COLUMN [test] Double;"
        With CurrentDb
            Set tdf = .TableDefs("Table1")
            Set fld = tdf.Fields("test")
            If HasProperty(fld, "Format") Then fld.Properties.Delete ("Format")
            Set prp = fld.CreateProperty("Format", dbText, "Fixed")
            fld.Properties.Append prp
            fld.Properties.Delete ("DecimalPlaces")
            Set prp = fld.CreateProperty("DecimalPlaces", dbByte, 2)
            fld.Properties.Append prp
            Set tdf = Nothing
            Set fld = Nothing
            Set prp = Nothing
        End With
    End Sub
    
    Public Function HasProperty(obj As Object, strPropName As String) As Boolean
        'Purpose: Return true if the object has the property.
        Dim varDummy As Variant
        On Error Resume Next
        varDummy = obj.Properties(strPropName)
        HasProperty = (Err.Number = 0)
    End Function
    Thank you. I'm sure this would work, but for some reason, I'm getting an error: "Compile error: User-defined type not defined".

    It's odd, because when I go into a new fresh access database and use it, then I don't get this error. I tried going to References and enabling the "DAO 3.6 Object Library", but then I get a different error: "Compile error" Sub or Function not defined".

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Corruption could be cause of the failure in original db. Maybe import everything to new db and go from there.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 10-04-2012, 01:30 PM
  2. Replies: 3
    Last Post: 07-20-2012, 11:41 AM
  3. Replies: 5
    Last Post: 05-25-2012, 12:02 PM
  4. Replies: 1
    Last Post: 08-18-2011, 08:35 AM
  5. Setting Field Properties for Numbers
    By Tim Hardison in forum Access
    Replies: 1
    Last Post: 12-09-2009, 06:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums