Solved Report Where condition (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Today, 10:46
Joined
Jun 26, 2007
Messages
851
Is it possible in my print codes [Where Condition] to change the date of a textbox on my report?

What I mean is I have a textbox (txtSpotcheckDate) that's control source is a particular date. Now when I print the report can I or am I able to change that date using the [Where Condition] to a earlier date?

The table (tbl_WeeklySafetyHuddle) field (WeekEnding) holds the date that the report uses.

Code:
 DoCmd.OpenReport "rpt_SpotCheck", acViewPreview, , "[WeekEnding]=" & txtSpotcheckDate - 4 , acHidden
DoCmd.PrintOut
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:46
Joined
Oct 29, 2018
Messages
21,358
If you're saying you want it to show the earliest date, maybe you could try using the Min() function.
 

plog

Banishment Pending
Local time
Today, 09:46
Joined
May 11, 2011
Messages
11,611
Now when I print the report can I or am I able to change that date using the [Where Condition] to a earlier date?

No.

But the good news is that I am super confused by your explanation. WHERE conditions do not change data, they implement criteria. So it's absolutly not possible 'to change the date using the [Where Condition]'.

Perhaps you can demonstrate your issue with a specific example.
 

Ranman256

Well-known member
Local time
Today, 10:46
Joined
Apr 9, 2015
Messages
4,339
the form could show you the min date: =DMin("[dateFld]","table", sWhere)
then you could enter it into the txtSpotcheckDate box
 

June7

AWF VIP
Local time
Today, 06:46
Joined
Mar 9, 2014
Messages
5,423
Where criteria for opening report would not refer to textbox on report.

Can have a calculation in textbox that subtracts days: =[Weekending] - 4
Or do calculation in query used as report RecordSource and bind textbox to that constructed field.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:46
Joined
Feb 28, 2001
Messages
26,999
If you use a form to launch the report, you can have the form code that does the launch change the filter on the line that opens the report. If used any other way, it gets a LOT tougher.

The problem you have is that in order to see the date in that textbox on your report, you have to open it - which means it is too late to change it. Once you have gone far enough to see the textbox, the recordset for the report has already been determined. If we understood your question, the answer is "No" in the way you asked it. The best bet would be if you launched from a form, have a separate query to look up the date that would be shown in the textbox in question and do a DLookup of that query to get the required date.

Basically your question as asked requires you to go backwards in time, which last I looked was impossible. But if you ever figure out how to go back in time, please let us know before you announce the IPO.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:46
Joined
Feb 19, 2002
Messages
42,970
Pass the --- txtSpotcheckDate - 4 in the OpenArgs as well as criteria. Then in your form's Load event, populate the report field you want to display the date.

Me.SomeDateField = Me.OpenArgs
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:46
Joined
Feb 19, 2002
Messages
42,970
@oxicottin I know people like to "like" things but if my suggestion solved your problem, in a forum like this, it is best to tell us in words so that others who find the post know what actually solved the problem. Then mark the thread as solved.
 

oxicottin

Learning by pecking away....
Local time
Today, 10:46
Joined
Jun 26, 2007
Messages
851
@Pat Hartman sorry for the late reply I been out of work due to an injury. I jumped the gun but I do believe your solution will work but I'm not completely understand it. Can you explain this further please? Also below is a better explanation of what I'm trying to do...

I have a form (frm_WeeklySafetyHuddle) with a text box (txtWeekEnding) that holds a Fridays date. I need to open the report (rpt_Spotcheck) from a button on the form (frm_WeeklySafetyHuddle) to print two copies of the (rpt_Spotcheck) but on the report there is a text box (txtSpotCheckDate) that has to be the Mondays date from the date on my form (frm_WeeklySafetyHuddle) textbox (txtWeekEnding) and the second reports copy would have tuesdays date from that text box (txtWeekEnding on the form.

Thanks and again sorry for jumping the gun...
 
Last edited:

June7

AWF VIP
Local time
Today, 06:46
Joined
Mar 9, 2014
Messages
5,423
One way:
Code:
For x = 4 to 3 Step -1
DoCmd.OpenReport "rpt_Spotcheck", , , , , Me.txtWeekEnding - x
DoCmd.Close
Next
Then expression in textbox on report: =[OpenArgs]
 

oxicottin

Learning by pecking away....
Local time
Today, 10:46
Joined
Jun 26, 2007
Messages
851
@June7 that worked perfectly thanks! A few questions though.... First I got an error "Variable not defined" so I added (Dim x As Integer) was that correct? Next I really don't know how its printing if I didn't tell it to using Docmd.PrintOut? Last can you explain the For statement please? Im not getting the Step - 1 because Microsoft says "Amount counter is changed each time through the loop" but it looks like = 4 starts as Monday and to me -1 would take you backwards in days?

Code:
Private Sub Command81_Click()
Dim x As Integer
For x = 4 To 3 Step -1
DoCmd.OpenReport "rpt_SpotCheck", , , , , Me.txtWeekEnding - x
DoCmd.Close
Next
End Sub
 

June7

AWF VIP
Local time
Today, 06:46
Joined
Mar 9, 2014
Messages
5,423
Yes, declaring variable was appropriate.

A For loop default increment (Step) is 1. The Step argument can specify larger increment and/or count backwards.

The default destination for OpenReport is direct to print. This can be changed with the second argument. You should explore all the OpenReport arguments.

You said first print should be Monday and second for Tuesday. Tuesday is closer to Friday of same week than Monday. I don't think it really matters which prints first. If you do
For x = 3 to 4
Then Tuesday will print first and Monday second. However, if you really want Monday and Tuesday of following week, then should be:
Me.txtWeekEnding + x
and
For x = 3 to 4
 

oxicottin

Learning by pecking away....
Local time
Today, 10:46
Joined
Jun 26, 2007
Messages
851
Thanks for explaining! It does matter what day prints because I'm tiring to do several prints of different reports for a weekly packet and when I go through it the dates will all be in order.

Thanks again!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:46
Joined
Feb 19, 2002
Messages
42,970
Next I really don't know how its printing if I didn't tell it to using Docmd.PrintOut?
June omitted the View argument. It comes next after the report name. Apparently acViewNormal is the default and that prints. I don't omit this type of variable, even though it seems redundant. Just because it confuses people.

DoCmd.OpenReport "rpt_SpotCheck",acViewNormal , , , , Me.txtWeekEnding - x
 

Users who are viewing this thread

Top Bottom