Finding records by Not or <>

[Derek Brown]

Hi All

I have a query that gets one of its criteria from a combo box called "User"
on my form. Most of the time I want the records selected using the combo's
text. But sometimes I want this criteria to be ignored and show all records.
To do this I wanted to put a check box on the form with name "X" and a
value -1 or 0. and I was hoping that with the following in the relative
criteria row it might do it. Hope you can see what I am trying to do.

IIf([Forms]![MainForm]![X]=0,[Forms]![MainForm]![User]),Not
[Forms]![MainForm]![User])

Obviously it doesn't or I would not be pestering you. Any Ideas?

 

[Guest]

create a new field:
expr1: [Forms]![MainForm]![X]

then add this criteria that field:

0

Put the criteria on a different line in the query design view,
so that you will get the record if this criteria is true, OR
if the other criteria are true.

(david)

 

[Van T. Dinh]

Try the criteria:

[Forms]![MainForm]![User] Or ([Forms]![MainForm]![X]=True)

 

[Derek Brown]

Thank you david

The problem is I want to search for all records where the name is "Derek"
but then without running a different query and by ticking a check box I just
want it to show all records regardless of name.

create a new field:
expr1: [Forms]![MainForm]![X]

then add this criteria that field:

0

Put the criteria on a different line in the query design view,
so that you will get the record if this criteria is true, OR
if the other criteria are true.

(david)

Hi All

I have a query that gets one of its criteria from a combo box called "User"
on my form. Most of the time I want the records selected using the
combo's
text. But sometimes I want this criteria to be ignored and show all records.
To do this I wanted to put a check box on the form with name "X" and a
value -1 or 0. and I was hoping that with the following in the relative
criteria row it might do it. Hope you can see what I am trying to do.

IIf([Forms]![MainForm]![X]=0,[Forms]![MainForm]![User]),Not
[Forms]![MainForm]![User])

Obviously it doesn't or I would not be pestering you. Any Ideas?

 

[david epsom dot com dot au]

Right click to get the filter menu.

(david)

Thank you david

The problem is I want to search for all records where the name is "Derek"
but then without running a different query and by ticking a check box I
just want it to show all records regardless of name.

create a new field:
expr1: [Forms]![MainForm]![X]

then add this criteria that field:

0

Put the criteria on a different line in the query design view,
so that you will get the record if this criteria is true, OR
if the other criteria are true.

(david)

Hi All

I have a query that gets one of its criteria from a combo box called "User"
on my form. Most of the time I want the records selected using the
combo's
text. But sometimes I want this criteria to be ignored and show all records.
To do this I wanted to put a check box on the form with name "X" and a
value -1 or 0. and I was hoping that with the following in the relative
criteria row it might do it. Hope you can see what I am trying to do.

IIf([Forms]![MainForm]![X]=0,[Forms]![MainForm]![User]),Not
[Forms]![MainForm]![User])

Obviously it doesn't or I would not be pestering you. Any Ideas?

 

[Derek Brown]

Hi David.

As usual I have tried to keep it short and sweet to avoid problems. But let
me explain further. The program is for commercial use. I cannot expect
inexperienced users to know what a filter is. The Check box is not connected
to the data and has no purpose other than to allow the user to effectiveley
show all records. Problem is that the records are in the form of a report so
its the reports records that need filtering (or not) and therefore the query
need the criteria for the report. It all works great until I want to show
all records not those in the criteria field. If it was just one report that
was concerned I could simply have two buttons and two queries and two
different reports. But many other aspects of the database (including
numerous Combo boxes) also use the same criteria and query. It looks like I
am going to have to add code to switch record souces for all the combos.
Just thought it was clumbsy and was hoping for the majic bullet!! Any Ideas?

Thanks again.

Right click to get the filter menu.

(david)

Thank you david

The problem is I want to search for all records where the name is "Derek"
but then without running a different query and by ticking a check box I
just want it to show all records regardless of name.

create a new field:
expr1: [Forms]![MainForm]![X]

then add this criteria that field:

0

