MS Access-日期作为表字段名称 [英] MS Access - Date as Table Field Name

问题描述

我需要在MS Access中创建一个表，然后根据用户提供的日期范围(StartDate，EndDate)，以DATES作为字段名称附加列，如下所示:

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:

姓氏|名|角色| 2017年10月1日| 10/02/2017 | ....

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

以下代码段接近，但只能使用格式为"Oct01"(mmmdd)的日期，而不是所需的"10/01/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"

...

推荐答案

将字段名称括在方括号中，如下所示:

Enclose the field name in square brackets like this:

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

方括号允许您在标识符中使用空格或其他特殊字符.您将需要确保在引用任何其他SQL语句中的字段名称时也使用方括号.

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.

这篇关于MS Access-日期作为表字段名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！