HELP PLEASE: develop a custom screener

[sam]

Hi All,

I am trying to build a student screener in access. Basically it’s going to
screen students from different school zones and other criteria.. So
basically.. Here is how I want it to work:

I have 4 dropdown lists:

School, Gender, Physical_attributes, Conditions

The values of these dropdown lists are:

School_Zone: West, North, East, South
Gender: Male, Female
Physical_attributes: Height, Weight
Conditions: =,<,>
And a Textbox

And there is a button "Search" .

So now,
Once I select a school from the dropdown list and click search, I see the
results based on what I selected for school only.

If I Select a School and a Gender, I see the results based on what I
selected for School and Gender..

If I Select “South†for School, “Height" for Physical_Attributes and “>"
from conditions dropdown and input "147" in the textbox, I see the results
based on what I selected for School, Physical_Attributes, Conditions and the
value in textbox.

So basically, the search criteria’s are independent of each other, But if
you select multiple search criteria’s you should see the results based on
what you selected for all the search criteria’s.

Here is the code I have so far:

StrSql = "SELECT * FROM Students_Table" & _
" WHERE Students_Table.Zone = " & _
Me.Zone.Value & _
" AND " & _
" Students_Table.Gender = " & _
Me.Gender.Value & _
" AND " & _
"Students_Table.Physical_Attributes = " & _
Me.PhysicalAtt.Value & _
" AND " & _
Me.conditions.Value & _
Me.TextBox.Value

Also, Once I click the "Search" Button, How can I make it display at the
bottown of the form?

Hope I was clear

Thanks in advance

 

[Gina Whipp]

Sam,

Well, this should help...

http://allenbrowne.com/ser-62.html

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi All,

I am trying to build a student screener in access. Basically it’s going to
screen students from different school zones and other criteria.. So
basically.. Here is how I want it to work:

I have 4 dropdown lists:

School, Gender, Physical_attributes, Conditions

The values of these dropdown lists are:

School_Zone: West, North, East, South
Gender: Male, Female
Physical_attributes: Height, Weight
Conditions: =,<,>
And a Textbox

And there is a button "Search" .

So now,
Once I select a school from the dropdown list and click search, I see the
results based on what I selected for school only.

If I Select a School and a Gender, I see the results based on what I
selected for School and Gender..

If I Select “South†for School, “Height" for Physical_Attributes and “>"
from conditions dropdown and input "147" in the textbox, I see the results
based on what I selected for School, Physical_Attributes, Conditions and the
value in textbox.

So basically, the search criteria’s are independent of each other, But if
you select multiple search criteria’s you should see the results based on
what you selected for all the search criteria’s.

Here is the code I have so far:

StrSql = "SELECT * FROM Students_Table" & _
" WHERE Students_Table.Zone = " & _
Me.Zone.Value & _
" AND " & _
" Students_Table.Gender = " & _
Me.Gender.Value & _
" AND " & _
"Students_Table.Physical_Attributes = " & _
Me.PhysicalAtt.Value & _
" AND " & _
Me.conditions.Value & _
Me.TextBox.Value

Also, Once I click the "Search" Button, How can I make it display at the
bottown of the form?

Hope I was clear

Thanks in advance

 

[Marshall Barton]

I am trying to build a student screener in access. Basically it’s going to
screen students from different school zones and other criteria.. So
basically.. Here is how I want it to work:

I have 4 dropdown lists:

School, Gender, Physical_attributes, Conditions

The values of these dropdown lists are:

School_Zone: West, North, East, South
Gender: Male, Female
Physical_attributes: Height, Weight
Conditions: =,<,>
And a Textbox

And there is a button "Search" .

So now,
Once I select a school from the dropdown list and click search, I see the
results based on what I selected for school only.

If I Select a School and a Gender, I see the results based on what I
selected for School and Gender..

If I Select “South” for School, “Height" for Physical_Attributes and “>"
from conditions dropdown and input "147" in the textbox, I see the results
based on what I selected for School, Physical_Attributes, Conditions and the
value in textbox.

So basically, the search criteria’s are independent of each other, But if
you select multiple search criteria’s you should see the results based on
what you selected for all the search criteria’s.

Here is the code I have so far:

StrSql = "SELECT * FROM Students_Table" & _
" WHERE Students_Table.Zone = " & _
Me.Zone.Value & _
" AND " & _
" Students_Table.Gender = " & _
Me.Gender.Value & _
" AND " & _
"Students_Table.Physical_Attributes = " & _
Me.PhysicalAtt.Value & _
" AND " & _
Me.conditions.Value & _
Me.TextBox.Value

Also, Once I click the "Search" Button, How can I make it display at the
bottown of the form?

If Not IsNull(Me.Zone) Then
strCriteria = " AND Zone = " & Me.Zone
End If

If Not IsNull(Me.Gender) Then
strCriteria = " AND Gender = " & Me.Gender
End If

If Not (IsNull(Me.PhysicalAtt) _
Or IsNull(Me.conditions) _
Or IsNull(Me.TextBox)) Then
strCriteria = " AND " & Me.PhysicalAtt & Me.conditions _
& Me.TextBox
End If

StrSql = "SELECT * FROM Students_Table "
If Len(strCriteria) > 0 Then
StrSql = StrSql & "WHERE " & Mid(strCriteria,6)
End If

 

[sam]

Thanks for the help marshall,

Do you know how to display the results at the bottom of the screener?

 

[Marshall Barton]

You can use a moderately large text box in the form's footer
section. Then add a line of code:

Me.txtCriteria = Mid(strCriteria,6)