Solved Report Where condition

[oxicottin]

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.

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

 

[theDBguy]

If you're saying you want it to show the earliest date, maybe you could try using the Min() function.

 

[plog]

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]

the form could show you the min date: =DMin("[dateFld]","table", sWhere)
then you could enter it into the txtSpotcheckDate box

 

[June7]

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]

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]

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]

@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]

@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...

 

[June7]

One way:

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]

@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?

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]

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]

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]

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