Solved Live count of Financial Week / Period / YTD (1 Viewer)

randallst

Registered User.
Local time
Today, 06:51
Joined
Jan 28, 2015
Messages
64
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).
Annotation 2020-08-14 163425.jpg


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
I have managed to make a form to return today's date and what the Week / Period / Year numbers are which I have done via On Current Form Event;

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
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:51
Joined
Aug 30, 2003
Messages
36,118
Well, you can use DCount() along with the criteria you've pulled from the table. More on the syntax:


From an efficiency standpoint, it doesn't make sense to open 3 recordsets on the same criteria to pull 3 values. I'd open it once with "SELECT * ..." and then pull your 3 values. Your table may be small enough where the speed difference is negligible, but it would be a better habit to develop (1 trip to the data instead of 3).
 

plog

Banishment Pending
Local time
Today, 01:51
Joined
May 11, 2011
Messages
11,613
First, you've got poor field names:


A. Reserved Words (https://support.microsoft.com/en-us/office/learn-about-access-reserved-words-and-symbols-ae9d9ada-3255-4b12-91a9-f855bdd9c5a2#:~:text="Reserved words" are words and,when referring to the field.)
[Week] & [End] and any word on the above list should not be used as names in your database, they confuse the system and can make coding and querying dificult.

B. Only use alphanumeric and underscores in field names. Spaces and special characters (#, &, *, etc) make coding and querying more difficult as well. Remove the spaces from your field names.

Second, your data isn't normalized--Tbl_Complaints should just have 1 field--[DateReceived]. Tbl_Week would then be used to put that complaint into a Week/Period/FY via a query using this SQL:

Code:
SELECT [Date Received], [Week], Period, FY
FROM Tbl_Complaints
INNER JOIN Tbl_Week ON [Date Received]>=Start AND <=[End]

Finally, to your question--you wouldn't use a VBA sub, but make the Control source of every text box based on a DCount(https://www.techonthenet.com/access/functions/domain/dcount.php) that counts the appropriate field from the query I illustrated above. The one for today's complaints would be this:

=DCount("[Date Received]", "MyQueryAbove", "[Date Received]=#" & Date() & "#")
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:51
Joined
May 7, 2009
Messages
19,169
add additional table (tbl_dummy) with 1 record.
see the Query (FinalQuery).
see also the code behind Module1,
it returns the Sunday (date) where the Current day starts.
 

Attachments

  • complaints.zip
    22.5 KB · Views: 509

randallst

Registered User.
Local time
Today, 06:51
Joined
Jan 28, 2015
Messages
64
Thank you all for your feedback, especially about the poor namings. Everything I've done with access is self taught and from help from you all, so now i know this I will do my best to follow the guide to prevent using certain words.

@arnelgp I have used your method and is working perfectly. Thank you for writing into the module what it is doing, this will help me in the future for sure :)

All the best
Stuart
 

Users who are viewing this thread

Top Bottom