update query difficulty

[gemma-the-husky]

i must confess i struggle to understand the above expression

how does a > get in there?, and resolve correctly (towards the end)

 

[DCrake]

Brian

It was not a criticism, it was an observation. Sometimes I might be susceptable to standing on the backs of giants, but in this case I was more intregued by the logic than the coding. And as I pointed out, the sql was visible only in an image and as such was not extractable to be able to play with.

There are many versions on this forum on how to correctly calculate a persons age, although you would expect the contrary.

If this was my issue I would have used a function to calculate the persons age instread of using the datediff() directly in the query. Then I would have filtered the results. However I was not the first responder, and as a rapore has been established between the OP and yourself I did not want to intrude.

David

 

[Brianwarnock]

i must confess i struggle to understand the above expression

how does a > get in there?, and resolve correctly (towards the end)

I have been critizised , mildly, by Namliam for this approach on the thread titled Datediff as it is not obviously clear, this is not helped by my typos, caused in part by frustration with repeated attempts to use the forum in the last few days. Namliam uses a second IIf and tests on Format of the respective MMDD.

However how does this work, I had hoped that I had explained before but to try again

DateDiff("yyyy",#04/06/1955#,Date())+(DateSerial(Year(Date()),Month(#04/06/1955#),Day(#04/06/1955#))>Date())

DateDiff("yyyy",#04/06/1955#,Date())

This obviously calculates the difference in years, we now need to readjust if a birthday has not been had.

(DateSerial(Year(Date()),Month(#04/06/1955#),Day(#04/06/1955#))>Date())


The date serial part calculates the birthday for the current year and this is then compared for todays date, if the birthday is greater then the expression returns -1 as it is TRUE, if birtdate is <= to date a birthaday has been had and a 0 (False) is returned, and thus the number of years is now correct. Namlian also points out that this is not the case in all DB systems but I'm only discussind ACCESS here so don't care, but accept his point.

BTW sorry if I laboured a few points but I am not a qualified teacher.

Brian