Query returns no records when "WHERE" is included when I use IIF &

[Hugh self taught]

Hi,

I have a Male (Yes/No) field & my lookup query must return all non male if
the "Male" field is Yes & visa versa, but as soon as I add the WHERE line, no
records show. Below is the query

SELECT tblNationalMaster.NatMaster_Idx, tblNationalMaster.[FIRST NAME] & " "
& tblNationalMaster.SURNAME AS NameMaster, tblNationalMaster.[FIRST NAME],
tblNationalMaster.SURNAME, IIf([tblNationalMaster.Male],Yes,No) AS Gender,
tblNationalMaster.Male
FROM tblNationalMaster
ORDER BY tblNationalMaster.[FIRST NAME] & " " & tblNationalMaster.SURNAME;

The Where line I added is WHERE (((tblNationalMaster.Male)<>[Gender]))

I also tried using a subquery but then got "FROM CLAUSE" errors even though
my syntax is copied from the help axample online

 

[Allen Browne]

So Male is a Yes/No field.

The expression:
IIf([tblNationalMaster.Male],Yes,No) AS Gender
returns Yes in the records where Male is Yes, and No in the fields where
Male is No.

It is therefore identical to the Male field, and so there are NO records
where it is different to the Male field.

Consequently, the clause:
WHERE (((tblNationalMaster.Male)<>[Gender]))
can never return any records.

Did you mean:
WHERE tblNationalMaster.Male = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Hugh self taught]

I was playing with this at 2 am & experimented unsuccessfully with several
options, however my goal is to show all the non-males when the current record
is male & male records when the current is non-male. Can you point me in the
right direction

So Male is a Yes/No field.

The expression:
IIf([tblNationalMaster.Male],Yes,No) AS Gender
returns Yes in the records where Male is Yes, and No in the fields where
Male is No.

It is therefore identical to the Male field, and so there are NO records
where it is different to the Male field.

Consequently, the clause:
WHERE (((tblNationalMaster.Male)<>[Gender]))
can never return any records.

Did you mean:
WHERE tblNationalMaster.Male = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Hi,

I have a Male (Yes/No) field & my lookup query must return all non male if
the "Male" field is Yes & visa versa, but as soon as I add the WHERE line,
no
records show. Below is the query

SELECT tblNationalMaster.NatMaster_Idx, tblNationalMaster.[FIRST NAME] & "
"
& tblNationalMaster.SURNAME AS NameMaster, tblNationalMaster.[FIRST NAME],
tblNationalMaster.SURNAME, IIf([tblNationalMaster.Male],Yes,No) AS Gender,
tblNationalMaster.Male
FROM tblNationalMaster
ORDER BY tblNationalMaster.[FIRST NAME] & " " & tblNationalMaster.SURNAME;

The Where line I added is WHERE (((tblNationalMaster.Male)<>[Gender]))

I also tried using a subquery but then got "FROM CLAUSE" errors even
though
my syntax is copied from the help axample online

 

[Allen Browne]

In what context do you mean 'current record'?

If you are talking about the current record in a form, you might use:
WHERE tblNationalMaster.Male <>[Forms].[Form1].[Male]

In a query datasheet, the WHERE clause is evaluated based on each record, so
that can't work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I was playing with this at 2 am & experimented unsuccessfully with several
options, however my goal is to show all the non-males when the current
record
is male & male records when the current is non-male. Can you point me in
the
right direction

So Male is a Yes/No field.

The expression:
IIf([tblNationalMaster.Male],Yes,No) AS Gender
returns Yes in the records where Male is Yes, and No in the fields where
Male is No.

It is therefore identical to the Male field, and so there are NO records
where it is different to the Male field.

Consequently, the clause:
WHERE (((tblNationalMaster.Male)<>[Gender]))
can never return any records.

Did you mean:
WHERE tblNationalMaster.Male = False

message

Hi,

I have a Male (Yes/No) field & my lookup query must return all non male
if
the "Male" field is Yes & visa versa, but as soon as I add the WHERE
line,
no
records show. Below is the query

SELECT tblNationalMaster.NatMaster_Idx, tblNationalMaster.[FIRST NAME]
& "
"
& tblNationalMaster.SURNAME AS NameMaster, tblNationalMaster.[FIRST
NAME],
tblNationalMaster.SURNAME, IIf([tblNationalMaster.Male],Yes,No) AS
Gender,
tblNationalMaster.Male
FROM tblNationalMaster
ORDER BY tblNationalMaster.[FIRST NAME] & " " &
tblNationalMaster.SURNAME;

