Question Today's date in a calculated field (2 Viewers)

[ReinM]

Then you will have to do it at the form level.

In the Form_BeforeUpdate event you can check whether all necessary data is present and correct and then add Now() to your Timestamp field if not already present

In the Form Design I know how to do it, but I want to do it without a form.
In the meantime I found the solution! Will describe it in a separate message.

 

[Minty]

Access creates the new record automatically, with the default values in it.

No, it doesn't, they are simply placeholders and it says <New> in the Autonumber field.

On the table go to the last record, and look at the "New" record
Press F5 to refresh the screen, it will update the Now() default value to the current time.

 

[cheekybuddha]

In the Form Design I know how to do it, but I want to do it without a form.

I guess you can use a data-macro

 

[ReinM]

I found the solution!
Open the table design view.
Click on create data macros.
Select "before change".
Enter:
===
If [IsInsert]=true Then
Setfield
Name Planned
Value = now()
End If
===
"Planned" is of course the field where I want the timestamp.
The above text is partially generated in the process by access itself.
It works!

 

[cheekybuddha]

I found the solution!
Open the table design view.
Click on create data macros.
Select "before change".
Enter:
===
If [IsInsert]=true Then
Setfield
Name Planned
Value = now()
End If
===
"Planned" is of course the field where I want the timestamp.
The above text is partially generated in the process by access itself.
It works!

Won't that set the Planned on every change to the record? I thought you only wanted to store the DateCreated?

 

[ReinM]

Won't that set the Planned on every change to the record? I thought you only wanted to store the DateCreated?

The "IfInsert=True" clause prevents that. Without that, it is indeed changed with every update.

 

[cheekybuddha]

The "IfInsert=True" clause prevents that. Without that, it is indeed changed with every update.

Very good! I missed that!

 

[ReinM]

No, it doesn't, they are simply placeholders and it says <New> in the Autonumber field.

On the table go to the last record, and look at the "New" record
Press F5 to refresh the screen, it will update the Now() default value to the current time.

You are right that refreshing updates it. But that is an extra action. That I wanted to avoid, and also it is easily forgotten.
Without refreshing, the existing (old) value is fixed as soon as a value is entered somewhere in the record.
On the basis of your message I hoped that access would refresh the value automatically before fixing it, but that doesn't seem to be the case.

 

[Minty]

Only if you are directly entering data into the Table, which you shouldn't be doing.
If you open a form and then click on new record the Now() default value will be correct for when the new record is created.

 

[ReinM]

Only if you are directly entering data into the Table, which you shouldn't be doing.
If you open a form and then click on new record the Now() default value will be correct for when the new record is created.

In this particular case I wanted to do it without a form. Next time that I do use a form I will try out your suggestion, because until now, in forms I used a "before update" event in the design to create a timestamp, which is much more work than what you suggest.

 

[Minty]

Additionally, If you insert new records with a query then the Now() default value also works for when the record is created.

 

[Pat Hartman]

In this particular case I wanted to do it without a form. Next time that I do use a form I will try out your suggestion, because until now, in forms I used a "before update" event in the design to create a timestamp, which is much more work than what you suggest.

1. You said you didn't want to set the time when the record was inserted and yet that is what you are doing with the data macro. Simply setting a default solves the problem. No macro is needed.
2. If one line of code is hard for you, maybe some training will help.

 

[ReinM]

In this particular case I wanted to do it without a form. Next time that I do use a form I will try out your suggestion, because until now, in forms I used a "before update" event in the design to create a timestamp, which is much more work than what you suggest.

1. You said you didn't want to set the time when the record was inserted and yet that is what you are doing with the data macro. Simply setting a default solves the problem. No macro is needed.
2. If one line of code is hard for you, maybe some training will help.

at 1: no, timestamp for Date Created meaning the first time any value was entered in a field was exactly what I wanted.
at 2: thank you for this welcoming comment.

 

[Pat Hartman]

If you sit on the new record rather than entering the data right away, the Now() default will get stale and so won't reflect the EXACT moment the record was saved. That is why if it matters to you, the simplest solution is the single line of code in a form's BeforeUpdate event. But you can look at it this way, rather than writing the single line of code, you learned how to create a data macro