date (1 Viewer)

[rainbows]

hi
I have a target date field in my form , what I would like to record is the date when someone enters the target date for the first time only could you please advise the best way for me to do this

thank you

steve

 

[CJ_London]

create another field called say TimeStamp and set it's default property to Now()

Since this is not a field the user will need access to, no need to display on a form. If you do, ensure the control is disabled

 

[arnelgp]

you can also create a Data Macro for your table, use Before Change macro.
supposed that TargetDate is your DateField and TargetDateEntered is the date the
TargetDate is entered for the first time:

 

[rainbows]

Issue List

in the snapshot you will see a date the ARNO was opened the 4 column so lets say the user don't put a targetdate in ( f which sometimes they cant because they need other peoples input but I want to record that time from when it was opened until the target date was set you can see record no 3 is closed and it was late by 42 days . but this might not be true that's why I want to log the date the target date was entered . I don't want that date to change even if the target date changes the timestamp date repeats it self on all records ???

thanks for you help
Issue List

ARNO	targetDate	days late/early	Opened Date	Dateclosed	timestamp
3​	17/10/2021​	42​	22-Oct-21​	28/11/2021​	22/10/2021 09:55:19
4​	17/10/2021​	5​	22-Oct-21​	​	22/10/2021 09:55:19
1​	08/10/2021​	14​	21-Oct-21​	​	22/10/2021 09:55:19
2​	24/10/2021​	-2​	21-Oct-21​	​	22/10/2021 09:55:19
5​	24/10/2021​	-2​	22-Oct-21​	​	22/10/2021 09:55:19
6​	16/10/2021​	6​	22-Oct-21​	​	22/10/2021 09:55:19

 

[arnelgp]

it repeat because you set it as Default value.

 

[CJ_London]

you originally said

I have a target date field in my form , what I would like to record is the date when someone enters the target date for the first time only could you please advise the best way for me to do this

now you are saying

but I want to record that time from when it was opened until the target date was set

so by when it was opened, you don't mean from when the record was created, you mean you want the datetime someone completed the opened date and the datetime someone completed the target date, regardless of the values of those two fields

timestamp is just to show when the record was created - if that is the same as the time the opened date is populated you can use that, plus Arnel's macro

Otherwise forget the timestamp column, you need two datetime columns and use a modification to Arnel's macro to update the two new fields

Or just use Arnel's macro and compare that to the opened date

 

[rainbows]

sorry if I have mislead you
lets take ARNO number 3
the record was created on the 22/10 without the targetdate being entered
but lets say the target date was entered in record no 3 on the 29/10 I would like a record that tells me it was entered on the 29/10 if I change that target date on record no 3 on say on 3/11 I don't want it to change the first date I want it to stay at the 29/10

hope I have explained it better

thanks
steve

 

[CJ_London]

in that case Arnel's solution will work - have you tried it?

edit: except on the first line there is a type - =fase should be =false

 

[rainbows]

I have looked at it but to be honest I don't know where to put it. on my form , table and I cannot see where I find the "before change "

steve

 

[Pat Hartman]

If you want the date to be populated the first time a user LOOKS at a record regardless of whether it is updated, you can't use a data macro, you will have to use the form's Current event. The logic is the same though.

 

[CJ_London]

it does not go into a form, but into the table

open the table in design view, on the ribbon you will see an option for create data macros - click on it

5th option down is before change. Click on that and refer to Arnels post to complete it

 

[Pat Hartman]

"IT" being the data macro but the data macro doesn't have an event that executes on read. That is the point. If you want the update to happen on the first update of the record, the data macro may also update the field for a NEW record which doesn't sound like what you want. Test carefully. You will probably have to add other conditions to the macro to check other fields.

 

[rainbows]

got this error

 

[CJ_London]

you do have a field called targetdateentered?

 

[LarryE]

Try

If IsNull([targetdate])=False Then
    If IsNull([targetdateentered])= True Then
        [targetdateentered]=Now()
    End If
End If

It appears you may have mixed brackets [] with parentheses ()

 

[CJ_London]

good spot!

 

[rainbows]

I still have the same problem and this field is in the table " targetdateentered

thanks steve

 

[rainbows]

thank you for all your help / it seems to be working now with the code Larry E sent