The Where line I added is WHERE (((tblNationalMaster.Male)<>[Gender]))

I also tried using a subquery but then got "FROM CLAUSE" errors even
though
my syntax is copied from the help axample online

 

[Hugh self taught]

Thanks for that Allen. However now I either get only males for all or females
for all. This particular field is on a subform where the "Male" field is on
the main form. When I scroll through the records on the Main form the "Male"
check box shows the correct selection. If I change the subform view to
datasheet then it changes male / non-male but stays on that choice. It
appears to keep the choice of whichever record was displayed first but
changes when changing between datasheet view & form view of the subform. Any
suggestions why ?

In what context do you mean 'current record'?

If you are talking about the current record in a form, you might use:
WHERE tblNationalMaster.Male <>[Forms].[Form1].[Male]

In a query datasheet, the WHERE clause is evaluated based on each record, so
that can't work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I was playing with this at 2 am & experimented unsuccessfully with several
options, however my goal is to show all the non-males when the current
record
is male & male records when the current is non-male. Can you point me in
the
right direction

So Male is a Yes/No field.

The expression:
IIf([tblNationalMaster.Male],Yes,No) AS Gender
returns Yes in the records where Male is Yes, and No in the fields where
Male is No.

It is therefore identical to the Male field, and so there are NO records
where it is different to the Male field.

Consequently, the clause:
WHERE (((tblNationalMaster.Male)<>[Gender]))
can never return any records.

Did you mean:
WHERE tblNationalMaster.Male = False

message
Hi,

I have a Male (Yes/No) field & my lookup query must return all non male
if
the "Male" field is Yes & visa versa, but as soon as I add the WHERE
line,
no
records show. Below is the query

SELECT tblNationalMaster.NatMaster_Idx, tblNationalMaster.[FIRST NAME]
& "
"
& tblNationalMaster.SURNAME AS NameMaster, tblNationalMaster.[FIRST
NAME],
tblNationalMaster.SURNAME, IIf([tblNationalMaster.Male],Yes,No) AS
Gender,
tblNationalMaster.Male
FROM tblNationalMaster
ORDER BY tblNationalMaster.[FIRST NAME] & " " &
tblNationalMaster.SURNAME;

The Where line I added is WHERE (((tblNationalMaster.Male)<>[Gender]))

I also tried using a subquery but then got "FROM CLAUSE" errors even
though
my syntax is copied from the help axample online

 

[Allen Browne]

So you want to load the *main* form, with the gender that's oppositite to
the value in the subform?

You could try this kind of thing:
WHERE Male <>[Forms]![Form1]![Sub1].[Form]![Male]
Explanation in:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

However, that could still give problems. The main form can't load until the
subform is loaded, but the subform can't load until the main form is loaded.
There seems to be some circular logic here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for that Allen. However now I either get only males for all or
females
for all. This particular field is on a subform where the "Male" field is
on
the main form. When I scroll through the records on the Main form the
"Male"
check box shows the correct selection. If I change the subform view to
datasheet then it changes male / non-male but stays on that choice. It
appears to keep the choice of whichever record was displayed first but
changes when changing between datasheet view & form view of the subform.
Any
suggestions why ?

In what context do you mean 'current record'?

If you are talking about the current record in a form, you might use:
WHERE tblNationalMaster.Male <>[Forms].[Form1].[Male]

In a query datasheet, the WHERE clause is evaluated based on each record,
so
that can't work.

message

I was playing with this at 2 am & experimented unsuccessfully with
several
options, however my goal is to show all the non-males when the current
record
is male & male records when the current is non-male. Can you point me
in
the
right direction

:

So Male is a Yes/No field.

The expression:
IIf([tblNationalMaster.Male],Yes,No) AS Gender
returns Yes in the records where Male is Yes, and No in the fields
where
Male is No.

It is therefore identical to the Male field, and so there are NO
records
where it is different to the Male field.

Consequently, the clause:
WHERE (((tblNationalMaster.Male)<>[Gender]))
can never return any records.

Did you mean:
WHERE tblNationalMaster.Male = False

in
message
Hi,

I have a Male (Yes/No) field & my lookup query must return all non
male
if
the "Male" field is Yes & visa versa, but as soon as I add the WHERE
line,
no
records show. Below is the query

