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)));
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]));
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