Display Records Between Date Range and Most Recent Previous Record

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Display Records Between Date Range and Most Recent Previous Record

Post by EnginerdUNH »

Hi,

I have a report which gets its data from the following query:

Code: Select all

SELECT qryFirstSets.ARExercise, qryFirstSets.Exercise, qryFirstSets.PosEquip, qryFirstSets.ARSets, qryFirstSets.ARLoad, qryFirstSets.MinOfARDate AS ARDate
FROM qryFirstSets
WHERE (((qryFirstSets.ARLoad)>Nz((SELECT Max(Q.ARLoad) FROM qryFirstSets AS Q WHERE Q.ARExercise=qryFirstSets.ARExercise AND Q.PosEquip=qryFirstSets.PosEquip AND Q.ARSets=qryFirstSets.ARSets AND Q.MinOfARDate<qryFirstSets.MinOfARDate),0)));
this query works great when I want to look at all records associated with it or only records on or between two different dates (set from a field on the main dashboard form of my database). But what I want to do is also include the most recent records on or before the first date in the report as well. i attempted to set myself up for this with the following query:

Code: Select all

SELECT tblAllRecords.ARExercise, tblExercise.Exercise, [tblPositions].[Position] & ", " & [tblEquipment].[Equipment] AS PosEquip, tblAllRecords.ARSets, Max(tblAllRecords.ARLoad) AS MaxOfARLoad, Min(tblAllRecords.ARDate) AS MinOfARDate
FROM ((tblAllRecords INNER JOIN tblExercise ON tblAllRecords.ARExercise = tblExercise.ExerciseID) INNER JOIN tblPositions ON tblAllRecords.ARPosition = tblPositions.PositionID) INNER JOIN tblEquipment ON tblAllRecords.AREquipment = tblEquipment.EquipmentID
GROUP BY tblAllRecords.ARExercise, tblExercise.Exercise, [tblPositions].[Position] & ", " & [tblEquipment].[Equipment], tblAllRecords.ARSets
HAVING (((tblAllRecords.ARSets)<=5) AND ((Min(tblAllRecords.ARDate))<=[Forms]![frmMainDashboard]![txtStartDate]));
My questions now are:
1) Do I need to create another query, essentially identical to the first one I posted but with the criteria that it only displays records between the dates from frmMainDashboard?
2) Do I use another select query with inner/left/right joins to combine these two queries or do I use a union query?
3) Do I need to create a new report that uses this new query as the record source or is there a way in VBA to say "if a date range is specified, your record source is this otherwise it's that..."?

Note: I attempted to attach a copy of the database to this post but even with compressing it into a zip file, it still kept giving me an error that the file size is too large. If you need me to post a copy, I could try to reduce the size by saving a copy of it and removing any tables and queries that I don't need for this question. I also have an older copy of the database attached to this post here: https://www.eileenslounge.com/viewtopic ... 55#p294055

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Display Records Between Date Range and Most Recent Previous Record

Post by HansV »

I'm not sure I understand your question, but perhaps you can open the report using

Code: Select all

    DoCmd.OpenReport ReportName:="rptAllRecords", View:=acViewPreview, WhereCondition:="ARDate<=" & Format(Forms!frmMainDashboard!txtStartDate, "\#yyyy-mm-dd\#")p/code]
Best wishes,
Hans

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Re: Display Records Between Date Range and Most Recent Previous Record

Post by EnginerdUNH »

Hi Hans!

Let me try to break it down a little bit and see if this makes some more sense:

I have a query, qryFirstRecord which displays the first instance of a PR from tblAllRecords.

Currently, I can use txtStartDate and txtEndDate on the main dashboard to filter rptAllRecords to show all records from qryFirstRecord where the date falls between txtStartDate and txtEndDate.

What I want to do is modify the database such that when I specify a start and end date, the report will also pull the most recent PR record occurring on or before txtStartDate.

Please let me know if that still doesn’t make sense

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Display Records Between Date Range and Most Recent Previous Record

Post by HansV »

Thanks.
1) Yes
2) Use a union query
3) Create a copy of the report and set its record source to the union query.
Best wishes,
Hans