Sql Notify whith no of days left when days passed from a date (1 Viewer)

[Manos39]

Hello
Trying to have a query that its records warn when date [RegDay] of a person [athleteID] comes as 30 days or less to complete 1 year passed from then to Date() , and if so in the calculation field [RenewReg], indicate if minus 30days, the days left to complete one year, if passed year since [RegDay] to show "Missed Reg"
Tryied in the logic by mind
RenewReg: Iif(Datediff "d", Date()-[RegDay]<=30,"DueReg", "MissedReg")
But how in that logic if true show days left to complete one year (365) days??

 

[The_Doc_Man]

If both fields are dates, then CLng( later-date - earlier-date) will be the number of days between the two dates, particularly if both dates are dates without times. If so, this will be accurate. Then you have the number and can compare it to your time limits.

 

[Manos39]

Could your suggestion be accomplished in a single calculation field?

 

[The_Doc_Man]

One computation will produce one result. You appear to be asking for two different numbers or two different warnings in your original question, so I would say "NO" - but if you think I have misunderstood your question, you might try to state it differently.

 

[Manos39]

One computation will produce one result. You appear to be asking for two different numbers or two different warnings in your original question, so I would say "NO" - but if you think I have misunderstood your question, you might try to state it differently.

All i imply is can the above true part of the Iif be differently calculated to show days left to one year from [RegDay]
Excuse my English

 

[Isaac]

@Manos39 your syntax is wrong, you're missing the last parenthesis in Datediff. Actually come to look at it closer you're missing ALL parenthesis for DateDiff. Get the syntax right and it may work

 

[Manos39]

Isaac i wrote it by mind to explain what i have in mind and i am asking whether some calculation inside the true part in above field could show instead of a simple "DueReq" indication of days as from -30 or -29 etc to 0 coming to fulfil whole one year since [RegDay]

 

[Manos39]

Maybe in the true part, i should nest a second Datediff

 

[The_Doc_Man]

Perhaps that would be correct. To save yourself some headaches, for test purposes write out the two DateDiffs separately just to see that you have the syntax right, then go ahead and edit those two date-diff expressions into your IIF. There is no rule against having both sides of an IIF as a function as long as you get each function right.

 

[arnelgp]

check Query1 if this is what you intend.

 

[Manos39]

check Query1 if this is what you intend.

check Query1 if this is what you intend.

check Query1 if this is what you intend.

I ll reply as soon as i can thank you its close

 

[Manos39]

I wanted to accomplish a query for the case: " Every athlete needs to renew Fed's Registration Id, (annually every year), and user should be informed
that it is due to renew 30 days before completing a whole year (since [FedRegDay]) - because whitout renew athlete cant compete in races-
I was trying in the logic
a) If Date()) - [FedRegDate] < 365 - MISSED (passed 1 year sinse athlete's registration)
b) If Date()) - [FedRegDate] Between 335 And 365 - ReIssue (for warning)
c) If Date()) - [FedRegDate] > 366 (then show days till 335)

What i did was :

RegDue: IIf(DateDiff("d";[FedRegDate];Date())>=365;"Missed";IIf(DateDiff("d";[FedRegDate];Date()) Between 335 And 365;"DueReg";"days to Reissue" & " = " & DateDiff("d";[FedRegDate];Date())))

Which works fine i suppose, although i wanted ("days to Reissue" & " = " & DateDiff("d";[FedRegDate];Date()) the other way round eg 180 days to reissue (which i cant produce)