Query using conditional statments

[thewrightdesign]

Thought I would start a new thread for this one even though I'm building off the last issue.

I have a single table db that now has a search form to find any combination of Institution, Pass/fail status, and begin and end dates. I am needing to now add in searches for some of the testing results. Three fields (R, M, and L) and I need to be able to search for below 8.0, 8.0 and above, and 10.0 and above on all three fields. Currently the query code looks like this:

SELECT GEDdata.[DOC#], GEDdata.Lastname, GEDdata.Firstname, GEDdata.Institution, GEDdata.ResultDate, GEDdata.PASS, GEDdata.Score, GEDdata.LAW, GEDdata.SS, GEDdata.SC, GEDdata.LAR, GEDdata.MAT, GEDdata.R, GEDdata.M, GEDdata.L, GEDdata.PLAW, GEDdata.PSS, GEDdata.PSC, GEDdata.PLAR, GEDdata.PMAT, GEDdata.PTOTAL
FROM GEDdata
WHERE ((Institution=Forms!SearchForm!cboInstitution Or Forms!SearchForm!cboInstitution Is Null) And
(PASS=Forms!SearchForm!cboPass Or Forms!SearchForm!cboPass Is Null) And
(ResultDate Between Nz(Forms!SearchForm!txtStart,Forms!SearchForm!txtAltStart) And Nz(Forms!SearchForm!txtEnd,Forms!SearchForm!txtAltEnd)));

Not really sure how to proceed to get the conditional statements added to this code. Do I simply do combo boxes like I did for institution and pass/fail and if so how do I code them to take more than an exact match of what is input?

 

[Dhamdard]

Here is an example of code that I use to generate optional reports using conditional statements... It might give you a hint how you could proceed.

SELECT Tab_Obs_Stip_Form_Mom_SuNDE.Date, Tab_Obs_Stip_Form_Mom_SuNDE.State, Tab_Obs_Stip_Form_Mom_SuNDE.County, Tab_Obs_Stip_Form_Child_SuNDE.Name
FROM Tab_Obs_Stip_Form_Mom_SuNDE INNER JOIN Tab_Obs_Stip_Form_Child_SuNDE ON Tab_Obs_Stip_Form_Mom_SuNDE.Pkey = Tab_Obs_Stip_Form_Child_SuNDE.Pkey
GROUP BY Tab_Obs_Stip_Form_Mom_SuNDE.Date, Tab_Obs_Stip_Form_Mom_SuNDE.State, Tab_Obs_Stip_Form_Mom_SuNDE.County, Tab_Obs_Stip_Form_Child_SuNDE.Name, Tab_Obs_Stip_Form_Mom_SuNDE.Date, IIf(IsNull([Forms]![Criteria_Menu_ForReporting_PMP]![From]) Or [Forms]![Criteria_Menu_ForReporting_PMP]![From]="",[State]<>"BB",[State]="BB")
HAVING (((Tab_Obs_Stip_Form_Mom_SuNDE.Date) Between [Forms]![Criteria_Menu_ForReporting_pmp]![From] And [Forms]![Criteria_Menu_ForReporting_pmp]![To])) OR (((IIf(IsNull([Forms]![Criteria_Menu_ForReporting_PMP]![From]) Or [Forms]![Criteria_Menu_ForReporting_PMP]![From]="",[State]<>"BB",[State]="BB"))<>False)) OR (((Tab_Obs_Stip_Form_Mom_SuNDE.Date) Between [Forms]![Criteria_Menu_ForReporting_pmp]![From] And [Forms]![Criteria_Menu_ForReporting_pmp]![To])) OR (((Tab_Obs_Stip_Form_Mom_SuNDE.Date) Between [Forms]![Criteria_Menu_ForReporting_pmp]![From] And [Forms]![Criteria_Menu_ForReporting_pmp]![To])) OR (((IIf(IsNull([Forms]![Criteria_Menu_ForReporting_PMP]![From]) Or [Forms]![Criteria_Menu_ForReporting_PMP]![From]="",[State]<>"BB",[State]="BB"))<>False));