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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|