IIf problem with <>

[Steve]

Hi can anybody please tell me why this query criteria does not work.
IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")

I have a tick box on a form called ShowComplete that can be set to true or
false
when it is set to true I want it to show all records no matter what is in
the status field. but if the tick box is set to false I want all records
except the records with the word "Complete" to show. what have I done wrong
in the iif statement.
Any help most apreciated.
Steve
From a land down under

 

[Marshall Barton]

Hi can anybody please tell me why this query criteria does not work.
IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")

I have a tick box on a form called ShowComplete that can be set to true or
false
when it is set to true I want it to show all records no matter what is in
the status field. but if the tick box is set to false I want all records
except the records with the word "Complete" to show. what have I done wrong
in the iif statement.

You are trying to construct a criteria cell for the query
designer. That really doesn't make sense when the query is
executed where a function returns a **value**, not part of
an expression.

Try setting the field's criteria:

RegisterTbl.Status Or Forms!RegisterFrm!ShowComplete=True

 

[Steve]

I dont understand what you are saying as it seems simple enough but the iif
statment just dont seem to want to accept the not the same as the word
Complete which is a value that is in the field being checked.
the field has only three possabilities Complete,Working,Checked

the form is set to Continuous Forms and all I want to be able to to is
filter out the records that have been completed by changing its recourd
source to the query. selecting a tick box that indicates show completed jobs
or show all jobs and I would have though the iif statment would have done it.
Regards
Steve
from a land down under

Hi can anybody please tell me why this query criteria does not work.
IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")

I have a tick box on a form called ShowComplete that can be set to true or
false
when it is set to true I want it to show all records no matter what is in
the status field. but if the tick box is set to false I want all records
except the records with the word "Complete" to show. what have I done wrong
in the iif statement.

You are trying to construct a criteria cell for the query
designer. That really doesn't make sense when the query is
executed where a function returns a **value**, not part of
an expression.

Try setting the field's criteria:

RegisterTbl.Status Or Forms!RegisterFrm!ShowComplete=True

 

[Dale Fye]

Steve,

I assume that your checkbox is in the header, and basically, what you want
is to either turn off the filter or turn it on. Is that correct? If so, I
would put the following code in the checkboxes AfterUpdate event.

Private sub ShowComplete_AfterUpdate

If me.ShowComplete = True then
me.filter = "[Status] = 'Complete'"
me.filteron = true
Else
me.Filter = ""
me.Filteron = False
endif

End sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I dont understand what you are saying as it seems simple enough but the iif
statment just dont seem to want to accept the not the same as the word
Complete which is a value that is in the field being checked.
the field has only three possabilities Complete,Working,Checked

the form is set to Continuous Forms and all I want to be able to to is
filter out the records that have been completed by changing its recourd
source to the query. selecting a tick box that indicates show completed jobs
or show all jobs and I would have though the iif statment would have done it.
Regards
Steve
from a land down under

Hi can anybody please tell me why this query criteria does not work.
IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")

I have a tick box on a form called ShowComplete that can be set to true or
false
when it is set to true I want it to show all records no matter what is in
the status field. but if the tick box is set to false I want all records
except the records with the word "Complete" to show. what have I done wrong
in the iif statement.

You are trying to construct a criteria cell for the query
designer. That really doesn't make sense when the query is
executed where a function returns a **value**, not part of
an expression.

Try setting the field's criteria:

RegisterTbl.Status Or Forms!RegisterFrm!ShowComplete=True

 

[Steve]

I want to filter so that I get every record that does not have the word
complete in the status field hence this is why in the iff stamnet I have put
the <> before the word Complete.
the iff statement works without the <> infront of the word Complete but of
course then when the showComplete flag is set to false I get all the records
with the status set to Complete instead of all the records without the word
Complet in the status field.
a simple filter wont do as there is two other bits of criteria acting apon
this query but at the same time I want it to show or not to show records with
the status set to complete
Hmmm Im having trouble explaining myself
so the criteria for the field status is this as it has two possabilities
that are setup by the tick box in the form header.

IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")


To me this should show all the records if the showComplete tick box is true
and NOT show records if the word Complete is in the satus field. and the
ShowComplete tick box is false. Does this make sence ?

Regards
Steve

Steve,

I assume that your checkbox is in the header, and basically, what you want
is to either turn off the filter or turn it on. Is that correct? If so, I
would put the following code in the checkboxes AfterUpdate event.

