Need help with changing query

[Afrosheen via AccessMonster.com]

First of all, thanks for reading this.

Here's my problem. I have 1 field called [sex] and another called [race]. At
one time the word sex was a text field Male or Female. I've changed the field
properties to either yes/no true false. Yes = Female and No = Male.

The code in the sql/query below is using the words Male or Female {list5}.
The [race] seems to be ok.
I need to change the sex part to either true or false even though on the
screen it will show Male/Female

SELECT qryEmpMaster.Lname, qryEmpMaster.Fname, qryEmpMaster.Sex, qryEmpMaster.
Race
FROM qryEmpMaster
WHERE 1=1 And IIf(Not isnull(Forms!frmGender!List5),qryEmpMaster.Sex=Forms!
frmGender!List5,1=1) And IIf(Not isnull(Forms!frmGender!combo7),qryEmpMaster.
Race=Forms!frmGender!combo7,1=1);

The list5 is a list box Male or Female. The combo7 is the race.

Thanks in advance for your help. I hope I've explained it correctly.

 

[KARL DEWEY]

I do not follow what you have in the WHERE 1=1 And IIf(Not
isnull(Forms!frmGender!List5),qryEmpMaster.Sex=Forms!
frmGender!List5,1=1) And IIf(Not isnull(Forms!frmGender!combo7),qryEmpMaster.
Race=Forms!frmGender!combo7,1=1);

Can you break it down for me? Do you want to test qryEmpMaster.Sex to match
Forms!frmGender!List5?

I am guessing but maybe this will do it for you --
WHERE qryEmpMaster.Sex Like IIf(Forms!frmGender!List5 Is Not Null,
IIF(Forms!frmGender!List5 = "Female", -1, 0), "*") AND qryEmpMaster.Race Like
IIF(Forms!frmGender!combo7 Is Not Null, Forms!frmGender!combo7,"*");

 

[Afrosheen via AccessMonster.com]

Thanks for getting back to me Karl.
I don't remember where I got the WHERE 1=1. The list 5 is a list box with
Male and Female. What it's supposed to do is when I select the Male then it
will give me all the male personal. Then I'd select the race from the combo
box. If I want White Males then it will give me a list of the white males.

If I don't select anything then it give me a list of all personal any race,
male or female.

Where the problem is that in my tables I had fields [male] - checkbox,
[female] checkbox, & [race] textbox. Since the [male] and [female] were
either true or false. I decide to take them out and use one called [sex]. So
if I choose female then the [sex] would be false and vice versa on the male.

The (form!frmGender!list5) is the list box in my form called frmGender.

I hope that kinda explains it better.

By the way I copied and pasted your part in to the query and all I got was
females even when I selected males..

I do not follow what you have in the WHERE 1=1 And IIf(Not
isnull(Forms!frmGender!List5),qryEmpMaster.Sex=Forms!
frmGender!List5,1=1) And IIf(Not isnull(Forms!frmGender!combo7),qryEmpMaster.
Race=Forms!frmGender!combo7,1=1);

Can you break it down for me? Do you want to test qryEmpMaster.Sex to match
Forms!frmGender!List5?

I am guessing but maybe this will do it for you --
WHERE qryEmpMaster.Sex Like IIf(Forms!frmGender!List5 Is Not Null,
IIF(Forms!frmGender!List5 = "Female", -1, 0), "*") AND qryEmpMaster.Race Like
IIF(Forms!frmGender!combo7 Is Not Null, Forms!frmGender!combo7,"*");

First of all, thanks for reading this.

[quoted text clipped - 17 lines]

Thanks in advance for your help. I hope I've explained it correctly.

 

[KARL DEWEY]

Try adding this in design view to see what is being fed to your query --
Just_Checking: [Forms]![frmGender]![List5]

Run query for females and again for males.

--
Build a little, test a little.

Thanks for getting back to me Karl.
I don't remember where I got the WHERE 1=1. The list 5 is a list box with
Male and Female. What it's supposed to do is when I select the Male then it
will give me all the male personal. Then I'd select the race from the combo
box. If I want White Males then it will give me a list of the white males.

If I don't select anything then it give me a list of all personal any race,
male or female.

Where the problem is that in my tables I had fields [male] - checkbox,
[female] checkbox, & [race] textbox. Since the [male] and [female] were
either true or false. I decide to take them out and use one called [sex]. So
if I choose female then the [sex] would be false and vice versa on the male.

The (form!frmGender!list5) is the list box in my form called frmGender.

I hope that kinda explains it better.

By the way I copied and pasted your part in to the query and all I got was
females even when I selected males..

I do not follow what you have in the WHERE 1=1 And IIf(Not
isnull(Forms!frmGender!List5),qryEmpMaster.Sex=Forms!
frmGender!List5,1=1) And IIf(Not isnull(Forms!frmGender!combo7),qryEmpMaster.
Race=Forms!frmGender!combo7,1=1);

Can you break it down for me? Do you want to test qryEmpMaster.Sex to match
Forms!frmGender!List5?

I am guessing but maybe this will do it for you --
WHERE qryEmpMaster.Sex Like IIf(Forms!frmGender!List5 Is Not Null,
IIF(Forms!frmGender!List5 = "Female", -1, 0), "*") AND qryEmpMaster.Race Like
IIF(Forms!frmGender!combo7 Is Not Null, Forms!frmGender!combo7,"*");

First of all, thanks for reading this.

[quoted text clipped - 17 lines]

Thanks in advance for your help. I hope I've explained it correctly.

--



.

 

[Afrosheen via AccessMonster.com]

[Forms]![frmGender]![List5] is a list box from the frmGender form

When I run the query from design mode a dialog box will pop up asking me what
I want in the List5 field. That would either be female or male.

I wish there was a way of pulling up the questions I've asked. I could get
the original question that brought about the query and the way it was written.

Try adding this in design view to see what is being fed to your query --
Just_Checking: [Forms]![frmGender]![List5]

Run query for females and again for males.

Thanks for getting back to me Karl.
I don't remember where I got the WHERE 1=1. The list 5 is a list box with

[quoted text clipped - 35 lines]