Query

[HOJU15]

I have a form that has 6 fields (job, lsd, sec, twp, rge ,mer)
the form gives data to a query.
a record is made up of the 6 fields above, some are null and some have data (numbers)

I want the query to constrain only by the fields that are filled in
i.e. if the user only types in a value for sec(5) and a value for twp(27) the query should return all records that have a value of 5 for sec and 27 for twp.

My current sql (below) will only constrain the records if you have more than one field with data, otherwise it return all records. If you have more than one field with data it will constrain by one less field.
i.e. if you have a value for lsd, sec- it will only constrain by the value for lsd
if you have value for lsd, sec, twp- it will only constrain by lsd,sec,


sql-
SELECT [located in].Job, [located in].Lsd, [located in].Section, [located in].Township, [located in].Range, [located in].Meridian, Job.well
FROM Job LEFT JOIN [located in] ON Job.job = [located in].Job
WHERE (((IIf(IsNull([forms].[SEARCH]![JOB]),IsNull([forms].[SEARCH]![JOB]),[located in].[JOB]=[forms].[SEARCH]![JOB]))<>False)
AND ((IIf(IsNull([forms].[SEARCH]![LSD]),IsNull([forms].[SEARCH]![LSD]),[located in].[LSD]=[forms].[SEARCH]![LSD]))<>False)
AND ((IIf(IsNull([forms].[SEARCH]![SECTION]),IsNull([forms].[SEARCH]![SECTION]),[located in].[SECTION]=[forms].[SEARCH]![SECTION]))<>False)
AND ((IIf(IsNull([forms].[SEARCH]![TOWNSHIP]),IsNull([forms].[SEARCH]![TOWNSHIP]),[located in].[TOWNSHIP]=[forms].[SEARCH]![TOWNSHIP]))<>False)
AND ((IIf(IsNull([forms].[SEARCH]![RANGE]),IsNull([forms].[SEARCH]![RANGE]),[located in].[RANGE]=[forms].[SEARCH]![RANGE]))<>False) AND ((IIf(IsNull([forms].[SEARCH]![MERIDIAN]),IsNull([forms].[SEARCH]![MERIDIAN]),[located in].[MERIDIAN]=[forms].[SEARCH]![MERIDIAN]))<>False));

what am I doing wrong??

 

[jfgambit]

How about if you have a command button that re-writes the Query Definition each time, that way the criteria is only filled in when there is something in the textbox or comboboxes.

See below for an example:

Private Sub cmdSearch_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef

Set dbNm = CurrentDb()

strSQL = "SELECT tblCustomers.CustID AS [CUST ID], tblCustomers.CName AS NAME, tblCustomers.CPhone AS PHONE, tblCustomers.CEMail AS EMAIL, tblCustomers.CCardExp AS [EXP DATE], tblCustomers.BCity AS CITY, tblCustomers.BState AS STATE " & _
"FROM tblCustomers"
strWhere = "WHERE"
strOrder = "ORDER BY tblCustomers.CustID;"

' set where clause conditions
If Not IsNull(Me.txtCustID) Then
strWhere = strWhere & " (tblCustomers.CustID) Like '*" & Me.txtCustID & "*' AND"
End If
If Not IsNull(Me.txtName) Then
strWhere = strWhere & " (tblCustomers.CName) Like '*" & Me.txtName & "*' AND"
End If
If Not IsNull(Me.txtPhone) Then
strWhere = strWhere & " (tblCustomers.CPhone) Like '*" & Me.txtPhone & "*' AND"
End If
If Not IsNull(Me.txtEMail) Then
strWhere = strWhere & " (tblCustomers.CEMail) Like '*" & Me.txtEMail & "*' AND"
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 5) ' remove ' and'

'This will re-write the SQL statement for Query Customer Search"

Set qryDef = dbNm.QueryDefs("qryCustomerSearch")
qryDef.SQL = strSQL & " " & strWhere & " " & strGrp

'This open the report based on the Query "Customer Search"
stDocName = "CustomerReport"
DoCmd.OpenReport stDocName, acPreview


HTH

 

[Jon K]

>> My current sql (below) will only constrain the records if you have more than one field with data, otherwise it return all records. If you have more than one field with data it will constrain by one less field. <<


The SQL is fine. I have tried it in a DB.

My guess is you directly run the query from the Queries tab while the cursor is still in the last field that you have entered the values.
If you do, the number in the last field is not yet registered. That should explain why "if you have value for lsd, sec, twp- it will only constrain by lsd, sec".

You can move the cursor away from the last field that you have entered the values before running the query. Or run the query from a command button on the form.

 

[Pat Hartman]

I reformatted your conditions and fixed the reference to the form field.

WHERE (IsNull([forms]![SEARCH]![JOB]) OR [located in].[JOB]=[forms]![SEARCH]![JOB]) AND (......) AND (....) AND (....);

The parenthese are REQUIRED because of the use of both AND and OR relational operators.

 

[HOJU15]

hmmm

I will try the second code. I am already using a command button to run the query. Can I stop access from putting in the <>false into my sql.

 

[Jon K]

Pat's Where clause is easier to read.

<> False is Access' Design View default, signifying that the expression in the Field: cell is a criterion. We can't do anything about it.