changing format of date field

  • Thread starter Thread starter Tcs
  • Start date Start date
T

Tcs

I've created a date field in a table, in a temp access db.

With MyTabledef
.Fields.Append .CreateField("DateRetrieved", dbDate)
End With

I want to change the format to "General Date", but can't figure out how. Might
someone help?

Thanks in advance,

Tom
 
After appending the field, CreateProperty() named "Format", of type dbText.

This is what we use to create the property if it does not already exist, or
set it if it does:

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.CreateProperty(strPropertyName, 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
Dim varDummy As Variant
On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
 
THANKS! This appears to be just what I need. I'm executing this:

DAOdbs1.TableDefs.Append MyTabledef

Set obj = MyTabledef.Fields
strPropertyName = "Format"
intType = dbText
varValue = "General Date"
strErrMsg = ""
Call fncSetPropertyDAO(obj, strPropertyName, intType, varValue, strErrMsg)

to get my format changed, BUT...

I get an error:

"Object doesn't support this property or method"

I used this:

Dim prpNew As Property
Dim prpLoop As Property

With DAOtbldef

' Create and append user-defined property.
Set prpNew = .CreateProperty()
prpNew.Name = "Description"
prpNew.Type = dbText
prpNew.Value = strLinkTableDesc
.Properties.Append prpNew

End With

as my example. I use this code to create a description on the linked table (for
the database window), that I pull from the internal ODBC table. (I'll switch
over to yours, once I get it to work.)

I changed:

Set obj = MyTabledef.Fields

to:

Set obj = MyTabledef

and the error went away, but I also didn't get the format for my date field
changed to "General Date". (It was empty/unchanged).

Since this is in my temp backend, is there anything special I need to do? (I
used "DAOdbs1", rather than "DAOdbs").

Sorry to ask for more help, but I'm still trying to get all the objects,
properties and methods straight in my head.

Thanks again,

Tom

Oh yeah, is "dbText" reserved? I ask, because before starting this thread, I
had tried setting it up as type string. And VB didn't like it.

And how should I dim SetPropertyDAO? Object? Integer? Right now I have it
defined as a global object.
 
and the error went away, but I also didn't get the format for my date
field changed to "General Date". (It was empty/unchanged).
Click to expand...

Bear in mind that there is just no reason for wanting to change the Format
property programmatically. It only affects (1) the display of the field in
table datasheets, and (2) the default assigned to new controls on forms
based on the field.

Now, no-one who is going to the trouble of all this VBA stuff is going to
be cruel enough to make their users look at table datasheets.

If you are going to be creating new forms and new controls, you are going
to be using the GUI, which means that you can use the GUI to change the
table and field properties in half the time anyway.

The Format property is not part of the data model, which is why it's not
visible to the .CreateField method or SQL DDL either.

Hope that helps


Tim F
 
Try:
Dim prpNew As DAO.Property
The Property exists in more than one library, so you need to disambiguate.

BTW, Tcs's comments about not bothering to set the Format property in this
case do make sense. However, there are cases where it might make sense to
set the Format property, such as if you used a Currency field to store a
value in millimetres (because you want fixed point, not floating point) and
you want to stop Access defaulting to dollars when you add a text box to
your forms/reports.
 
Back
Top