1

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"

...
Mark Pelletier
  • 1,329
  • 2
  • 24
  • 42
  • 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. – Erik A Nov 02 '17 at 21:55
  • 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. – Mark Pelletier Nov 02 '17 at 22:40
  • Note that this breaks normalization (see https://stackoverflow.com/questions/10768696/database-normalization-whos-right). 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. – Erik A Nov 03 '17 at 06:52

1 Answers1

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.

Mark Elder
  • 3,987
  • 1
  • 31
  • 47