DOB

A

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!
 
A

Ana

PS

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

Ken Sheridan

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
 
A

Ana

Wow. Will give it a try. Thank you.

Ken Sheridan said:
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
Click to expand...
 
A

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.
 
A

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
 
D

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
 
A

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
 
A

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

API function 0
Hiding info on page header 1
Datediff 2
playing w/dates 2
Divide by 0 error 1
Storing dates 8
DB connection Q: 0
ADE problem 2

Top