Loop through all Fields & Controls & Set Date Format

[MSingh]

Hi,

This may be a tall ask:

Is there a way to loop through all tables, find all fields with the Date/Time data type then set the ffg. field properties:
Format: yyyy-mm-dd
Input Mask:0000-00-00;0;_

Extending on this:
Is there a way to loop through all forms, find all controls with fields with the Date/Time data type then set the format property to: yyyy-mm-dd
& Finally,
Can the same be done for all reports?

Kind Regards,
Mohamed

[HansV]

I would strongly recommend not setting an input mask for date fields - it is bound to irritate users. Let them enter the date the way they prefer.

Do you really want to set the format of ALL date/time fields to yyyy-mm-dd? If you have any fields used for time, they would be set to yyyy-mm-dd too.

[MSingh]

Hi Hans,
Thank You for the very quick response.
The client requested the input mask. (another draw-back in support of your view: For a form I think the MS Help says that the windows calendar control will not be available if the text box has an input mask).
It's a new project with no data so no danger of changing any existing data.

Kind Regards,
Mohamed

[HansV]

I'd go back to the client and explain that an input mask has many disadvantages.

Here is code to set the Format property of date/time fields to yyyy-mm-dd in all tables:

Code: Select all

Sub SetDateFormat()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    On Error GoTo ErrHandler
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
        For Each fld In tdf.Fields
            If fld.Type = dbDate Then
                fld.Properties("Format").Value = "yyyy-mm-dd"
            End If
        Next fld
        tdf.Fields.Refresh
    Next tdf
    dbs.TableDefs.Refresh
    Exit Sub
ErrHandler:
    If Err = 3270 Then ' property doesn't exist
        Set prp = fld.CreateProperty("Format", dbText, "yyyy-mm-dd")
        fld.Properties.Append prp
        fld.Properties.Refresh
        Resume Next
    End If
End Sub

For forms/reports it's less clear - a control may already have its Format set to a date format, otherwise we don't know if it's intended to be used for dates.

[MSingh]

Thanks Hans,
& I'll follow-up with the client.
Kind Regards
Mohamed

[MSingh]

Hi Hans,
And the code should be run in the back-end of a split database.
Kind Regards,
Mohamed

[HansV]

You can replace the line

Code: Select all

    Set dbs = CurrentDb

with

Code: Select all

    Set dbs = OpenDatabase("path and filename of backend database")

substituting the actual path and filename, and add a line

Code: Select all

    dbs.Close

at the end of the code.