Put the criteria on a different line in the query design view,
so that you will get the record if this criteria is true, OR
if the other criteria are true.

(david)




Hi All

I have a query that gets one of its criteria from a combo box called
"User"
on my form. Most of the time I want the records selected using the
combo's
text. But sometimes I want this criteria to be ignored and show all
records.
To do this I wanted to put a check box on the form with name "X" and a
value -1 or 0. and I was hoping that with the following in the relative
criteria row it might do it. Hope you can see what I am trying to do.

IIf([Forms]![MainForm]![X]=0,[Forms]![MainForm]![User]),Not
[Forms]![MainForm]![User])

Obviously it doesn't or I would not be pestering you. Any Ideas?

 

[david epsom dot com dot au]

The only way to make a report look different is
to re-run the report.

It is difficult to make sense of your explanations,
but it seems to me that the original suggestions
would work.

(david)

Hi David.

As usual I have tried to keep it short and sweet to avoid problems. But
let me explain further. The program is for commercial use. I cannot expect
inexperienced users to know what a filter is. The Check box is not
connected to the data and has no purpose other than to allow the user to
effectiveley show all records. Problem is that the records are in the form
of a report so its the reports records that need filtering (or not) and
therefore the query need the criteria for the report. It all works great
until I want to show all records not those in the criteria field. If it
was just one report that was concerned I could simply have two buttons and
two queries and two different reports. But many other aspects of the
database (including numerous Combo boxes) also use the same criteria and
query. It looks like I am going to have to add code to switch record
souces for all the combos. Just thought it was clumbsy and was hoping for
the majic bullet!! Any Ideas?

Thanks again.

Right click to get the filter menu.

(david)

Thank you david

The problem is I want to search for all records where the name is
"Derek" but then without running a different query and by ticking a
check box I just want it to show all records regardless of name.

create a new field:
expr1: [Forms]![MainForm]![X]

then add this criteria that field:

0

Put the criteria on a different line in the query design view,
so that you will get the record if this criteria is true, OR
if the other criteria are true.

(david)




Hi All

I have a query that gets one of its criteria from a combo box called
"User"
on my form. Most of the time I want the records selected using the
combo's
text. But sometimes I want this criteria to be ignored and show all
records.
To do this I wanted to put a check box on the form with name "X" and a
value -1 or 0. and I was hoping that with the following in the
relative
criteria row it might do it. Hope you can see what I am trying to do.

IIf([Forms]![MainForm]![X]=0,[Forms]![MainForm]![User]),Not
[Forms]![MainForm]![User])

Obviously it doesn't or I would not be pestering you. Any Ideas?

 

[Derek Brown]

Hi thanks for your patience

If I could write in the criteria of the query "Use the value in Combobox
"ComboA"on the main form, unless, The check box on the same form is ticked,
then ignor the value in Combobox "ComboA" and show all records". That would
be perfect. This is why I tried

IIf ([Forms]![MainForm]![CheckX]=0,[Forms]![MainForm]![ComboA],"*")

Doesn't work, but hopefully you can see what I need

The only way to make a report look different is
to re-run the report.

It is difficult to make sense of your explanations,
but it seems to me that the original suggestions
would work.

(david)

Hi David.

As usual I have tried to keep it short and sweet to avoid problems. But
let me explain further. The program is for commercial use. I cannot
expect inexperienced users to know what a filter is. The Check box is not
connected to the data and has no purpose other than to allow the user to
effectiveley show all records. Problem is that the records are in the
form of a report so its the reports records that need filtering (or not)
and therefore the query need the criteria for the report. It all works
great until I want to show all records not those in the criteria field.
If it was just one report that was concerned I could simply have two
buttons and two queries and two different reports. But many other aspects
of the database (including numerous Combo boxes) also use the same
criteria and query. It looks like I am going to have to add code to
switch record souces for all the combos. Just thought it was clumbsy and
was hoping for the majic bullet!! Any Ideas?

Thanks again.

Right click to get the filter menu.

(david)

Thank you david

