Solved Query between two dates on different forms (1 Viewer)

[foshizzle]

I have a report based off 15 small queries. Some of those queries accepts a start and end date entered on a form to find transactions between a given time.

I would like to re-use those same queries for a new report without having to make all 15 of queries again. The problem is, those queries are looking at the original form's start and end dates instead of the new form for the new report.

I've tried using an OR in the query's date criteria but it just prompts for the dates on the original form.
Between [Forms]![frmReportBuildGainLoss]![txtStartDate] And [Forms]![frmReportBuildGainLoss]![txtEndDate] OR
Between [Forms]![frmReportBuildTransactions]![txtStartDate] And [Forms]![frmReportBuildTransactions]![txtEndDate]

Hope that made sense; is there any way around this?

 

[moke123]

Use a tempvar or public variable.

 

[Gasman]

I have used TempVars for exactly that purpose?

 

[foshizzle]

Thanks! I'm looking at a few pages regarding TempVars...

So now, I have the following in the button's click event on my date entry form.
Dim tmpStartDate As TempVars
Dim tmpEndDate As TempVars
TempVars!tmpStartDate = Me.txtStartDate.Value
TempVars!tmpEndDate = Me.txtEndDate.Value

At the end of the event code, I'll enter
TempVars.RemoveAll

Then, for each query needing the start/end dates, I'll enter
Between TempVars!tmpStartDate And TempVars!tmpEndDate

Does this make sense? I'd just like to verify before i change multiple queries. Thanks for your insight!

 

[Gasman]

Just try it with one
Even copy an existing query?

FWIW I've never removed TempVars, naughty perhaps?

If you are going to use this a lot (which sounds like you are?), I'd define them at the start/logon, then just populate them when needed?

 

[MajP]

If not tempvars you could make UDFs

Public Function GetStartDate(FormName as string, Optional ControlName as string = "txtStartDate") as string
   return formatted date from selected form
end function

 

[foshizzle]

Just try it with one
Even copy an existing query?

FWIW I've never removed TempVars, naughty perhaps?

If you are going to use this a lot (which sounds like you are?), I'd define them at the start/logon, then just populate them when needed?

Can't believe that worked. Much easier than i thought. Thanks!