Changing criteria in query builder

[Jeff Hunt]

I have a query called qryMain that is the data source for a form. Currently
it is made of a table (tblAssociates) and a query (qryMaxDate). The
qryMaxDate is used to filter the associate list so that only the active
people (most recently downloaded) show up in the form. Now I am trying to
make it so the user can select to show outdated associates as well. I wrote
an IIF statement in the criteria that *almost* does what I want. It looks
like:

IIf([forms]![frmMain]![chkShowInactiveAssoc]=0,
[qryTblAssociatesMaxDate].[MaxOfLastDownloaded],
[tblAssociates].[LastDownloaded])

The reason that I don't like this solution is that it shows current and old
associates, and I would rather show just the old associates when
chkShowInactiveAssociates is checked. What I tried to write was something
like this:

IIf([forms]![frmMain]![chkShowInactiveAssoc]=0,
[qryTblAssociatesMaxDate].[MaxOfLastDownloaded],
<>[qryTblAssociatesMaxDate].[MaxOfLastDownloaded])

But that doesn't work. I've tried a bunch of different variations (less
than, not in, not like, etc) but can't seem to make it work. Is there a
simple way to make it show a range of dates based on a form-bound criteria?

Thanks.
....jeff...

 

[Jeff Boyce]

Jeff

Another approach might be to use the checkbox's AfterUpdate event to modify
the RowSource of the list (you don't say if it is a listbox, a combobox, or
what).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Hunt]

Thanks for the quick answer. The query is the source for the entire form,
not just a list or combo box. Your comment makes me think that I may be able
to just create an alternate query to do this, and change the data source of
the form with the after update code, but I'm not sure the query will do that
anyway (with all the playing I did earlier, I remember seeing that I couldn't
get only the non-recent records to show). I will try it when I get back to
the office tomorrow. In the mean time, does anyone know if it's even
possible to make a criteria do this type of variable filtering?

Jeff

Another approach might be to use the checkbox's AfterUpdate event to modify
the RowSource of the list (you don't say if it is a listbox, a combobox, or
what).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a query called qryMain that is the data source for a form.
Currently
it is made of a table (tblAssociates) and a query (qryMaxDate). The
qryMaxDate is used to filter the associate list so that only the active
people (most recently downloaded) show up in the form. Now I am trying to
make it so the user can select to show outdated associates as well. I
wrote
an IIF statement in the criteria that *almost* does what I want. It looks
like:

IIf([forms]![frmMain]![chkShowInactiveAssoc]=0,
[qryTblAssociatesMaxDate].[MaxOfLastDownloaded],
[tblAssociates].[LastDownloaded])

The reason that I don't like this solution is that it shows current and
old
associates, and I would rather show just the old associates when
chkShowInactiveAssociates is checked. What I tried to write was something
like this:

IIf([forms]![frmMain]![chkShowInactiveAssoc]=0,
[qryTblAssociatesMaxDate].[MaxOfLastDownloaded],
<>[qryTblAssociatesMaxDate].[MaxOfLastDownloaded])

But that doesn't work. I've tried a bunch of different variations (less
than, not in, not like, etc) but can't seem to make it work. Is there a
simple way to make it show a range of dates based on a form-bound
criteria?

Thanks.
...jeff...

 

[Pieter Wijnen]

A simple solution is to use a Union Query

SELECT * From YourTable
WHERE TheDateField >= TheDate
And Forms!MyForm!Criteria <> 0
Union All
SELECT * From YourTable
WHERE TheDateField < TheDate
And Forms!MyForm!Criteria = 0

to make it updateable set the rowsource to either of the Select's

Pieter

Thanks for the quick answer. The query is the source for the entire form,
not just a list or combo box. Your comment makes me think that I may be
able
to just create an alternate query to do this, and change the data source
of
the form with the after update code, but I'm not sure the query will do
that
anyway (with all the playing I did earlier, I remember seeing that I
couldn't
get only the non-recent records to show). I will try it when I get back
to
the office tomorrow. In the mean time, does anyone know if it's even
possible to make a criteria do this type of variable filtering?

Jeff

Another approach might be to use the checkbox's AfterUpdate event to
modify
the RowSource of the list (you don't say if it is a listbox, a combobox,
or
what).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a query called qryMain that is the data source for a form.
Currently
it is made of a table (tblAssociates) and a query (qryMaxDate). The
qryMaxDate is used to filter the associate list so that only the active
people (most recently downloaded) show up in the form. Now I am trying
to
make it so the user can select to show outdated associates as well. I
wrote
an IIF statement in the criteria that *almost* does what I want. It
looks
like:

IIf([forms]![frmMain]![chkShowInactiveAssoc]=0,
[qryTblAssociatesMaxDate].[MaxOfLastDownloaded],
[tblAssociates].[LastDownloaded])

The reason that I don't like this solution is that it shows current and
old
associates, and I would rather show just the old associates when
chkShowInactiveAssociates is checked. What I tried to write was
something
like this:

IIf([forms]![frmMain]![chkShowInactiveAssoc]=0,
[qryTblAssociatesMaxDate].[MaxOfLastDownloaded],
<>[qryTblAssociatesMaxDate].[MaxOfLastDownloaded])

But that doesn't work. I've tried a bunch of different variations
(less
than, not in, not like, etc) but can't seem to make it work. Is there
a
simple way to make it show a range of dates based on a form-bound
criteria?

Thanks.
...jeff...