SELECT tblNationalMaster.NatMaster_Idx, tblNationalMaster.[FIRST
NAME]
& "
"
& tblNationalMaster.SURNAME AS NameMaster, tblNationalMaster.[FIRST
NAME],
tblNationalMaster.SURNAME, IIf([tblNationalMaster.Male],Yes,No) AS
Gender,
tblNationalMaster.Male
FROM tblNationalMaster
ORDER BY tblNationalMaster.[FIRST NAME] & " " &
tblNationalMaster.SURNAME;

The Where line I added is WHERE
(((tblNationalMaster.Male)<>[Gender]))

I also tried using a subquery but then got "FROM CLAUSE" errors even
though
my syntax is copied from the help axample online

 

[Hugh self taught]

I must be missing something so I made a complete new db importing only the
data from my original. I then auto created a form from the table. I then
changed the "partner" text box to a combo box, adding the following sql
statement. Problem is when the "male" changes on the form, the records listed
doesn't.

SELECT tblNationalMaster.NatMaster_Idx, tblNationalMaster.FirstName,
tblNationalMaster.Surname, tblNationalMaster.FirstName & " " &
tblNationalMaster.Surname AS Name, tblNationalMaster.Male
FROM tblNationalMaster
WHERE ((([Forms]![frmNationalMaster].[Male])<>[tblNationalMaster].[Male]))
ORDER BY tblNationalMaster.FirstName & " " & tblNationalMaster.Surname;

To my way of thinking the table data needs to be reloaded/re-read whenever
the "male" value changes. Currently since the first record is male, "Yes",
the resulting records are all "No" no matter what the field value of the form
is. My problem is how to achieve that.

So you want to load the *main* form, with the gender that's oppositite to
the value in the subform?

You could try this kind of thing:
WHERE Male <>[Forms]![Form1]![Sub1].[Form]![Male]
Explanation in:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

However, that could still give problems. The main form can't load until the
subform is loaded, but the subform can't load until the main form is loaded.
There seems to be some circular logic here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for that Allen. However now I either get only males for all or
females
for all. This particular field is on a subform where the "Male" field is
on
the main form. When I scroll through the records on the Main form the
"Male"
check box shows the correct selection. If I change the subform view to
datasheet then it changes male / non-male but stays on that choice. It
appears to keep the choice of whichever record was displayed first but
changes when changing between datasheet view & form view of the subform.
Any
suggestions why ?

In what context do you mean 'current record'?

If you are talking about the current record in a form, you might use:
WHERE tblNationalMaster.Male <>[Forms].[Form1].[Male]

In a query datasheet, the WHERE clause is evaluated based on each record,
so
that can't work.

message
I was playing with this at 2 am & experimented unsuccessfully with
several
options, however my goal is to show all the non-males when the current
record
is male & male records when the current is non-male. Can you point me
in
the
right direction

:

So Male is a Yes/No field.

The expression:
IIf([tblNationalMaster.Male],Yes,No) AS Gender
returns Yes in the records where Male is Yes, and No in the fields
where
Male is No.

It is therefore identical to the Male field, and so there are NO
records
where it is different to the Male field.

Consequently, the clause:
WHERE (((tblNationalMaster.Male)<>[Gender]))
can never return any records.

Did you mean:
WHERE tblNationalMaster.Male = False

in
message
Hi,

I have a Male (Yes/No) field & my lookup query must return all non
male
if
the "Male" field is Yes & visa versa, but as soon as I add the WHERE
line,
no
records show. Below is the query

SELECT tblNationalMaster.NatMaster_Idx, tblNationalMaster.[FIRST
NAME]
& "
"
& tblNationalMaster.SURNAME AS NameMaster, tblNationalMaster.[FIRST
NAME],
tblNationalMaster.SURNAME, IIf([tblNationalMaster.Male],Yes,No) AS
Gender,
tblNationalMaster.Male
FROM tblNationalMaster
ORDER BY tblNationalMaster.[FIRST NAME] & " " &
tblNationalMaster.SURNAME;

The Where line I added is WHERE
(((tblNationalMaster.Male)<>[Gender]))

I also tried using a subquery but then got "FROM CLAUSE" errors even
though
my syntax is copied from the help axample online

 

[Allen Browne]

Perhaps you could Requery the subform.

Or, it might be easier to add a text box with properties:
Control Source =Not([Male])
Format Yes/No
Visible No
Name txtNotMale