The problem is I want to search for all records where the name is
"Derek" but then without running a different query and by ticking a
check box I just want it to show all records regardless of name.

create a new field:
expr1: [Forms]![MainForm]![X]

then add this criteria that field:

0

Put the criteria on a different line in the query design view,
so that you will get the record if this criteria is true, OR
if the other criteria are true.

(david)




Hi All

I have a query that gets one of its criteria from a combo box called
"User"
on my form. Most of the time I want the records selected using the
combo's
text. But sometimes I want this criteria to be ignored and show all
records.
To do this I wanted to put a check box on the form with name "X" and
a
value -1 or 0. and I was hoping that with the following in the
relative
criteria row it might do it. Hope you can see what I am trying to do.

IIf([Forms]![MainForm]![X]=0,[Forms]![MainForm]![User]),Not
[Forms]![MainForm]![User])

Obviously it doesn't or I would not be pestering you. Any Ideas?

 

[Guest]

IIf ([Forms]![MainForm]![CheckX]=0,[Forms]![MainForm]![ComboA],"*")

It doesn't look anything like what I suggested, or what VTD
suggested. Google
"finding records by not" group:microsoft.public.access.*

to see the suggestions again.

(david)

Hi thanks for your patience

If I could write in the criteria of the query "Use the value in Combobox
"ComboA"on the main form, unless, The check box on the same form is ticked,
then ignor the value in Combobox "ComboA" and show all records". That would
be perfect. This is why I tried

IIf ([Forms]![MainForm]![CheckX]=0,[Forms]![MainForm]![ComboA],"*")

Doesn't work, but hopefully you can see what I need

The only way to make a report look different is
to re-run the report.

It is difficult to make sense of your explanations,
but it seems to me that the original suggestions
would work.

(david)

Hi David.

As usual I have tried to keep it short and sweet to avoid problems. But
let me explain further. The program is for commercial use. I cannot
expect inexperienced users to know what a filter is. The Check box is not
connected to the data and has no purpose other than to allow the user to
effectiveley show all records. Problem is that the records are in the
form of a report so its the reports records that need filtering (or not)
and therefore the query need the criteria for the report. It all works
great until I want to show all records not those in the criteria field.
If it was just one report that was concerned I could simply have two
buttons and two queries and two different reports. But many other aspects
of the database (including numerous Combo boxes) also use the same
criteria and query. It looks like I am going to have to add code to
switch record souces for all the combos. Just thought it was clumbsy and
was hoping for the majic bullet!! Any Ideas?

Thanks again.

Right click to get the filter menu.

(david)

Thank you david

The problem is I want to search for all records where the name is
"Derek" but then without running a different query and by ticking a
check box I just want it to show all records regardless of name.

create a new field:
expr1: [Forms]![MainForm]![X]

then add this criteria that field:

0

Put the criteria on a different line in the query design view,
so that you will get the record if this criteria is true, OR
if the other criteria are true.

(david)




Hi All

I have a query that gets one of its criteria from a combo box called
"User"
on my form. Most of the time I want the records selected using the
combo's
text. But sometimes I want this criteria to be ignored and show all
records.
To do this I wanted to put a check box on the form with name "X" and
a
value -1 or 0. and I was hoping that with the following in the
relative
criteria row it might do it. Hope you can see what I am trying to do.

IIf([Forms]![MainForm]![X]=0,[Forms]![MainForm]![User]),Not
[Forms]![MainForm]![User])

Obviously it doesn't or I would not be pestering you. Any Ideas?

 

[Derek Brown]

Hi David

I am sorry i completely missunderstood your original reply. I had assumed
that the extra field was for the form not the query, "yes I know you did say
query. I was concentrating on the expression so much i skipped the important
bit. I'm sure that will work perfectly. That's a great solution.

Thanks again

IIf ([Forms]![MainForm]![CheckX]=0,[Forms]![MainForm]![ComboA],"*")

It doesn't look anything like what I suggested, or what VTD
suggested. Google
"finding records by not" group:microsoft.public.access.*

to see the suggestions again.

(david)

