Expressions in Form

[Deb4566]

I created an expression in a form that adds 14 days to a field "Received Date" and places the information in the form in a field "Due Date". When I runa query, the field "Due Date" is blank. What have I done or not done?

[June7]

Post the query SQL statement for analysis.

[Deb4566]

Select [subpoena tracking].clinic, [subpoena tracking].[date received], [subpoena tracking].[clinic contact], [subpoena tracking].requestor, [subpoena tracking].[control #], [subpoena tracking].[last name], [subpoena tracking].[first name], [subpoena tracking].[ar #], [subpoena tracking].[date records sent], [subpoena tracking].[due date]
from [subpoena tracking];

[June7]

That query has no reference to the form. If [due date] is not a field in table and you simply want to calculate this value, do it in the query.

SELECT *, [date received] + 14 AS [due date] FROM [subpoena tracking];


If [due date] is a field in table and you want to populate it, that requires either an UPDATE query or VBA code. However, this value can be calculated when needed - no need to save to table. It is usually not desirable to save calculated data. A database principle is save raw data calculate in reports.


Advise no spaces or special characters/punctuation (underscore is exception) in names. Better would be ControlNum or Control_Num, not Control #.

[Deb4566]

[Due Date] is a fileld in this table. I have the expression in that field in the table. While I understand that raw data is the idea behind the db and calculations are in reports I have a need for it to be in the form (multiple people looking at it without timely access to the report). [due date] calculates within the table but when I ran the query tied to that table it doesn't populate. Nvm. I think I know what I didn't do. The expression is only in the form not the table. The query is pulling from the table not the form. I need to put the epression in the table. yes?

[June7]

You are using Access2003? Cannot have an expression in a field in table. Calculations can be done in query, textbox, or code.

[Deb4566]

drat! ok, I know how to do the expression in a query but it shows the column headrer as Exrp1 or some such code-y language. For the people that will need this information can I change it to read "Due Date"?

[June7]

You can change the Expr1 to whatever you want. Also, labels in form/report can be changed to whatever you want.


Advise no spaces or special characters/punctuation in names, nor reserved words as names. Better would be SubpTracking or Subp_Tracking.