MS Access - Date as Table Field Name

Question

I need to create a table in MS Access, then append columns with DATES as the field names based upon a user-provided date range (StartDate, EndDate), like this:

LastName | FirstName | Role | 10/01/2017 | 10/02/2017 | ....

The following snippet comes close, but can only use dates formatted as "Oct01" (mmmdd), vs the desired "10/01/2017". I swear my earliest attempts did produce the desired date format as the field name, but I cannot seem to reproduce - guessing a syntax issue on "format";

Dim db As Database
Dim StartDate As Date
Dim EndDate As Date
Dim strDate As String

Set db = CurrentDb

StartDate = #10/1/2017#
strDate = Format(StartDate, "mmmdd")

db.Execute "CREATE TABLE MyTable " & _
    "(LastName CHAR, FirstName CHAR, Role CHAR);"
db.Close

CurrentDb.Execute "ALTER TABLE myTable ADD COLUMN " & Format(StartDate, "mmmdd") & " CHAR"

StartDate = StartDate + 1

CurrentDb.Execute "ALTER TABLE myTable ADD COLUMN " & Format(StartDate, "mmmdd") & " CHAR"

...

Rebecarebecca

Note that having dates as field names is a clear indicator of a non-normalized database. Often, you can avoid the need by properly normalizing your database, and often make your database easier to work with in the process. – Beisel

Erik, to clarify, I am logging specific events against the horizontal timeline. Essentially a horizontal calendar timeline. This is visual tool/form to assist our schedulers to see where resources have open days in their calendar. The read-only "calendar view" is built on-the-fly from normalized data in another table. – Rebecarebecca

Note that this breaks normalization (see #10769196). As discussed there, there are arguments for breaking normalization, and I often break this rule by caching results of queries that take hours. But you might want to consider using a crosstab query to generate this data. – Beisel

Answer 1

Enclose the field name in square brackets like this:

  CurrentDb.Execute "ALTER TABLE myTable ADD COLUMN [10/02/1017] CHAR"

The square brackets allow you to use spaces or other special characters in identifiers. You will need to ensure you also use the brackets when referencing the field name in any other SQL Statements.

Even though you can do this, it is really not recommended practice to use special characters in identifier names.

Myosin

Thanks Mark! That does what I want. Thanks for the caution on calling these via SQL. – Rebecarebecca