Private sub ShowComplete_AfterUpdate

If me.ShowComplete = True then
me.filter = "[Status] = 'Complete'"
me.filteron = true
Else
me.Filter = ""
me.Filteron = False
endif

End sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I dont understand what you are saying as it seems simple enough but the iif
statment just dont seem to want to accept the not the same as the word
Complete which is a value that is in the field being checked.
the field has only three possabilities Complete,Working,Checked

the form is set to Continuous Forms and all I want to be able to to is
filter out the records that have been completed by changing its recourd
source to the query. selecting a tick box that indicates show completed jobs
or show all jobs and I would have though the iif statment would have done it.
Regards
Steve
from a land down under

Steve wrote:

Hi can anybody please tell me why this query criteria does not work.
IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")

I have a tick box on a form called ShowComplete that can be set to true or
false
when it is set to true I want it to show all records no matter what is in
the status field. but if the tick box is set to false I want all records
except the records with the word "Complete" to show. what have I done wrong
in the iif statement.


You are trying to construct a criteria cell for the query
designer. That really doesn't make sense when the query is
executed where a function returns a **value**, not part of
an expression.

Try setting the field's criteria:

RegisterTbl.Status Or Forms!RegisterFrm!ShowComplete=True

 

[Dale Fye]

Where is this IIF statement? Is it in the query that is the forms
Recordsource?

IF so, post the entire SQL for that RecordSource.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I want to filter so that I get every record that does not have the word
complete in the status field hence this is why in the iff stamnet I have put
the <> before the word Complete.
the iff statement works without the <> infront of the word Complete but of
course then when the showComplete flag is set to false I get all the records
with the status set to Complete instead of all the records without the word
Complet in the status field.
a simple filter wont do as there is two other bits of criteria acting apon
this query but at the same time I want it to show or not to show records with
the status set to complete
Hmmm Im having trouble explaining myself
so the criteria for the field status is this as it has two possabilities
that are setup by the tick box in the form header.

IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")


To me this should show all the records if the showComplete tick box is true
and NOT show records if the word Complete is in the satus field. and the
ShowComplete tick box is false. Does this make sence ?

Regards
Steve

Steve,

I assume that your checkbox is in the header, and basically, what you want
is to either turn off the filter or turn it on. Is that correct? If so, I
would put the following code in the checkboxes AfterUpdate event.

Private sub ShowComplete_AfterUpdate

If me.ShowComplete = True then
me.filter = "[Status] = 'Complete'"
me.filteron = true
Else
me.Filter = ""
me.Filteron = False
endif

End sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I dont understand what you are saying as it seems simple enough but the iif
statment just dont seem to want to accept the not the same as the word
Complete which is a value that is in the field being checked.
the field has only three possabilities Complete,Working,Checked

the form is set to Continuous Forms and all I want to be able to to is
filter out the records that have been completed by changing its recourd
source to the query. selecting a tick box that indicates show completed jobs
or show all jobs and I would have though the iif statment would have done it.
Regards
Steve
from a land down under

:

Steve wrote:

Hi can anybody please tell me why this query criteria does not work.
IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")

I have a tick box on a form called ShowComplete that can be set to true or
false
when it is set to true I want it to show all records no matter what is in
the status field. but if the tick box is set to false I want all records
except the records with the word "Complete" to show. what have I done wrong
in the iif statement.


You are trying to construct a criteria cell for the query
designer. That really doesn't make sense when the query is
executed where a function returns a **value**, not part of
an expression.

Try setting the field's criteria:

RegisterTbl.Status Or Forms!RegisterFrm!ShowComplete=True

 

[Dale Fye]

The problem is that you cannot put: <> "Complete"
as the false result parameter in an IIF clause.

Let me make sure I've got this right. The field contains one of three
values (Complete, Working, Checked). What you want is to always show the
records for Working and Checked, and only show the records that match
"Complete" if the checkbox is checked.

If this is the case, try changing the criteria under the [Status] field to:

<> "Complete" OR (Forms!Request.ShowComplete = -1)

