DOB

[Ana]

Hi,

In a table I've EmpID, DOB, EmpName among other fields.

I'd like to have at start-up of my project a msg which displays: 'Hi, today's
Bob's bday' base on

IIF (DATEDIFF(D;DOB;NOW()) = 0;EmpName;'')

How to tune my idea into a code?

TIA

Ana



--------------------------------------------------------------------------------

Estoy utilizando la versión gratuita de SPAMfighter para usuarios privados.
Ha eliminado 2592 correos spam hasta la fecha.
Los abonados no tienen este mensaje en sus correos.
¡Pruebe SPAMfighter gratis ya!

 

[Ana]

PS

Instead of 'Hi', how about Good morning.Good afternoon. Good evening based
on local time?

 

[Guest]

Ana:

Paste the following function into a standard module in your database:

Public Function IsBirthday(intMonth As Integer, _
intDay As Integer, _
Optional varDateAt As Variant) As Boolean

Dim n As Integer

' set current date as default
If IsMissing(varDateAt) Then
varDateAt = VBA.Date
End If

' is birthday on 29 Feb
If intMonth = 2 And intDay = 29 Then
' if current year not a leap year make birthday 28 Feb
If Day(DateSerial(Year(varDateAt), 2, 29)) <> 29 Then
n = 1
End If
End If

IsBirthday = (DateSerial(Year(varDateAt), intMonth, intDay - n) =
varDateAt)

End Function

Lets assume evening starts at 6.00 PM. The code would need to loop through
the table checking each employee's date of birth. If its someone's birthday
today, then it need to check what time of day it is, so the code would go
like this:

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strMessage As String
Dim strTimeOfDay AsString

strSQL = "SELECT EmpName, DOB FROM Employees"

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:=strSQL, _
CursorType:=adOpenForwardOnly

