Missing something?

  • Thread starter Thread starter Me
  • Start date Start date
M

Me

Hi,

I've got a report where the transaction lines shown should be limited by a
dialog window with dates from and to. However, the date used for this
limitation should be either the date of transaction or the date it was input
into the file. This choice is made via a checkbox on the controlling form.
I do have both these dates stored but I'm having trouble with this "choice"
of which date to use in the underlying query.

Without this choice, I know I can just stick my values in the criteria area
of the query such as "Between dlgDatesToUse.DateFrom and
dlgDatesToUse.DateTo". But that only works for one control. How can I run
this and make my choice? Can I use an IIf statement in criteria? So, if
the checkbox is equal to the date of transaction limitation, it uses the
date of transaction or else it runs with the date of entry? I've tried this
and it doesn't work.

How can this choice be made dynamically?

TIA,
ron
 
Hi,

I've got a report where the transaction lines shown should be limited by a
dialog window with dates from and to. However, the date used for this
limitation should be either the date of transaction or the date it was input
into the file. This choice is made via a checkbox on the controlling form.
I do have both these dates stored but I'm having trouble with this "choice"
of which date to use in the underlying query.

Without this choice, I know I can just stick my values in the criteria area
of the query such as "Between dlgDatesToUse.DateFrom and
dlgDatesToUse.DateTo". But that only works for one control. How can I run
this and make my choice? Can I use an IIf statement in criteria? So, if
the checkbox is equal to the date of transaction limitation, it uses the
date of transaction or else it runs with the date of entry? I've tried this
and it doesn't work.

How can this choice be made dynamically?

TIA,
ron
Click to expand...

You may find this easier in SQL view. Assuming that you have a form frmMyForm
with textboxes DateFrom and DateTo, and a checkbox chkWhichDate (with True
being TransactionDate and False being InputDate), you could use a criterion
like

WHERE (TransactionDate >= Forms!frmMyForm!DateFrom AND TransactionDate <=
Forms!frmMyForm!DateTo AND Forms!frmMyForm!chkWhichDate = True)
OR
(InputDate >= Forms!frmMyForm!DateFrom AND InputDate<= Forms!frmMyForm!DateTo
AND Forms!frmMyForm!chkWhichDate = True)
 
Thanks John,

That worked. However, it caused another problem (isn't it always the
case?). I use the same query on a couple other reports that are called from
another controlling form. So, when I try to run one of those other ones, I
get the parameter box popping up asking what Forms!frmMyForm!chkWhichDate
should be--because that form isn't open. Sheez!

Is there a way to control this stuff from the command that's running the
report on the form? So, put in the criteria there instead of in the query
as I'm trying to do? I've tried that, but I've found it gives me the
parameter input box asking for DateOfTransaction or DateOfEntry, depending
on which I've asked for (so,these are separate DoCmd.OpenReport blah blah's
depending on which box is checked for the whichdate control). I should
mention here that this report I'm trying to limit, date wise, is a
sub-report of a main report (that's why I'm reusing the same query on
different reports--they have other data from other sources that're
different...yet the transactions are the same.

I have a feeling it's part of the "parameters" part of the query but I just
can't seem to figure that out--Microsoft's help file is not helping me.

Thanks for any, all further help,
ron
 
Back
Top