Use code or a macro to change field formats in tables 1

[zach028]

Is there a way to do this? I set up this function but Access asks me to define "Tables". I don't think this function is meant to work with tables but I don't know what else to try. Can anyone help?

Function FormatValue() As Integer

Dim val As Variant

val = Tables!Homeq.CURRBAL
Tables!Homeq.CURRBAL = "Currency"

End Function

Zach

 

[gwinn7]

Your question seems a little vague to me. Of what type of object is "Homeeq"? If it is a TableDef object, then the property "CurrBal" does not exist. May be its just me, but I think more clarification on your question is needed. What are you attempting to do specifically?

Gary
gwinn7

 

[zach028]

Quite simply, I am trying to format field "CURRBAL" in table "Homeq" to a currency format.

 

[PaulF]

If a Format Property already Exists for CurrBal then you will be able to change it in code using the following. You can leave out the Debug.Print lines, they're only there for test purposes. If there is no current entry for the Format property the code will not see the property when it loops through the For Next... just another Access thing. In this case you can set the Format Property in your Query field, or the textbox on a Form or Report that will display the value.

Dim db As DAO.database, tdf As TableDef
Dim fld As Field, p As Property
Set db = CurrentDb
Set tdf = db.TableDefs("Homeq&quot
Set fld = tdf("CurrBal&quot
For Each p In fld.Properties
If p.Name = "format" Then
Debug.Print p.Name; " Before "; p.Value
p.Value = "Currency"
Debug.Print p.Name; " After "; p.Value
End If
Next p

Set db = Nothing
Set tdf = Nothing


PaulF

 

[zach028]

I'm not sure if a Format Property is set. Looking at the table design, there is of course a data type (text), but nothing is set in the format options. I tried running the code anyway, but it didn't do anything at all. Is it possible to set a format property using code? Maybe by Setting p equal to something?
Or maybe do I not understand what you're talking about?

 

[PaulF]

The point I was trying to make is that when you loop through the properties of a field, if a entry already exists in the Format Property for that field, the code will recognize Format as a Property. If no entry exists in the Format Property for that field the code will not see that property for the field so you can't update it.

Here is a test you can run. If you didn't take out the Debug.Print lines in the code provided, then create a form, attach the code to a command button on the form. Open the Debug Immediate window (In 97 press Ctrl - G), click on the form's command button. If there is no entry in CurrBal's Format Property (open the table in Design View, look at the Properties for the CurrBal field), when the code runs there will be nothing on the Debug screen. If you enter a value in the Format Property for CurrBal (such as General) and then click on the button again (after saving and closing the table) you should see two lines in the Debug Window

Format Before General
Format After Currency

Then when you open the table you should see Currency as the Format Property for CurrBal.

Now another question.... why use Currency as the Format for a Text field.... this field should be Numeric - Double or Numeric - Single not Text.

PaulF

 

[zach028]

OK, that did work, but not how I wanted. I see that I may have been confusing you with my terminology (my bad!).

That code left the Data Type as text and changed the Format to currency. I want to change the Data Type to Currency and don't really to do anything with the Format. Thanks for sticking with me on this...

 

[zach028]

OK, I've worked around this problem. All I did was create a new field in the table and update it with the value from the old field. Here is the code if you want to see....

Dim db As Database
Dim tdf As TableDef, fld As Field

Set db = CurrentDb
Set tdf = db.TableDefs("Homeq&quot
Set fld = tdf.CreateField("NewCurBal", dbCurrency)

tdf.Fields.Append fld
DoCmd.OpenQuery ("qrytrnsfrcurbal&quot

 

[PaulF]

Unfortunately this doesn't seem do-able. This is from the Access Help Files for Type
---------------------------------------
The setting or return value is a constant that indicates an operational or data type. For a Field or Property object, this property is read/write until the object is appended to a collection or to another object, after which it's read-only. For a QueryDef, Recordset, or Workspace object, the property setting is read-only. For a Parameter object in a Microsoft Jet workspace the property is read-only, while in an ODBCDirect workspace the property is always read-write.
-----------------------------------------

Since you have already Appended CurrBal to the Fields Collection, the Type property is read-only, and can't be changed in code. You can however, change the Name of the current field, then add a new field named CurrBal with a Data Type of Currency, then update the field with the value in the old field, using CCur to convert to Currency. Use the following code (make a backup of the table before attempting this, just to be on the safe side).

Dim db As DAO.database, tdf As TableDef
Dim fld As Field, p As Property
Set db = CurrentDb
Set tdf = db.TableDefs("Homeq&quot
Set fld = tdf("CurrBal&quot
On Error GoTo errMsg
For Each p In fld.Properties
If p.Name = "Name" Then
Debug.Print p.Name; " Before "; p.Value;
p.Value = "OldCurrBal"
Debug.Print p.Name; " After "; p.Value;
End If
Next p
With tdf
.Fields.Append .CreateField("CurrBal", dbCurrency)
.Fields.Refresh
End With

db.Execute ("UPDATE Homeq SET Homeq.CurrBal = CCur([Homeq].[OldCurrBal]);&quot

Exit Sub
errMsg:
MsgBox Err.Number & " : " & Err.Description, vbCritical

PauF

 

[zach028]

Thanks again for your help Paul. This will definitely work

 

[ErikZ]

Hit the "Let X know if this post was helpful". I think it's a nice little rating system built into the forum.

Later,
ErikZ

 

[scking]

I may be a little late in adding my two cents worth but changing the format of the field in the table seems a roundabout and dangerous means of accomplishing a simple format of a data variable. Although you possible could accomplish what you want to do there are a number of safer and more accepted ways of accomplishing this task.

1) Set the format property on the control.
2) Format the variable for display in code.
3) Design the table using the correct data format.

Steve King