Hi thanks for your patience

If I could write in the criteria of the query "Use the value in Combobox
"ComboA"on the main form, unless, The check box on the same form is ticked,
then ignor the value in Combobox "ComboA" and show all records". That would
be perfect. This is why I tried

IIf ([Forms]![MainForm]![CheckX]=0,[Forms]![MainForm]![ComboA],"*")

Doesn't work, but hopefully you can see what I need

The only way to make a report look different is
to re-run the report.

It is difficult to make sense of your explanations,
but it seems to me that the original suggestions
would work.

(david)


Hi David.

As usual I have tried to keep it short and sweet to avoid problems.
But
let me explain further. The program is for commercial use. I cannot
expect inexperienced users to know what a filter is. The Check box is not
connected to the data and has no purpose other than to allow the user to
effectiveley show all records. Problem is that the records are in the
form of a report so its the reports records that need filtering (or not)
and therefore the query need the criteria for the report. It all works
great until I want to show all records not those in the criteria
field.
If it was just one report that was concerned I could simply have two
buttons and two queries and two different reports. But many other aspects
of the database (including numerous Combo boxes) also use the same
criteria and query. It looks like I am going to have to add code to
switch record souces for all the combos. Just thought it was clumbsy and
was hoping for the majic bullet!! Any Ideas?

Thanks again.

Right click to get the filter menu.

(david)

Thank you david

The problem is I want to search for all records where the name is
"Derek" but then without running a different query and by ticking a
check box I just want it to show all records regardless of name.

create a new field:
expr1: [Forms]![MainForm]![X]

then add this criteria that field:

0

Put the criteria on a different line in the query design view,
so that you will get the record if this criteria is true, OR
if the other criteria are true.

(david)




Hi All

I have a query that gets one of its criteria from a combo box called
"User"
on my form. Most of the time I want the records selected using the
combo's
text. But sometimes I want this criteria to be ignored and show
all
records.
To do this I wanted to put a check box on the form with name "X" and
a
value -1 or 0. and I was hoping that with the following in the
relative
criteria row it might do it. Hope you can see what I am trying to do.

IIf([Forms]![MainForm]![X]=0,[Forms]![MainForm]![User]),Not
[Forms]![MainForm]![User])

Obviously it doesn't or I would not be pestering you. Any Ideas?

 

[Derek Brown]

Thank you Van

I must say I am so glad that didn't work. I realy thought I had spent the
last 10 years doing this in vain. The queried field is a date field the
check box is a yes no (I know you are well aware of this) so as usual I am
assuming that I have lead you up the garden path with my question. Can you
have another look?

Thank you

Try the criteria:

[Forms]![MainForm]![User] Or ([Forms]![MainForm]![X]=True)


--
HTH
Van T. Dinh
MVP (Access)

Hi All

I have a query that gets one of its criteria from a combo box called "User"
on my form. Most of the time I want the records selected using the
combo's
text. But sometimes I want this criteria to be ignored and show all records.
To do this I wanted to put a check box on the form with name "X" and a
value -1 or 0. and I was hoping that with the following in the relative
criteria row it might do it. Hope you can see what I am trying to do.

IIf([Forms]![MainForm]![X]=0,[Forms]![MainForm]![User]),Not
[Forms]![MainForm]![User])

Obviously it doesn't or I would not be pestering you. Any Ideas?

 

[Van T. Dinh]

Well ... I am not even sure "what didn't work".

I offered a possible solution for your original post asking about a ComboBox
"User" and a CheckBox "X" to ignore the criterion that uses the ComboBox.
Now you referred to a Date Field which I can't see how this Date Field
related to your original question or the possible solution I posted.

Reading your "conversation" with David in this thread, the Date Field was
never mentioned either.

Incorrect thread, perhaps?

 

[Derek Brown]

Incorrect brain I think.
I meant Text box not Date. and now I understand your solution too. Must stop
these late nights. I frequently use And Or statements in the same column
criteria. When you offered the solution I thought you meant put your
solution in the same column.

Thank you both again gentlemen.