You do understand that in the checkboxes click event you will need to
requery the form, don't you?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I want to filter so that I get every record that does not have the word
complete in the status field hence this is why in the iff stamnet I have put
the <> before the word Complete.
the iff statement works without the <> infront of the word Complete but of
course then when the showComplete flag is set to false I get all the records
with the status set to Complete instead of all the records without the word
Complet in the status field.
a simple filter wont do as there is two other bits of criteria acting apon
this query but at the same time I want it to show or not to show records with
the status set to complete
Hmmm Im having trouble explaining myself
so the criteria for the field status is this as it has two possabilities
that are setup by the tick box in the form header.

IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")


To me this should show all the records if the showComplete tick box is true
and NOT show records if the word Complete is in the satus field. and the
ShowComplete tick box is false. Does this make sence ?

Regards
Steve

Steve,

I assume that your checkbox is in the header, and basically, what you want
is to either turn off the filter or turn it on. Is that correct? If so, I
would put the following code in the checkboxes AfterUpdate event.

Private sub ShowComplete_AfterUpdate

If me.ShowComplete = True then
me.filter = "[Status] = 'Complete'"
me.filteron = true
Else
me.Filter = ""
me.Filteron = False
endif

End sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I dont understand what you are saying as it seems simple enough but the iif
statment just dont seem to want to accept the not the same as the word
Complete which is a value that is in the field being checked.
the field has only three possabilities Complete,Working,Checked

the form is set to Continuous Forms and all I want to be able to to is
filter out the records that have been completed by changing its recourd
source to the query. selecting a tick box that indicates show completed jobs
or show all jobs and I would have though the iif statment would have done it.
Regards
Steve
from a land down under

:

Steve wrote:

Hi can anybody please tell me why this query criteria does not work.
IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")

I have a tick box on a form called ShowComplete that can be set to true or
false
when it is set to true I want it to show all records no matter what is in
the status field. but if the tick box is set to false I want all records
except the records with the word "Complete" to show. what have I done wrong
in the iif statement.


You are trying to construct a criteria cell for the query
designer. That really doesn't make sense when the query is
executed where a function returns a **value**, not part of
an expression.

Try setting the field's criteria:

RegisterTbl.Status Or Forms!RegisterFrm!ShowComplete=True

 

[Steve]

Thanks Dale that works great now - I wish I had known that you cant use <>
"Complete"
and Im still not sure why? Any way thanks for your help its all working fine
now thanks to your help
Regards
Steve
from a land down under

The problem is that you cannot put: <> "Complete"
as the false result parameter in an IIF clause.

Let me make sure I've got this right. The field contains one of three
values (Complete, Working, Checked). What you want is to always show the
records for Working and Checked, and only show the records that match
"Complete" if the checkbox is checked.

If this is the case, try changing the criteria under the [Status] field to:

<> "Complete" OR (Forms!Request.ShowComplete = -1)

You do understand that in the checkboxes click event you will need to
requery the form, don't you?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I want to filter so that I get every record that does not have the word
complete in the status field hence this is why in the iff stamnet I have put
the <> before the word Complete.
the iff statement works without the <> infront of the word Complete but of
course then when the showComplete flag is set to false I get all the records
with the status set to Complete instead of all the records without the word
Complet in the status field.
a simple filter wont do as there is two other bits of criteria acting apon
this query but at the same time I want it to show or not to show records with
the status set to complete
Hmmm Im having trouble explaining myself
so the criteria for the field status is this as it has two possabilities
that are setup by the tick box in the form header.

IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")


To me this should show all the records if the showComplete tick box is true
and NOT show records if the word Complete is in the satus field. and the
ShowComplete tick box is false. Does this make sence ?

Regards
Steve

Steve,

I assume that your checkbox is in the header, and basically, what you want
is to either turn off the filter or turn it on. Is that correct? If so, I
would put the following code in the checkboxes AfterUpdate event.

Private sub ShowComplete_AfterUpdate

If me.ShowComplete = True then
me.filter = "[Status] = 'Complete'"
me.filteron = true
Else
me.Filter = ""
me.Filteron = False
endif

End sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I dont understand what you are saying as it seems simple enough but the iif
statment just dont seem to want to accept the not the same as the word
Complete which is a value that is in the field being checked.
the field has only three possabilities Complete,Working,Checked

the form is set to Continuous Forms and all I want to be able to to is
filter out the records that have been completed by changing its recourd
source to the query. selecting a tick box that indicates show completed jobs
or show all jobs and I would have though the iif statment would have done it.
Regards
Steve
from a land down under

:

Steve wrote:

Hi can anybody please tell me why this query criteria does not work.
IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")

