A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need help with changing query



 
 
Thread Tools Display Modes
  #1  
Old February 12th, 2010, 02:59 PM posted to microsoft.public.access.queries
Afrosheen via AccessMonster.com
external usenet poster
 
Posts: 70
Default Need help with changing query

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=For ms!
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

  #2  
Old February 12th, 2010, 11:20 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Need help with changing query

I do not follow what you have in the WHERE 1=1 And IIf(Not
isnull(Forms!frmGender!List5),qryEmpMaster.Sex=For ms!
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,"*");


--
Build a little, test a little.


"Afrosheen via AccessMonster.com" wrote:

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=For ms!
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

.

  #3  
Old February 12th, 2010, 11:56 PM posted to microsoft.public.access.queries
Afrosheen via AccessMonster.com
external usenet poster
 
Posts: 70
Default Need help with changing query

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..


KARL DEWEY wrote:
I do not follow what you have in the WHERE 1=1 And IIf(Not
isnull(Forms!frmGender!List5),qryEmpMaster.Sex=Fo rms!
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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

  #4  
Old February 13th, 2010, 12:50 AM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Need help with changing query

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.


"Afrosheen via AccessMonster.com" wrote:

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..


KARL DEWEY wrote:
I do not follow what you have in the WHERE 1=1 And IIf(Not
isnull(Forms!frmGender!List5),qryEmpMaster.Sex=Fo rms!
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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

.

  #5  
Old February 13th, 2010, 02:02 AM posted to microsoft.public.access.queries
Afrosheen via AccessMonster.com
external usenet poster
 
Posts: 70
Default Need help with changing query

[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.


KARL DEWEY wrote:
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]

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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.