CalculatedField - Days Till Birthdate - Form, Orderby, query

[Guest]

hey


So... i have this continous form, based on a query.
On the form are a few fields like, [name], [birthdate]..etc.

I wanted to display next to each name the number of days
until that persons birthday So, i created a calculated field called
"DaysLeft" , and
slapped this into the control source;


=IIf(IsNull([Birthdate]),"",IIf((DateDiff("d",Format(Now(),"mmmm
dd"),Format([Birthdate],"mmmm dd")))<0,(DateDiff("d",Format(Now(),"mmmm
dd"),Format([Birthdate],"mmmm dd")))+365,DateDiff("d",Format(Now(),"mmmm
dd"),Format([Birthdate],"mmmm dd"))))


First, i wanted to check with you guys to see if its correct - it seems to
work fine, though im not sure how deadly accurate the result is with the
"365" as part of the calculation.. Is there an easier way to do it?

Secondly, i wanted to sort the continous form according to that calculated
field,"DaysLeft", and from a previous post here i gather thats impossible.
That, rather, i should create the calculated field in the underlying query
which makes up the form before i can sort using it.

And when i say sort, I guess i mean ORDERBY - there is no other command that
will sort, right?

Best regards,
WebDude


btw: 18 days left until MY birthday! wooooo. (not that im counting ) lol

 

[tina]

well, if you want complete accuracy, i wouldn't use a hardcoded value like
365 - the little issue of leap year, you know. here's one alternate
solution:

you can return the number of days before the person's *next upcoming*
birthday by using the following function, either as a calculated field in a
query
DaysLeft: isDayCount([birthdate])
or in the ControlSource of an unbound control in the form
=isDayCount([birthdate])


Public Function isDayCount(ByVal datBirthdate _
As Date) As Integer

Dim datThisYear As Date

datThisYear = DateSerial(Year(Date), _
Month(datBirthdate), Day(datBirthdate))

If datThisYear >= Date Then
isDayCount = DateDiff("d", Date, datThisYear)
Else
isDayCount = DateDiff("d", datThisYear, _
DateAdd("yyyy", 1, Date))
End If

End Function

just paste the function into a public module (you can name the module
anything *except* the same name as the function).

hth

 

[Guest]

Hi Tina

Thanks for replying to my message!!

I...um.. didnt exactly understand most of the syntax
of the function you suggested lol however i followed your
lead and created a function of my own!

I placed a call to my function from the control source
of a text box(which is on a continous form);

=MyFunction([Birthdate])

The [Birthdate] field is part of the query which is the continous forms
RecordSource. And everything works fine. )

However, when there is no date in the [Birthdate] field then the text box
which calls myFunction displays an " #Error ". ANd So i assume my function is
not handeling an empty/null/ZeroLength [Birthdate] field properly?



Function DaysUntilBirthday(varDate As String)
If IsEmpty(varDate) Then
DaysUntilBirthday = " "
Else
DaysUntilBirthday = DateDiff("d", Format(Now(), "mmmm dd"),
Format(varDate, "mmmm dd"))

If DaysUntilBirthday < 0 Then
DaysUntilBirthday = DaysUntilBirthday + 365
End If
End If
End Function







I also replaced "If IsEmpty(varDate) then" with;

If IsNull(varDate) then...
If Len(varDate) < 1 then..
If vardate = "" then...

but nothing worked :-(



Any ideas?
Cheers,
WebDude out!

 

[tina]

well, your function uses a hard value of 365, which leaves you with the same
problem as your originally posted IIf() function - leap year will cause an
inaccurate return value.

did you paste my function into a module, and step through it to see exactly
what it's doing? in a nutshell:

it takes the Birthdate value and converts it into the same date in the
current year (example: DOB 3/21/74 becomes 3/21/05), and stores it in
variable datThisYear. now you have "this year's" birthday date.

this year's birthday is either today, or in the future of today, or in the
past of today. so you need to handle those three conditions, to make sure
the function always returns the number of days until the *next* birthday.
the function uses the If statement to handle the conditions.

the If statement says:
If datThisYear is "equal to or in the future of" today's date,
Then compare the two dates and return the number of days until this year's
birthday,
Else, compare datThisYear to "today's date + 1 year" and return the number
of days until the "next" birthday.

so if you run the function using your birthdate, and your birthday happens
to be today, the function returns a zero (0). if your birthday is tomorrow,
it returns 1. if your birthday was yesterday, it returns 364 or 365 -
depending on whether or not a leap year figures into the calculations.

to handle the issue of the Birthdate field being Null, i would probably not
call the function at all when it's Null, as

in a query:
DaysLeft: IIf([birthdate] Is Null, Null, isDayCount([birthdate]))
or in the ControlSource of an unbound control in the form:
=IIf([birthdate] Is Null, Null, isDayCount([birthdate]))

hth

Hi Tina

Thanks for replying to my message!!

I...um.. didnt exactly understand most of the syntax
of the function you suggested lol however i followed your
lead and created a function of my own!

I placed a call to my function from the control source
of a text box(which is on a continous form);

=MyFunction([Birthdate])

The [Birthdate] field is part of the query which is the continous forms
RecordSource. And everything works fine. )

However, when there is no date in the [Birthdate] field then the text box
which calls myFunction displays an " #Error ". ANd So i assume my function is
not handeling an empty/null/ZeroLength [Birthdate] field properly?



Function DaysUntilBirthday(varDate As String)
If IsEmpty(varDate) Then
DaysUntilBirthday = " "
Else
DaysUntilBirthday = DateDiff("d", Format(Now(), "mmmm dd"),
Format(varDate, "mmmm dd"))

If DaysUntilBirthday < 0 Then
DaysUntilBirthday = DaysUntilBirthday + 365
End If
End If
End Function







I also replaced "If IsEmpty(varDate) then" with;

If IsNull(varDate) then...
If Len(varDate) < 1 then..
If vardate = "" then...

but nothing worked :-(



Any ideas?
Cheers,
WebDude out!

 

[Guest]

Hello

..your function uses a hard value of 365, which leaves you with the same
problem as your originally posted IIf() function - leap year will cause an
inaccurate return value.

True. I havent forgotten.. i just, well, i was going to add a leap year
clause to my function later. cause Browsing this newsgroup i found a few good
ones like;


IsALeapYear = (Month(DateSerial(YearNum,2,29))=2)
IsLeapYear = (Day(DateSerial(iYear, 2, 29) = 29)
IsALeapYear = (Day(DateSerial(YearNum,3,0))=29)
fLeapYear = (y mod 4)=0 and ((y mod 100)>0 or (y mod 400)=0)
IsALeapYear = (DatePart("y",DateSerial(YearNum,12,31))=366)

:-D

did you paste my function into a module, and step through it to see exactly
what it's doing?

No. :-( Sadly i didnt know you could do that..Step through a
procedure..cool!! ill give it a try with all me procedures lol just to
see what goes on behind the scenes :-O

to handle the issue of the Birthdate field being Null, i would probably not
call the function at all when it's Null... in a query:
DaysLeft: IIf([birthdate] Is Null, Null, isDayCount([birthdate]))

Yes, i did exactly that and it worked like a charm!

Thanks Tina for ALL YOUR HELP
and your patience. )

Im not the sharpest pencil in the case
Cheers,
Weeeebduuuuuude awaaaay!

 

[tina]

you're welcome

Hello

..your function uses a hard value of 365, which leaves you with the same
problem as your originally posted IIf() function - leap year will cause an
inaccurate return value.

True. I havent forgotten.. i just, well, i was going to add a leap year
clause to my function later. cause Browsing this newsgroup i found a few good
ones like;


IsALeapYear = (Month(DateSerial(YearNum,2,29))=2)
IsLeapYear = (Day(DateSerial(iYear, 2, 29) = 29)
IsALeapYear = (Day(DateSerial(YearNum,3,0))=29)
fLeapYear = (y mod 4)=0 and ((y mod 100)>0 or (y mod 400)=0)
IsALeapYear = (DatePart("y",DateSerial(YearNum,12,31))=366)

:-D

did you paste my function into a module, and step through it to see exactly
what it's doing?

No. :-( Sadly i didnt know you could do that..Step through a
procedure..cool!! ill give it a try with all me procedures lol just to
see what goes on behind the scenes :-O

to handle the issue of the Birthdate field being Null, i would probably not
call the function at all when it's Null... in a query:
DaysLeft: IIf([birthdate] Is Null, Null, isDayCount([birthdate]))

Yes, i did exactly that and it worked like a charm!

Thanks Tina for ALL YOUR HELP
and your patience. )

Im not the sharpest pencil in the case
Cheers,
Weeeebduuuuuude awaaaay!