Retrieve field properties DAO recordset

[kt1978]

Hi

Is it possible to retrieve the format of a field when looping through recordset.

e.g you can get the field type by using

rs.Fields.Type

But the type for decimal or percentage is the same. It is the format that is different

I need to be able to see what is formatted as a percentage and what is formatted as fixed.

Thanks

 

[kt1978]

I've kind of got round it but it doesn't feel right to do it like this...

Basically, changed the name of the field to include a "%" symbol and the use instr to determine the existence of the percentage symbol.

If InStr(1, rs.Fields(y).Name, "%") > 0 Then r.NumberFormat = "0.00%"

Any better ideas?

 

[nanscombe]

Create yourself a Public Function thus...

Public Function formatOfField(ByVal fofField As Variant)
On Error Resume Next
  formatOfField = "<none>"
  formatOfField = fofField.Properties("Format")
End Function

Pass it a field to work with and it will pass back either "<none>", if no format is set, or the value of the format ie Percent, General Number etc.

 

[Galaxiom]

I've kind of got round it but it doesn't feel right to do it like this...

Basically, changed the name of the field to include a "%" symbol and the use instr to determine the existence of the percentage symbol.

If InStr(1, rs.Fields(y).Name, "%") > 0 Then r.NumberFormat = "0.00%"

Any better ideas?

Anything would be better than including a % in a fieldname.

The Format Property should be read from the source field.

 

[MarkK]

To elaborate on what nanscombe is saying . . .
If a format was applied to a field in a table or query, then when you open a DAO.Recordset, the corresponding DAO.Field object stores that fact in its Properties collection by adding a Format property. You can reference that property like this . . .

dim fld as dao.field
set fld = rst.fields("SomeField")
debug.print fld.properties("Format").value

. . . but if it's not present you'll get an error. Nanscombes function handles all this, and I would refactor it like this . . .

Function GetFormatPropertyValue(Field as DAO.Field) as String
On Error Resume Next
    GetFormatPropetyValue = Field.Properties("Format").Value
End Function

. . . so everything is strongly typed and more clearly named.

 

[kt1978]

Anything would be better than including a % in a fieldname.

Ha, I did say it didn't feel right...

 

[kt1978]

Hi, sorry for not getting back to you on this sooner... although it was messy I had bigger fish to fry...

Anyway, just wanted to say thanks to you both for this. Perfect solution.

 

[gemma-the-husky]

but generally speaking

Sub showfieldproperties()

 Dim prop As Property
Dim fld As Field
Dim tdf As TableDef
Dim db As Database

 Set db = CurrentDb
Set tdf = db.TableDefs("mytable")
Set fld = tdf.Fields("somefield")

 For Each prop In fld.Properties
    On Error Resume Next
    MsgBox (prop.Name & "  " & prop.value)
Next

 End Sub