Then use this text box in the subform's linking. In form design view,
right-click the edge of the subform control, and choose Properties. On the
Data tab of the Properties sheet, set:
Link Master Fields txtNotMale
Link Child Fields Male

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I must be missing something so I made a complete new db importing only
the data from my original. I then auto created a form from the table. I
then
changed the "partner" text box to a combo box, adding the following sql
statement. Problem is when the "male" changes on the form, the records
listed doesn't.

SELECT tblNationalMaster.NatMaster_Idx, tblNationalMaster.FirstName,
tblNationalMaster.Surname, tblNationalMaster.FirstName & " " &
tblNationalMaster.Surname AS Name, tblNationalMaster.Male
FROM tblNationalMaster
WHERE ((([Forms]![frmNationalMaster].[Male])<>[tblNationalMaster].[Male]))
ORDER BY tblNationalMaster.FirstName & " " & tblNationalMaster.Surname;

To my way of thinking the table data needs to be reloaded/re-read whenever
the "male" value changes. Currently since the first record is male, "Yes",
the resulting records are all "No" no matter what the field value of the
form
is. My problem is how to achieve that.

So you want to load the *main* form, with the gender that's oppositite to
the value in the subform?

You could try this kind of thing:
WHERE Male <>[Forms]![Form1]![Sub1].[Form]![Male]
Explanation in:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

However, that could still give problems. The main form can't load until
the
subform is loaded, but the subform can't load until the main form is
loaded.
There seems to be some circular logic here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for that Allen. However now I either get only males for all or
females
for all. This particular field is on a subform where the "Male" field
is
on
the main form. When I scroll through the records on the Main form the
"Male"
check box shows the correct selection. If I change the subform view to
datasheet then it changes male / non-male but stays on that choice. It
appears to keep the choice of whichever record was displayed first but
changes when changing between datasheet view & form view of the
subform.
Any
suggestions why ?

:

In what context do you mean 'current record'?

If you are talking about the current record in a form, you might use:
WHERE tblNationalMaster.Male <>[Forms].[Form1].[Male]

In a query datasheet, the WHERE clause is evaluated based on each
record,
so
that can't work.

message
I was playing with this at 2 am & experimented unsuccessfully with
several
options, however my goal is to show all the non-males when the
current
record
is male & male records when the current is non-male. Can you point
me
in
the
right direction

:

So Male is a Yes/No field.

The expression:
IIf([tblNationalMaster.Male],Yes,No) AS Gender
returns Yes in the records where Male is Yes, and No in the fields
where
Male is No.

It is therefore identical to the Male field, and so there are NO
records
where it is different to the Male field.

Consequently, the clause:
WHERE (((tblNationalMaster.Male)<>[Gender]))
can never return any records.

Did you mean:
WHERE tblNationalMaster.Male = False

"Hugh self taught" <Hugh self (e-mail address removed)>
wrote
in
message
Hi,

I have a Male (Yes/No) field & my lookup query must return all
non
male
if
the "Male" field is Yes & visa versa, but as soon as I add the
WHERE
line,
no
records show. Below is the query

SELECT tblNationalMaster.NatMaster_Idx, tblNationalMaster.[FIRST
NAME]
& "
"
& tblNationalMaster.SURNAME AS NameMaster,
tblNationalMaster.[FIRST
NAME],
tblNationalMaster.SURNAME, IIf([tblNationalMaster.Male],Yes,No)
AS
Gender,
tblNationalMaster.Male
FROM tblNationalMaster
ORDER BY tblNationalMaster.[FIRST NAME] & " " &
tblNationalMaster.SURNAME;

The Where line I added is WHERE
(((tblNationalMaster.Male)<>[Gender]))

I also tried using a subquery but then got "FROM CLAUSE" errors
even
though
my syntax is copied from the help axample online

 

[Hugh self taught]

I've tried your suggestion of a text box on the subform but I can't link it.
My master fields are from the main form's table & the child from the
subform's table. It also won't allow me to link to the text box on the
subform & I'm pretty sure I used the correct syntax....

Perhaps you could Requery the subform.

Or, it might be easier to add a text box with properties:
Control Source =Not([Male])
Format Yes/No
Visible No
Name txtNotMale

Then use this text box in the subform's linking. In form design view,
right-click the edge of the subform control, and choose Properties. On the
Data tab of the Properties sheet, set:
Link Master Fields txtNotMale
Link Child Fields Male

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I must be missing something so I made a complete new db importing only
the data from my original. I then auto created a form from the table. I
then
changed the "partner" text box to a combo box, adding the following sql
statement. Problem is when the "male" changes on the form, the records
listed doesn't.

