lil help for incoming bithday

Hi, i'm connected to a kronos db. I have 2 tbl.

tbl_Person
field_person_num
field_birthday_date

tbl_Employee
field_person_num
field_person_fullname
field_current_pay_Start(this is the current pay period start 05/10/16)
field_current_pay_End (this is the current pay period end, 05/10/22)


what i want to do is to have a report of my employee who have their
birhtday in the current pay period. the current pay period start on the
sunday and end's on saturday of each week. it auto update itself. or if
i could enter 2 date and have the employe who have their b-day in it
would be nice too.

date and b-day = yyyy/mm/dd (but i can change that in query)


i have a query with all those fields?
i dont know if i can do this in a query or do i have to do it in the
report. thx.

re: lil help for incoming bithday

NomoreSpam4Me@hotmail.com wrote:[color=blue]
> Hi, i'm connected to a kronos db. I have 2 tbl.
>
> tbl_Person
> field_person_num
> field_birthday_date
>
> tbl_Employee
> field_person_num
> field_person_fullname
> field_current_pay_Start(this is the current pay period start 05/10/16)
> field_current_pay_End (this is the current pay period end, 05/10/22)
>
>
> what i want to do is to have a report of my employee who have their
> birhtday in the current pay period. the current pay period start on the
> sunday and end's on saturday of each week. it auto update itself. or if
> i could enter 2 date and have the employe who have their b-day in it
> would be nice too.
>
> date and b-day = yyyy/mm/dd (but i can change that in query)
>
>
> i have a query with all those fields?
> i dont know if i can do this in a query or do i have to do it in the
> report. thx.
>[/color]

Have you tried
SELECT (fields)
FROM (join)
WHERE
BD >= Start AND BD <= End
or
BD BETWEEN Start AND End
?
--
Smartin

re: lil help for incoming bithday

yes i did, but the b-day of my employee r like 1956-12-01 ......any my
current pay period is 2005-10-10.... None of my enployee is born this
week (dont know if you understand), what cause me problem is the year.
I only need the month and the day. Its normal that when i enter my
formula like you wrote, it checks between 2005-10-10 and 2005-10-10. It
will check if i have an employee b-day between these 2 dates.

Is there a way to just look at the month and the day and forget about
the year?

re: lil help for incoming bithday

I would suggest a public function to return a true/false for use in
your query as it is not as simple as stripping off the year and
comparing the values. For most of the year, this isn't a problem, but
if you have a payperiod that straddles the end of the year (e.g. start
date is 12/29 and end date is 01/06), you'd end up with a mess on your
hands. Here's the function I came up with. It isn't pretty, but it
does the trick. Perhaps someone has a better suggestion?

Public Function IsBDay(StartDate As Date, EndDate As Date, _
BDate As Date) As Boolean
Dim StartYr, EndYr As Integer
Dim StartMo As Integer
StartYr = Year(StartDate)
EndYr = Year(EndDate)
StartMo = Month(StartDate)
If StartDate > EndDate Then
MsgBox "Your start date must be earlier than your end date!"
Exit Function
End If
'Determine proper year to add to birth day and month
If StartYr = EndYr Then
'Year stays the same, so just use the year of the start date
BDate = CDate(StartYr & "/" & Month(BDate) & "/" & Day(BDate))
Else
'Payperiod straddles over the end of the year,
'so decide which year to use
If Month(BDate) = StartMo Then
'BD is just before the end of the year
'so use StartDate's year
BDate = CDate(StartYr & "/" & Month(BDate) & "/" & Day(BDate))
Else
'BD is just after the end of the year
'so use EndDate's year
BDate = CDate(EndYr & "/" & Month(BDate) & "/" & Day(BDate))
End If
End If
If BDate >= StartDate And BDate <= EndDate Then
IsBDay = True
Else
IsBDay = False
End If
End Function

Use the function in your query, feeding the startdate, enddate and
birthdate fields into the function's arguments.

HTH,

Jana

re: lil help for incoming bithday

NomoreSpam4Me@hotmail.com wrote:[color=blue]
> yes i did, but the b-day of my employee r like 1956-12-01 ......any my
> current pay period is 2005-10-10.... None of my enployee is born this
> week (dont know if you understand), what cause me problem is the year.
> I only need the month and the day. Its normal that when i enter my
> formula like you wrote, it checks between 2005-10-10 and 2005-10-10. It
> will check if i have an employee b-day between these 2 dates.
>
> Is there a way to just look at the month and the day and forget about
> the year?
>[/color]