With rst
Do While Not .EOF
' is today the employee's birthday
If IsBirthday(Month(.Fields("DOB"),Day(.Fields("DOB")) Then
' what time of day is it
Select Case Hour(VBA.Date)
Case Is < 12
strTimeOfDay = "morning"
Case 12 To 17
strTimeOfDay = "afternoon"
Case Else
strTimeOfDay = "evening"
End Select

strMessage = " Good " & strTimeOfDay & ", today's " & _
.Fields("EmpName") & "'s birthday!"

MsgBox strMessage, vbExclamation, "Happy Birthday"

.MoveNext
Loop
End With

Ken Sheridan
Stafford, England

 

[Ana]

Wow. Will give it a try. Thank you.

Ana:

Paste the following function into a standard module in your database:

Public Function IsBirthday(intMonth As Integer, _
intDay As Integer, _
Optional varDateAt As Variant) As Boolean

Dim n As Integer

' set current date as default
If IsMissing(varDateAt) Then
varDateAt = VBA.Date
End If

' is birthday on 29 Feb
If intMonth = 2 And intDay = 29 Then
' if current year not a leap year make birthday 28 Feb
If Day(DateSerial(Year(varDateAt), 2, 29)) <> 29 Then
n = 1
End If
End If

IsBirthday = (DateSerial(Year(varDateAt), intMonth, intDay - n) =
varDateAt)

End Function

Lets assume evening starts at 6.00 PM. The code would need to loop
through
the table checking each employee's date of birth. If its someone's
birthday
today, then it need to check what time of day it is, so the code would go
like this:

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strMessage As String
Dim strTimeOfDay AsString

strSQL = "SELECT EmpName, DOB FROM Employees"

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:=strSQL, _
CursorType:=adOpenForwardOnly

With rst
Do While Not .EOF
' is today the employee's birthday
If IsBirthday(Month(.Fields("DOB"),Day(.Fields("DOB")) Then
' what time of day is it
Select Case Hour(VBA.Date)
Case Is < 12
strTimeOfDay = "morning"
Case 12 To 17
strTimeOfDay = "afternoon"
Case Else
strTimeOfDay = "evening"
End Select

strMessage = " Good " & strTimeOfDay & ", today's " & _
.Fields("EmpName") & "'s birthday!"

MsgBox strMessage, vbExclamation, "Happy Birthday"

.MoveNext
Loop
End With

Ken Sheridan
Stafford, England

 

[Jamie Collins]

The code would need to loop through
the table checking each employee's date of birth.

"Need to"? I strongly disagree. Access/Jet is a SQL product and a
set-based solution (as opposed to procedural code) is preferred e.g.

SELECT 'Good ' & SWITCH(
DATEPART('H', NOW()) BETWEEN 0 AND 11, 'morning',
DATEPART('H', NOW()) BETWEEN 12 AND 17, 'afternoon',
DATEPART('H', NOW()) BETWEEN 18 AND 23, 'evening'
) & ', today''s ' & EmpName & '''s birthday!' AS birthday_message
FROM Employees
WHERE DATEPART('M', DOB) = DATEPART('M', NOW())
AND
IIF(DATEPART('M', DOB) = 2 AND DATEPART('D', DOB) = 29, 28,
DATEPART('D', DOB))
=
IIF(DATEPART('M', NOW()) = 2 AND DATEPART('D', NOW()) = 29, 28,
DATEPART('D', NOW()));

Jamie.

--

 

[Ana]

Thank you Ken,
Some employees have no info in the DOB field, thas, null.
So, the line:
****If IsBirthday(Month(.Fields("DOB")), Day(.Fields("DOB"))) Then****
halts.

 

[Ana]

Hello Jaime,

I like your approach also and I was wondering if you can code it in VB for
an adp file.

TIA

Ana

 

[Douglas J. Steele]

With rst
Do While Not .EOF
If IsNull(.Fields("DOB")) = False Then
' is today the employee's birthday
If IsBirthday(Month(.Fields("DOB"),Day(.Fields("DOB")) Then
' what time of day is it
Select Case Hour(VBA.Date)
Case Is < 12
strTimeOfDay = "morning"
Case 12 To 17
strTimeOfDay = "afternoon"
Case Else
strTimeOfDay = "evening"
End Select
strMessage = " Good " & strTimeOfDay & ", today's " & _
.Fields("EmpName") & "'s birthday!"
MsgBox strMessage, vbExclamation, "Happy Birthday"
.MoveNext
End If
End If
Loop
End With

 

[Jamie Collins]

I like your approach also and I was wondering if you can code it in VB for
an adp file.

Certainly not <g>!

If your target engine is SQL Server, here's the T-SQL equivalent:

SELECT 'Good ' + CASE
WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) BETWEEN 0 AND 11 THEN
'morning'
WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) BETWEEN 12 AND 17 THEN
'afternoon'
WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) BETWEEN 18 AND 23 THEN
'evening'
END + ', today''s ' + EmpName + '''s birthday!' AS birthday_message
FROM Employees
WHERE DATEPART(M, DOB) = DATEPART(M, CURRENT_TIMESTAMP)
AND
CASE WHEN DATEPART(M, DOB) = 2 AND DATEPART(D, DOB) = 29 THEN 28 ELSE
DATEPART(D, DOB) END
=
CASE WHEN DATEPART(M, CURRENT_TIMESTAMP) = 2 AND DATEPART(D,
CURRENT_TIMESTAMP) = 29 THEN 28 ELSE DATEPART(D, CURRENT_TIMESTAMP)
END;

Jamie.

--

 

[Ana]

Thank you Douglas,

I had to change the line
***If IsBirthday(Month(.Fields("DOB"),Day(.Fields("DOB")) Then***
to
***If IsBirthday(Month(.Fields("DOB")), Day(.Fields("DOB"))) Then*** because
it was causing problems
***VBA.date to VBA.time
and
***.MoveNext End If End If Loop
to
***End If End If .MoveNext Loop
otherwise the form would loop and not start.
Ana

 

[Ana]

Thank you Jaime,

This' what I've:
Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String

strSQL = "SELECT 'Good ' + CASE " _
& "WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) BETWEEN 0 AND 11 THEN
'morning'" _
& "WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) BETWEEN 12 AND 17 THEN
'afternoon'" _
& "WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) BETWEEN 18 AND 23 THEN
'evening'" _
& "END + ', today''s ' + EmpName + '''s birthday!' AS birthday_message"
_
& "FROM Employees" _
& "WHERE DatePart(M, DOB) = DatePart(M, CURRENT_TIMESTAMP)AND" _
& "CASE WHEN DATEPART(M, DOB) = 2 AND DATEPART(D, DOB) = 29 THEN 28 ELSE" _
& "DATEPART(D, DOB) END =" _
& "CASE WHEN DATEPART(M, CURRENT_TIMESTAMP) = 2 AND DATEPART(D," _
& "CURRENT_TIMESTAMP) = 29 THEN 28 ELSE DATEPART(D, CURRENT_TIMESTAMP)End"

End Sub

But it won't work nor generates errors in access. However, it does work when
using the SQL analyzer.
Ana

 

[Douglas J. Steele]

I don't see any difference between the 1st and 2nd lines.

If the Date function is causing a problem, check your References. While in
the VB Editor, go to Tools | References and examine all of the selected
references (they'll all be at the top of the list).

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

If you're still having a problem after that, make sure you haven't named
anything in your application Date: no tables, fields in tables, forms,
controls on forms, functions, variables, constants, etc. Date is a reserved
word, and using reserved words for your own purposes can lead to problems.
For a comprehensive list of reserved words, check what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Jamie Collins]

This' what I've:
Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String

strSQL = "SELECT 'Good ' + CASE " _
& "WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) BETWEEN 0 AND 11 THEN
'morning'" _
& "WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) BETWEEN 12 AND 17 THEN
'afternoon'" _
& "WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) BETWEEN 18 AND 23 THEN
'evening'" _
& "END + ', today''s ' + EmpName + '''s birthday!' AS birthday_message"
_
& "FROM Employees" _
& "WHERE DatePart(M, DOB) = DatePart(M, CURRENT_TIMESTAMP)AND" _
& "CASE WHEN DATEPART(M, DOB) = 2 AND DATEPART(D, DOB) = 29 THEN 28 ELSE" _
& "DATEPART(D, DOB) END =" _
& "CASE WHEN DATEPART(M, CURRENT_TIMESTAMP) = 2 AND DATEPART(D," _
& "CURRENT_TIMESTAMP) = 29 THEN 28 ELSE DATEPART(D, CURRENT_TIMESTAMP)End"

End Sub

But it won't work nor generates errors in access. However, it does work when
using the SQL analyzer.

Your code merely populates a String variable; I'm not surprised it
"won't work nor generates errors" because you have done nothing with
it <g>! Hint: there may be a RecordSource property to set or
Connection.Execute method to invoke, etc.

Jamie.

--