change date field propertie to short date

[hafco]

hello everybody,
i am trying to edit a datefield propertie into short date format trough VBA but its just not working for me .

this is wat ive come up with so far

DoCmd.RunSQL ("ALTER TABLE tablename ALTER COLUMN fieldname TEXT(255);")
this is a text field propertie change and this works just fine,but now i want to change a date field format into short date but i can't get it to work.

DoCmd.RunSQL ("ALTER TABLE tablename ALTER COLUMN fieldname DATE(Short Date);")

any suggestions?

 

[NauticalGent]

Good morning hafco,

Are you trying to alter the format for a form or report? If so, there is a more efficient way to do it without VBA. If you are doing it for table/datasheet view, you can do it by changing the table properties, again without VBA.

 

[Minty]

I don't think Short Date is a valid data type. It is a valid display format.

 

[hafco]

thanks for the replies

i need to make this change to multiple application tables so therefore it has to be done trough VBA.

 

[Minty]

What format is the Field at the moment ? the only valid date time format in Access is DateTime as far as I know.

 

[hafco]

i need to set the date notation to 24-12-1977 instead of standard format.
the fieldtype DateTime is ok but in the properties window of the field i need to set the date notation to short date.

 

[Minty]

Okay as we have said you can't do that as a field type, but you can display it that way by using a format on the field.

To do that in VBA is not simple - but have a look here for a solution http://answers.microsoft.com/en-us/...t-fields/5c243510-2aa6-40cf-8ccd-c794d0e8fb97

 

[hafco]

Yesss i found a way

Created a sub based on a thead i found on the internet

Sub DeleteFieldProperty()

Dim tdf As TableDef
Dim curDatabase As Object
Dim fld As Object
Dim prp As DAO.Property

'Get a reference to the current database
Set curDatabase = CurrentDb

'Get a reference to a table
Set tdf = curDatabase.TableDefs("TestTable")
Set fld = tdf.Fields("DateD")
fld.Properties.Delete "Format"

Set prp = fld.CreateProperty("Format", dbText, "Short Date")
fld.Properties.Append prp

Set tdf = Nothing
Set fld = Nothing

End Sub

This sub can only be performed on a existing format property
It first delete's the property and then adds a new property in the Short Date format.

 

[gemma-the-husky]

I don't understand the point of this. A date time data type stores a date and/or a time

the display format is simply a way of displaying whichever bit of the date you want to see, be that the year, month, quarter, day in full, etc.

I can't see any benefit in manipulating the table at all