retrieve the Format Setting of a Field

[prabha_friend]

How to retrieve the Format Setting of a Field for Example “dd/mm/yyyy” using VBA. I tried it. But No avail. Help Needed. Is it possible?

 

[raskew]

Re: Not Everyone look for Soul inside. Sometimes Face is Enough.

Perhaps if you posted subject titles that relate to the MS Access problem, you might get some responses.

Bob

 

[prabha_friend]

Raskew. Seriously I am trying to write my code like a poem. Anyway thank you for helping me by changing the Title.

 

[gemma-the-husky]

you could try something like

controls("fieldname"). {intellisense}

this should let you get at all the properties, and this will include format if there is one

if its blank/null then it will use a default format for the data type - but i really am not sure how you would get at that.

 

[prabha_friend]

Actually I dont want to use forms or Reports here. Is it possible to get the format setting properties of a field in a "table" directly. Thanks for your replies.

 

[gemma-the-husky]

similar syntax

it will be either

currentdb.tabledefs("tablename").fields("fieldname"). {intellisense}

or maybe

currentdb.tabledefs("tablename").fields("fieldname").properties("someproperty")

 

[prabha_friend]

Gemma-the-husky you tried it? It is neither listed in intellisense not comes from properties collection. Shall we conclude as "Not possible" then?

 

[gemma-the-husky]

i didnt try it

i just thought that would most likely be where it was to be found.
i am sure it is possible - pretty well everything is just part of a collection of some sort

------------------

I have tried it now - like a lot of things its a user created property - so it isnt there unitl you define it - then it is there

this sub shows you all the properties of a given field

Sub tryit()
Dim s As String
Dim dbs As Database
Dim prp As Property

s = "Properties " & vbcrlf 
Set dbs = CurrentDb
For Each prp In dbs.TableDefs("switchboard items").Fields("argument").Properties
    s = s & prp.Name & vbCrLf
Next
MsgBox (s)
End Sub

create a format - then you will see it IS there



and this version will show you the format string

Sub tryit()
Dim s As String
Dim dbs As Database

On Error GoTo noprop
Set dbs = CurrentDb
MsgBox (dbs.TableDefs("switchboard items").Fields("thedate").Properties("format").value)
Exit Sub

noprop:
MsgBox ("Property Not Found" & vbCrLf & _
    "Error: " & err & "  Desc: " & err.Description)

End Sub