Replacing date field after updating different field

[Wind Dancer]

I can't seem to get my date field to update to today when I add information to another field.



My table has a lookup field, date field, text field, text field, text field.

I have set the date field to default to today for each new record.

I work with this table as a subform.

I would like to have the date field update to the date I go back to the record and update the first text field.

I have tried using after update, and an if - then statement, but nothing seems to work.

If someone could help that would be great.

[June7]

Show the code you attempted. Why doesn't it work, what happens - error message, wrong results, nothing?

[Wind Dancer]

This is my last attempt

Private Sub Date_Paid_AfterUpdate()

If [Check_No] > 0 Then
[Date_Paid] = Now()
Else
If [Check_No] < 0 Then
[Date_Paid] = [Date_Paid]
End If
End Sub

and nothing happens

[orange]

What does this mean - exactly

My table has a lookup field, date field, text field, text field, text field.

If you have a lookup field in a table definition, I suggest you remove it. Make a separate Lookup table.

It isn't clear what this does.

Private Sub Date_Paid_AfterUpdate()

 If [Check_No] > 0 Then  'if greater than 0
       [Date_Paid] = Now()
     Else
 If [Check_No] < 0 Then    'if less than 0
       [Date_Paid] = [Date_Paid]
     End If
End Sub

and nothing happens

What do you expect to happen?
What if the check_no equals 0?

I'm not sure I understand the reason for the ElseIF.

[June7]

Shouldn't that be in the AfterUpdate event of Check_No?

How could a check number ever be less than 0? Don't allow that.

What should happen if check number is exactly 0?

Have you run Debug > Compile? That code is missing an End If line.

The Else and second If structure is not necessary.

[Wind Dancer]

So you only need to tell it what to do if there is a check no in that field?
I was think like excel where you need both if true and false
I don't want the date changed until there is a check no filled in.
The check no field is text, is that the problem?
I will try it with out the else, and put it in the check no after update and see what happens.

[Wind Dancer]

I thought I had the solution, but now I am getting a runtime error 3163 when it tries to update the date.
Is there a way to make sure it is only using a short date?

[June7]

[Date_Paid] = Date()

But that probably won't change the error.

What is the exact message of the error?

[orange]

What is the data type of Date_Field? Is it Date data type?

As for the IF you could try this sort of thing:

Private Sub Date_Paid_AfterUpdate()

If CDbl([Check_No]) > 0 Then 'if greater than 0
[Date_Paid] = Now()
Else
[Date_Paid] = [Date_Paid]
End If
End Sub

[Wind Dancer]

The field is too small to accept the amount of data you attempted to add
Try inserting or pasting less data

Just tried the last suggestion and that did it.

thanks to everyone who helped!!!

[June7]

The field is a text data type? Make it a date/time type.