Form with calculation and Update event 1

[houstonbill]

The below SQL is the qry that makes up a form.

SELECT Date()-[SubDate] AS Age, tbl_Suggestions.SuggestionID, tbl_Suggestions.BadgeId, tbl_Suggestions.Idea, tbl_Suggestions.SuggestedAction,
tblEval.AckDate, tblEval.DecisionSent, tblEval.ClosedDate, tblEval.Decision, tblEval.ImpleDate
FROM tblEval RIGHT JOIN tbl_Suggestions ON tblEval.SuggID = tbl_Suggestions.SuggestionID;

What I want to do is have an event so that when the ClosedDate field is filled in it will stop the Age Date. I believe it will be an After Update event in the ClosedDate field that would read something like this:

Private Sub Closed_Date_AfterUpdate()
If Me![ClosedDate] = True Then Me![Age] = Number
End Sub

Will this stop the age based on the calculation I have set? I could wait until tomorrow to see if the number increases but thought I would verify if this event is formatted correctly now.

 

[dhookom]

You can't update an age field since it isn't stored anywhere. You could just change the calculation in the query to return varying values depending on the ClosedDate field. Is ClosedDate actually a yes/no field?

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]

 

[houstonbill]

Closed Date is the field name that will contain a date, and based on your question, I am sure that the after update event is incorrect now. How would I go about changing the query calculation as you indicated? That is a differnt twist on what I would normally do. Thanks.

 

[dhookom]

Can you describe what you would like to see in the "Age" column? We have no idea what you mean by "Number".

Define some values and rules and fields and columns for us.

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]

 

[lespaul]

maybe instead of:

SELECT Date()-[SubDate] AS Age

you can:

 SELECT IIF([ClosedDate] Is NotNull, [ClosedDate] - [SubDate], Date() - [SubDate]) As Age

Now, I know the Is NotNull part is wrong, but I can't remember the correct syntax for that, but should give you the basic idea.

HTH

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

 

[PHV]

Perhaps this ?
SELECT Nz([ClosedDate],Date())-[SubDate] As Age

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[houstonbill]

Thanks to everyone for your help. I used what PHV suggested and it does exactly what I need it to do. Just Perfect! Unfortunately, at my skill level I would not have thought to do it this way.