Create table and set format proprty

[mahnovetsky]

Does anybody know of a way to set the format property for a date field
in VBA?? I need to set the property to medium so I can display display
aussie dates on a graph..

Here is what Im doing:

1) Im creating a table on the fly

' Delete the temporary temp if it exists.
On Error GoTo error
db1.Execute "DROP TABLE temp_DS;"

error:
' Create a new temp_CIPII table
db1.Execute "CREATE TABLE temp_DS " _
& " ([Date] DATETIME," _
& " DS_Changes INTEGER," _
& " DS_Defects INTEGER);"

2) now I need to set the format property for the Date,
here is my guesss: Set
CurrentDb().Tab leDefs("temp_DS ").Fields("Date ").Properties(" format") =
"Medium"... . but it does not work

OR does anybody know how to convert american dates to aussie dates
when displaying to a graph...All I need is to display aussie dates on
the graph..HELP!!!

[Allen Browne]

You will need to CreateProperty( ) and Append to the field's Properties.

The function below lets you do it in one step, like this:
? SetPropertyDAO( dbEngine(0)(0). TableDefs("MyTa ble").Fields("M yDate"),
"Format", dbText, "Medium Date")

Function SetPropertyDAO( obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj , strPropertyName ) Then
obj.Properties( strPropertyName ) = varValue
Else
obj.Properties. Append obj.CreatePrope rty(strProperty Name, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function

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
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"mahnovetsk y" <ma*********@ya hoo.com.au> wrote in message
news:e5******** *************** ***@posting.goo gle.com...

Does anybody know of a way to set the format property for a date field
in VBA?? I need to set the property to medium so I can display display
aussie dates on a graph..

Here is what Im doing:

1) Im creating a table on the fly

' Delete the temporary temp if it exists.
On Error GoTo error
db1.Execute "DROP TABLE temp_DS;"

error:
' Create a new temp_CIPII table
db1.Execute "CREATE TABLE temp_DS " _
& " ([Date] DATETIME," _
& " DS_Changes INTEGER," _
& " DS_Defects INTEGER);"

2) now I need to set the format property for the Date,
here is my guesss: Set
CurrentDb().Tab leDefs("temp_DS ").Fields("Date ").Properties(" format") =
"Medium"... . but it does not work

OR does anybody know how to convert american dates to aussie dates
when displaying to a graph...All I need is to display aussie dates on
the graph..HELP!!!