i have a date field in my databse in format mm/dd/yyyy
how do i find if someone is having their birthday based on today's date in the databse when the form loads.
how to loop in the recordset i'm using adodc data control
Printable View
i have a date field in my databse in format mm/dd/yyyy
how do i find if someone is having their birthday based on today's date in the databse when the form loads.
how to loop in the recordset i'm using adodc data control
i've tried this so far
not working though
Do Until Adodc.Recordset.EOF
If Adodc.Recordset.Fields("BirthDay") = Format(Now, "mm/dd/yyyy") Then
MsgBox "Today is " & Adodc.Recordset.Fields("First_Name") & "BirthDay!!!!", , "Birthdays"
End If
Loop
Is Birthday a date/time field?
In what format are dates stored in this field?
I would do this:
set rs = new adodb.recordset
sql = " SELECT [FIRST NAME] FROM TABLENAME WHERE [BIRTHDATE] = " & "'" & CDATE(DATE) & "'"
rs.Open SQL,CnxnTechSQL, adOpenKeyset, adLockReadOnly, adCmdText
if not rs.bof and not rs.eof then
rs.movefirst
msg = ""
do
msg= msg & "Happy Birthday " & rs![[First Name] & "" & vbcrlf
rs.movenext
loop while not rs.eof
msgbox, msg
rs.close
set rs = nothing
i'm getting an error on this line
rs.Open SQL, CnxnTechSQL, adOpenKeyset, adLockReadOnly, adCmdText
what does htis do CnxnTechSQL?
anyway the error is
Runtime error 3001
Arguments are of the wrong type ,are out of acceptable range,or are in conflict with one another
cnxntechsql is the database connection name
CnxnTechSQL appears to be a connection object.
To properly define it as such, you would doThen, you would give the connection object a connection string to your database.VB Code:
Dim CnxnTechSQL As ADODB.Connection Set CnxnTechSQL = ADODB.Connection
VB Code:
Dim recordbday() as string Dim todaydate() as string Do Until Adodc.Recordset.EOF recordbday = Split(adodc.recordset.fields("Birthday"),"/") todaydate = Split(now,"/") If recordbday(0) & "/" & recordbday(1) = todaydate(0) & "/" & todaydate(1) Then MsgBox "Today is " & Adodc.Recordset.Fields("First_Name") & "BirthDay!!!!", , "Birthdays" End If Loop
try that..although I didn't test it
This might work also...
VB Code:
Do Until Adodc.Recordset.EOF If DateValue(Adodc.Recordset.Fields("BirthDay")) = DateValue(Date) Then MsgBox "Today is " & Adodc.Recordset.Fields("First_Name") & "BirthDay!!!!", , "Birthdays" End If Loop
And this too...
VB Code:
SQL = " SELECT [FIRST NAME] FROM TABLENAME WHERE [BIRTHDATE] = " & "'" & DateValue(Date) & "'"