CODEWITHSUNDEEP
ms-accessvba
Mark Pelletier
Mark Pelletier

Reputation: 1359

MS Access - Date as Table Field Name

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"

...

Upvotes: 1

Views: 2189

Answers (1)

Mark Elder
Mark Elder

Reputation: 4127

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.

Upvotes: 1

Related Questions