SELECT tblNationalMaster.NatMaster_Idx, tblNationalMaster.FirstName,
tblNationalMaster.Surname, tblNationalMaster.FirstName & " " &
tblNationalMaster.Surname AS Name, tblNationalMaster.Male
FROM tblNationalMaster
WHERE ((([Forms]![frmNationalMaster].[Male])<>[tblNationalMaster].[Male]))
ORDER BY tblNationalMaster.FirstName & " " & tblNationalMaster.Surname;

To my way of thinking the table data needs to be reloaded/re-read whenever
the "male" value changes. Currently since the first record is male, "Yes",
the resulting records are all "No" no matter what the field value of the
form
is. My problem is how to achieve that.

So you want to load the *main* form, with the gender that's oppositite to
the value in the subform?

You could try this kind of thing:
WHERE Male <>[Forms]![Form1]![Sub1].[Form]![Male]
Explanation in:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

However, that could still give problems. The main form can't load until
the
subform is loaded, but the subform can't load until the main form is
loaded.
There seems to be some circular logic here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
message Thanks for that Allen. However now I either get only males for all or
females
for all. This particular field is on a subform where the "Male" field
is
on
the main form. When I scroll through the records on the Main form the
"Male"
check box shows the correct selection. If I change the subform view to
datasheet then it changes male / non-male but stays on that choice. It
appears to keep the choice of whichever record was displayed first but
changes when changing between datasheet view & form view of the
subform.
Any
suggestions why ?

:

In what context do you mean 'current record'?

If you are talking about the current record in a form, you might use:
WHERE tblNationalMaster.Male <>[Forms].[Form1].[Male]

In a query datasheet, the WHERE clause is evaluated based on each
record,
so
that can't work.

message
I was playing with this at 2 am & experimented unsuccessfully with
several
options, however my goal is to show all the non-males when the
current
record
is male & male records when the current is non-male. Can you point
me
in
the
right direction

:

So Male is a Yes/No field.

The expression:
IIf([tblNationalMaster.Male],Yes,No) AS Gender
returns Yes in the records where Male is Yes, and No in the fields
where
Male is No.

It is therefore identical to the Male field, and so there are NO
records
where it is different to the Male field.

Consequently, the clause:
WHERE (((tblNationalMaster.Male)<>[Gender]))
can never return any records.

Did you mean:
WHERE tblNationalMaster.Male = False

"Hugh self taught" <Hugh self (e-mail address removed)>
wrote
in
message
Hi,

I have a Male (Yes/No) field & my lookup query must return all
non
male
if
the "Male" field is Yes & visa versa, but as soon as I add the
WHERE
line,
no
records show. Below is the query

SELECT tblNationalMaster.NatMaster_Idx, tblNationalMaster.[FIRST
NAME]
& "
"
& tblNationalMaster.SURNAME AS NameMaster,
tblNationalMaster.[FIRST
NAME],
tblNationalMaster.SURNAME, IIf([tblNationalMaster.Male],Yes,No)
AS
Gender,
tblNationalMaster.Male
FROM tblNationalMaster
ORDER BY tblNationalMaster.[FIRST NAME] & " " &
tblNationalMaster.SURNAME;

The Where line I added is WHERE
(((tblNationalMaster.Male)<>[Gender]))

I also tried using a subquery but then got "FROM CLAUSE" errors
even
though
my syntax is copied from the help axample online

 

[Allen Browne]

I don't understand the issue here.
Do you get an error message?

You should be able to use a text box name in the
LinkMasterFields/LinkChildFields properties.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I've tried your suggestion of a text box on the subform but I can't link
it.
My master fields are from the main form's table & the child from the
subform's table. It also won't allow me to link to the text box on the
subform & I'm pretty sure I used the correct syntax....

Perhaps you could Requery the subform.

Or, it might be easier to add a text box with properties:
Control Source =Not([Male])
Format Yes/No
Visible No
Name txtNotMale

Then use this text box in the subform's linking. In form design view,
right-click the edge of the subform control, and choose Properties. On
the
Data tab of the Properties sheet, set:
Link Master Fields txtNotMale
Link Child Fields Male

message

I must be missing something so I made a complete new db importing only
the data from my original. I then auto created a form from the table. I
then
changed the "partner" text box to a combo box, adding the following sql
statement. Problem is when the "male" changes on the form, the records
listed doesn't.