英文:
Want to calculate the Age of a Date Field in a Table in Access DB
问题
我有一个表格，其中有一个[DateOpened]字段和一个[Age]字段。我希望年龄字段能够自动计算与开放日期字段的年龄差异。开放日期字段以日期mm\dd\yyyy的格式存在。当我尝试在年龄字段中使用DateDiff函数时，出现错误，无法找到开放日期字段。
=DateDiff('d',[Date Opened],Date())
错误：数据库引擎无法识别验证表达式中的字段'Date Opened'，或者表格中的默认值。
有什么建议吗？我不想使用查询来计算年龄字段。但是，如果必须使用查询，我该如何设置字段计算在查询中？
英文:
I have a table that has a [DateOpened] field and an [Age] Field. I want the age field to auto calculate the age difference of the Date open field. the Date Open field is in a Date mm\dd\yyyy format. When i try to use the DateDiff function in the Age field i get an error can find the date open field.
=DateDiff('d',[Date Opened],Date())
error: The database engine does not recognize either the field'Date Opened' in a validation expression, or the default value in the table.
Any suggestions. I am not wanting to use a query to calculate the Age Field. However, if i have to how would i set that field calculation in the Query?
答案1
得分: 0
我会开始一个新的查询，在那里构建正确的公式，因为在查询构建器中构建和测试公式更容易。
这将意味着将类似以下内容作为额外的（参数化/计算的）查询字段添加进去：
年龄：DateDiff('d',[DateOpened],Now())
我怀疑你不太可能将其转移到表格本身，因为表格中的计算字段在允许的函数类型方面受到相当大的限制。我认为DateDiff是不允许的。
最佳做法是设置一个查询，通过该查询查看和与主表进行交互，该查询将添加任何计算字段，例如：
SELECT Table1.*, DateDiff('d',[DateOpened],Now()) AS Age FROM Table1;
我通常使用以下表格和查询名称的约定：
tbl_FOO - 包含原始（未计算的）数据
qry_FOO - 通过所有(*)，并补充任何所需的计算字段
英文:
I'd start a new query and build the correct formula there, as its easier to build & test formulas in the query builder.
It will mean putting something like the following in as an extra (parameterised / calculated) query field:
Age: DateDiff('d',[DateOpened],Now())
I doubt you'll be able to transfer that to the table itself though, as calculated fields in tables are quite restricted as to the kind of functions that are allowed. I don't think DateDiff is allowed.
Best practice would be to set up a query through which to view & interact with your main table, which adds on any calculated fields, e.g:
SELECT Table1.*, DateDiff('d',[DateOpened],Now()) AS Age FROM Table1;
I usually follow a convention with table & query names as follows:
tbl_FOO - holds the raw (uncalculated) data
qry_FOO - passes through all (*), and supplements that with any required calculated fields
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论