Hi Everyone,
I thought I had mastered everything on my new project, but i'm stuck on my last hurdle! I haven't managed to create anything yet as my mind has gone blank and anything I've tried has failed miserably!
I've drawn out the two tables I need to reference, containing example data (Tbl_Complaints and Tbl_Week).
What I'm trying to do is on my Complaints Main Form (Frm_Complaints) is have 4 text boxes that auto populate with the count of complaints we've had;
Can anyone help me with my madness?! As always, any help / hints etc is greatly appreciated.
All the best
Stu
I thought I had mastered everything on my new project, but i'm stuck on my last hurdle! I haven't managed to create anything yet as my mind has gone blank and anything I've tried has failed miserably!
I've drawn out the two tables I need to reference, containing example data (Tbl_Complaints and Tbl_Week).
What I'm trying to do is on my Complaints Main Form (Frm_Complaints) is have 4 text boxes that auto populate with the count of complaints we've had;
- Today
- This Week
- This Period
- This Financial Year
Code:
Private Sub Form_Current()
'Add Todays Date
Me.Date_Received.Value = Date
'Add Period Number
SQLQuery = "SELECT Period FROM Tbl_Week WHERE CDATE(" & Chr(34) & Date_Received & Chr(34) & " )>= Start AND CDATE(" & Chr(34) & Date_Received & Chr(34) & ") <= End"
Me.Period_Received = CurrentDb.OpenRecordset(SQLQuery).Fields(0)
'Add Week Number
SQLQuery = "SELECT Week FROM Tbl_Week WHERE CDATE(" & Chr(34) & Date_Received & Chr(34) & " )>= Start AND CDATE(" & Chr(34) & Date_Received & Chr(34) & ") <= End"
Me.Week_Received = CurrentDb.OpenRecordset(SQLQuery).Fields(0)
'Add FY Number
SQLQuery = "SELECT FY FROM Tbl_Week WHERE CDATE(" & Chr(34) & Date_Received & Chr(34) & " )>= Start AND CDATE(" & Chr(34) & Date_Received & Chr(34) & ") <= End"
Me.Year_Received = CurrentDb.OpenRecordset(SQLQuery).Fields(0)
End Sub
Can anyone help me with my madness?! As always, any help / hints etc is greatly appreciated.
All the best
Stu