Cancelling a Age Calculation query for one record

[NickDenyer]

Dear All,

I require your vast, collective intellects!

I have the following age calculation query:

Age: (Now()-[DOB])\365

It works a treat! However, I do not want this to continue to caluclate if the record has them as deceased - I want it to stop at their date of death.

I have a tick box that when selected indicates that this record has died, and a field where you can enter date of death.

Is there some way that via clicking this button, or by entering a date of death, I can stop the Age Query from calculating for just that relevant record, not all of them? If so, could you please show me where to place the neccessary VBA, etc? I can't think of a solution!

KINDEST THANKS!

 

[pr2-eugin]

Nick, Try..

Age: IIF([COLOR=Blue][deceased][/COLOR] = -1, "", (DateDiff("d",[DOB],Date()))\365)

Where [deceased] would be the Tick box which denotes if the person has passed away.. Checking it with -1 says the person is Dead, so we place an empty string.. If it is False (or not ticked) then do the Calculation..

 

[NickDenyer]

Hi pr2-eugin, long time no speak!

I have tried the following, (with one change - "Died" in place of "deceased" as per my control name):

Age: IIF([Died] = -1, "", (DateDiff("d",[DOB],Date()))\365)

and it did not work I then tried changing "Died" to "Check136" (the checkbox - bad programming name I know!) and this just asked for a parameter. Any thoughts?

Cheers,

Nick

 

[pr2-eugin]

Okay is this on a Form or you trying this on a Query? I thought it was a Query.. If it is a Form, is it a single form or continuous? If it is a single form, then try the Form current event..

Private Form_Current()
    If Me.Check136 Then
        Me.AgeControlName = vbNullString
    Else
        Me.AgeControlName = DateDiff("d",[DOB],Date)\365
    End If
End Sub

 

[Keith Tedbury]

Hi

Is Died on the same table as the other fields? and with trying to reference the checkbox sounds like either the form isn't open or your not calling it properly forms!formname!checkbox

 

[Pat Hartman]

and it did not work

What does that mean? Did Access shut down? Did you get an error message? What was the message? Did the field show up empty? Did Access spit in your eye?

If Died is actually the date the person died, which would be much more informative as data than a boolean, you can leave his age as static this way.

Age: IIF(IsDate([Died]) = True, (DateDiff("d",[DOB],Died))\365, (DateDiff("d",[DOB],Date()))\365)

 

[NickDenyer]

Pat,

Thank you very much! This:

Age: IIF(IsDate([Died]) = True, (DateDiff("d",[DOB],Died))\365, (DateDiff("d",[DOB],Date()))\365)

solved my problem it works perfectly! And sorry for not being specific.

Pr2-Eugin - it was on a query, yes, sorry for not specifying - though I've kept your original formula advice just in case I ever need to do this on a form

Keith - thank you also for your advice - I feel more confident with calculations now

Thank you one and all!

Nick

 

[gemma-the-husky]

surely you are better with datediff("y" ....) rather than dividing days by 365

it is conceivable that dividing by 365 might generate an error in some cases.

 

[Pat Hartman]

I also forgot to mention that the calculation you are using isn't really accurate. If you want something more accurate, find the date sample database I loaded in the samples section.