Oops silly me (^:

A little Googling reveals this ugly and untested potential solution:

SELECT * FROM MyTable WHERE
DateSerial(Year(PayStart),Month(Birthday),Day(Birt hday)) BETWEEN
DateSerial(Year(PayStart),Month(PayStart),Day(PayS tart)) AND
DateSerial(Year(PayEnd),Month(PayEnd),Day(PayEnd))
;

--
Smartin

re: lil help for incoming bithday

Smartin:

Appears that the solution you found ignores the year issue when pay
period straddles the end of the year. If Birthday is 06/01/02 and pay
start is 05/12/29 and pay end is 06/01/02, this would do the following:

Remove the BD year and replace it with 2005, changing BD to 05/01/02,
which is NOT between 05/12/29 and 06/01/06.

Otherwise, it works great when the year on the start & end dates are
the same :D

Jana

re: lil help for incoming bithday

Jana wrote:[color=blue]
> Smartin:
>
> Appears that the solution you found ignores the year issue when pay
> period straddles the end of the year. If Birthday is 06/01/02 and pay
> start is 05/12/29 and pay end is 06/01/02, this would do the following:
>
> Remove the BD year and replace it with 2005, changing BD to 05/01/02,
> which is NOT between 05/12/29 and 06/01/06.
>
> Otherwise, it works great when the year on the start & end dates are
> the same :D
>
> Jana
>[/color]

Right you are Jana. This should take care of the "year-end bug":

SELECT * FROM MyTable WHERE
(
DateSerial(Year(PayStart),Month(Birthday),Day(Birt hday)) BETWEEN
DateSerial(Year(PayStart),Month(PayStart),Day(PayS tart)) AND
DateSerial(Year(PayEnd),Month(PayEnd),Day(PayEnd))
)
OR
(
DateSerial(Year(PayEnd),Month(Birthday),Day(Birthd ay)) BETWEEN
DateSerial(Year(PayStart),Month(PayStart),Day(PayS tart)) AND
DateSerial(Year(PayEnd),Month(PayEnd),Day(PayEnd))
)
;

PS I like your VB solution too!
--
Smartin

re: lil help for incoming bithday

Smartin wrote:[color=blue]
> PS I like your VB solution too![/color]

Here's another VB solution for your entertainment.

Public Function boolBirthdayThisWorkWeek(dtBirth As Date, dtTimeTicket
As Date) As Boolean
Dim dtSundayStarting As Date
Dim dtSaturdayEnding As Date
Dim intAgeSaturday As Integer
Dim intAgeSunday As Integer

dtSundayStarting = DateAdd("d", 1 - WeekDay(dtTimeTicket),
dtTimeTicket)
dtSaturdayEnding = DateAdd("d", 6, dtSundayStarting)
intAgeSaturday = Int(Format(dtSaturdayEnding, "yyyy.mmdd") -
Format(dtBirth, "yyyy.mmdd"))
intAgeSunday = Int(Format(dtSundayStarting, "yyyy.mmdd") -
Format(dtBirth, "yyyy.mmdd"))
boolBirthdayThisWorkWeek = (intAgeSunday <> intAgeSaturday) Or
(Format(dtSundayStarting, "\.mmdd") = Format(dtBirth, "\.mmdd"))
End Function

James A. Fortune

re: lil help for incoming bithday

jimfortune@compumarc.com wrote:[color=blue]
> Smartin wrote:
>[color=green]
>>PS I like your VB solution too![/color]
>
>
> Here's another VB solution for your entertainment.
>
> Public Function boolBirthdayThisWorkWeek(dtBirth As Date, dtTimeTicket
> As Date) As Boolean
> Dim dtSundayStarting As Date
> Dim dtSaturdayEnding As Date
> Dim intAgeSaturday As Integer
> Dim intAgeSunday As Integer
>
> dtSundayStarting = DateAdd("d", 1 - WeekDay(dtTimeTicket),
> dtTimeTicket)
> dtSaturdayEnding = DateAdd("d", 6, dtSundayStarting)
> intAgeSaturday = Int(Format(dtSaturdayEnding, "yyyy.mmdd") -
> Format(dtBirth, "yyyy.mmdd"))
> intAgeSunday = Int(Format(dtSundayStarting, "yyyy.mmdd") -
> Format(dtBirth, "yyyy.mmdd"))
> boolBirthdayThisWorkWeek = (intAgeSunday <> intAgeSaturday) Or
> (Format(dtSundayStarting, "\.mmdd") = Format(dtBirth, "\.mmdd"))
> End Function
>
> James A. Fortune
>[/color]

I'm not clear on what should be passed for dtTimeTicket?

When I evaluate this function against an arbitrary DOB and any fixed
dtTimeTicket, it only returns true for dtTimeTicket = the last 7 days of
the year.
--
Smartin

re: lil help for incoming bithday

Smartin wrote:[color=blue]
> jimfortune@compumarc.com wrote:[color=green]
> > Smartin wrote:
> >[color=darkred]
> >>PS I like your VB solution too![/color]
> >
> >
> > Here's another VB solution for your entertainment.
> >
> > Public Function boolBirthdayThisWorkWeek(dtBirth As Date, dtTimeTicket
> > As Date) As Boolean
> > Dim dtSundayStarting As Date
> > Dim dtSaturdayEnding As Date
> > Dim intAgeSaturday As Integer
> > Dim intAgeSunday As Integer
> >
> > dtSundayStarting = DateAdd("d", 1 - WeekDay(dtTimeTicket),
> > dtTimeTicket)
> > dtSaturdayEnding = DateAdd("d", 6, dtSundayStarting)
> > intAgeSaturday = Int(Format(dtSaturdayEnding, "yyyy.mmdd") -
> > Format(dtBirth, "yyyy.mmdd"))
> > intAgeSunday = Int(Format(dtSundayStarting, "yyyy.mmdd") -
> > Format(dtBirth, "yyyy.mmdd"))
> > boolBirthdayThisWorkWeek = (intAgeSunday <> intAgeSaturday) Or
> > (Format(dtSundayStarting, "\.mmdd") = Format(dtBirth, "\.mmdd"))
> > End Function
> >
> > James A. Fortune
> >[/color]
>
> I'm not clear on what should be passed for dtTimeTicket?
>
> When I evaluate this function against an arbitrary DOB and any fixed
> dtTimeTicket, it only returns true for dtTimeTicket = the last 7 days of
> the year.
> --
> Smartin[/color]

I envisioned dtTimeTicket to be any date during the work week, usually
the same as Date(). That way someone entering a time ticket for an
employee can be shown the information. The function calculates the
start and end of the work week based on that date. I tested this
function enough to be reasonably sure of its accuracy. Let me know if
you still have problems with it after supplying a different date for
dtTimeTicket. Perhaps the CDate function or #'s are required.

James A. Fortune

re: lil help for incoming bithday

hehehe, u lost me.

i'm a bit confuse about the DateSerial function and there is not a lot
on it in the help file.

can any1 help me translation it.

i'll give it a try for the moment.

thx

re: lil help for incoming bithday

NomoreSpam4Me@hotmail.com wrote:[color=blue]
> i'm a bit confuse about the DateSerial function and there is not a lot
> on it in the help file.[/color]

Douglas Steele straightened me out on this one:

http://groups.google.com/group/comp....3acc8e2?hl=en&

In programming, expanding the solution to a specific problem to be able
to handle more general situations is known as abstraction. In general,
extra care has to be taken when doing in this direction.

James A. Fortune

re: lil help for incoming bithday

Everyone:

Just goes to prove the old addage that there's more than one way to
skin a cat...

Smartin, I must admit that I never thought of adding in the OR
part...LOL

Hope that the OP found something that works for them...

Jana

re: lil help for incoming bithday

NomoreSpam4Me@hotmail.com wrote:[color=blue]
> hehehe, u lost me.
>
> i'm a bit confuse about the DateSerial function and there is not a lot
> on it in the help file.
>
> can any1 help me translation it.
>
> i'll give it a try for the moment.
>
> thx[/color]

I changed this to a SQL statement using the same table and field names
as Smartin for ease of comparison.

SELECT Int(Format(PayEnd, 'yyyy.mmdd') - Format(Birthday, 'yyyy.mmdd'))
AS AgeSaturday, Int(Format(PayStart, 'yyyy.mmdd') - Format(Birthday,
'yyyy.mmdd')) AS AgeSunday, ([AgeSunday] <> [AgeSaturday]) Or
(Format(PayStart, "\.mmdd") = Format(Birthday, "\.mmdd")) As
BirthdayThisWorkWeek FROM MyTable;

You can also substitute and use the less didactic:

SELECT (Int(Format(PayStart, 'yyyy.mmdd') - Format(Birthday,
'yyyy.mmdd')) <> Int(Format(PayEnd, 'yyyy.mmdd') - Format(Birthday,
'yyyy.mmdd'))) Or (Format(PayStart, "\.mmdd") = Format(Birthday,
"\.mmdd")) As BirthdayThisWorkWeek FROM MyTable;

I hope this is clearer,

James A. Fortune

re: lil help for incoming bithday

yes a lot, thx , i'll work my way out with it.