I have a tick box on a form called ShowComplete that can be set to true or
false
when it is set to true I want it to show all records no matter what is in
the status field. but if the tick box is set to false I want all records
except the records with the word "Complete" to show. what have I done wrong
in the iif statement.


You are trying to construct a criteria cell for the query
designer. That really doesn't make sense when the query is
executed where a function returns a **value**, not part of
an expression.

Try setting the field's criteria:

RegisterTbl.Status Or Forms!RegisterFrm!ShowComplete=True

 

[Dale Fye]

You can use IIF( ) statments in criteria, but I try to avoid them.

Another way to have addressed this might have been to add a computed column

IncludeComplete: Forms!RegisterFrm.ShowComplete

and set up criteria that look like:

STATUS IncludeComplete
[Status] True
<> "Complete" False


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Thanks Dale that works great now - I wish I had known that you cant use <>
"Complete"
and Im still not sure why? Any way thanks for your help its all working fine
now thanks to your help
Regards
Steve
from a land down under

The problem is that you cannot put: <> "Complete"
as the false result parameter in an IIF clause.

Let me make sure I've got this right. The field contains one of three
values (Complete, Working, Checked). What you want is to always show the
records for Working and Checked, and only show the records that match
"Complete" if the checkbox is checked.

If this is the case, try changing the criteria under the [Status] field to:

<> "Complete" OR (Forms!Request.ShowComplete = -1)

You do understand that in the checkboxes click event you will need to
requery the form, don't you?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I want to filter so that I get every record that does not have the word
complete in the status field hence this is why in the iff stamnet I have put
the <> before the word Complete.
the iff statement works without the <> infront of the word Complete but of
course then when the showComplete flag is set to false I get all the records
with the status set to Complete instead of all the records without the word
Complet in the status field.
a simple filter wont do as there is two other bits of criteria acting apon
this query but at the same time I want it to show or not to show records with
the status set to complete
Hmmm Im having trouble explaining myself
so the criteria for the field status is this as it has two possabilities
that are setup by the tick box in the form header.

IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")


To me this should show all the records if the showComplete tick box is true
and NOT show records if the word Complete is in the satus field. and the
ShowComplete tick box is false. Does this make sence ?

Regards
Steve


:

Steve,

I assume that your checkbox is in the header, and basically, what you want
is to either turn off the filter or turn it on. Is that correct? If so, I
would put the following code in the checkboxes AfterUpdate event.

Private sub ShowComplete_AfterUpdate

If me.ShowComplete = True then
me.filter = "[Status] = 'Complete'"
me.filteron = true
Else
me.Filter = ""
me.Filteron = False
endif

End sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I dont understand what you are saying as it seems simple enough but the iif
statment just dont seem to want to accept the not the same as the word
Complete which is a value that is in the field being checked.
the field has only three possabilities Complete,Working,Checked

the form is set to Continuous Forms and all I want to be able to to is
filter out the records that have been completed by changing its recourd
source to the query. selecting a tick box that indicates show completed jobs
or show all jobs and I would have though the iif statment would have done it.
Regards
Steve
from a land down under

:

Steve wrote:

Hi can anybody please tell me why this query criteria does not work.
IIf([Forms]![RegisterFrm]![ShowComplete]=True,[RegisterTbl]![Status],<>"Complete")

I have a tick box on a form called ShowComplete that can be set to true or
false
when it is set to true I want it to show all records no matter what is in
the status field. but if the tick box is set to false I want all records
except the records with the word "Complete" to show. what have I done wrong
in the iif statement.


You are trying to construct a criteria cell for the query
designer. That really doesn't make sense when the query is
executed where a function returns a **value**, not part of
an expression.

Try setting the field's criteria:

RegisterTbl.Status Or Forms!RegisterFrm!ShowComplete=True

 

[Marshall Barton]

Thanks Dale that works great now - I wish I had known that you cant use <>
"Complete" and Im still not sure why?

It might help you to understand what I was trying to explain
if you switch your IIf query to SQL view and look at the
WHERE clause. Then do the same with the criteria I
suggested.

 

[John W. Vinson]

I wish I had known that you cant use <>
"Complete"
and Im still not sure why?

Because IIF returns *A VALUE*. You're not returning a value; you are trying to
return an operator <> and also a value. In the same vein, you cannot use the
LIKE operator or the IN() operator in an IIF.