VBA code to add 30 days in PrintDate and display Label caption

aman

Registered User.
Local time
Yesterday, 17:38
Joined
Oct 16, 2008
Messages
1,251
Hi Guys

I have a table as below:

ProjectRef VPrintdate Reminder
P14 1/03/2012 No
P15 2/03/2012 No
P16 30/03/2012 No

Now everytime the main form is loaded I want to run the code so that it looks for the ProjectRef whose 30 days have past since VPrintDate and reminder is still No and then display it on Label20 caption e.g

Label20.caption="P14,P15 Reminders are due"

Can anyone please provide me the code for this?

Thanks
 
You could create a query that selects the ProjectRef and filters out the dates that are due using the DateDiff() function, then you can open a recordset based on this query and loop through it and grab the ProjectRef's and build a StringVariable that you assign to your lable.

JR
 
Hi JANR, thanks for your reply. Could you please send me the code for this?

Regards,
 
You could at least TRY and do it yourself, to get you started see if this query gives you the correct records:

Code:
Select ProjectRef From [COLOR="Red"]MyTable[/COLOR] Where DateDiff('d', [VPrintDate] ,Date()) >= 30;

Match whats marked in red with your own setup.

JR
 
Private Sub Form_Open(Cancel As Integer)
Dim rs As Recordset
Dim db As Database
Dim Project As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT YourTable.ProjectRef FROM YourTable WHERE (((YourTable.VPrintDate) = Date() - 30) And ((YourTable.Reminder) = 'No')) ORDER BY YourTable.ProjectRef;")
With rs
rs.MoveFirst
Do
Project = Project & ", " & !ProjectRef
.MoveNext
Loop Until .EOF
.Close
End With
Set rs = Nothing
Project = Mid(Project, 3)
If Not IsNull(Project) Then
Me.Label20.Caption = Project & " Reminders are due"
Else
Me.Label20.Caption = "No reminders are due"
End If
End Sub
 
Thanks guys for your help. I worked absolutely fine.
 

Users who are viewing this thread

Back
Top Bottom