Setting a field's default value based on another field

[Spydermine]

Hi,

I want to base the value of one field (PAReviewDate) from another field (AssessmentDate). Where PAReviewDate would be AssessmentDate + 3 months. AssessmentDate already has values in Date/Time Short Date format. I've tried tinkering with DefaultValue property for the PAReviewDate field but haven't gotten any results.

Thanks in advance.

 

[Remou]

Have you looked at DateAdd? What have you tried?

 

[Spydermine]

DateAdd («interval», «number», «date»)

I'm not sure what values it wants in the first two parameters. <<date>> I replaced with AssessmentDate.

DateAdd («interval», «number», "AssessmentDate")

I've put random numbers into interval and number but it wouldn't allow me to save the table design.
"Error: Type mismatch in defaultvalue property" Something along those lines.

 

[Remou]

I assume that AssessmentDate is a date?

[tt]DateAdd ("m",3,AssessmentDate)[/tt]

 

[Spydermine]

Yes AssessmentDate is a date/time field with short date format.

When I enter
DateAdd("m",3,AssessmentDate)
into the defaultvalue property, access autochanges it to
DateAdd("m",3,"AssessmentDate")
Not sure if that's an issue, but i'm just saying in case.

It still presents the error when trying to save
"Error 'Type Mismatch' in the default value"

I've made sure that PAReviewDate and AssessmentDate have the same format, but to no avail >_>

 

[dhookom]

The default value property won't have any effect with a record unless the record is brand new. A brand new record doesn't have an AssessmentDate yet so trying to set the default will be worthless.

If you want to store a value in a field/control that is 3 months later an existing date, you may need to add code to the After Update event of the AssessmentDate control like:

   Me.txtPAReviewDate = DateAdd("m",3,Me.txtAssessmentDate)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Spydermine]

Thanks for the help you two. I haven't tried that last post but it sounds correct. I would need to make an entry form for them as opposed to entering it straight to the table, but that's fine. I had hoped the AssessmentDate field had a After Update property, oh well.

 

[lespaul]

I would need to make an entry form for them as opposed to entering it straight to the table,

You ALWAYS want to make an entry form and any other form required to work with the tables.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins