Query Criteria

[Allen Browne]

You cannot have the query read an entire expression including the operators
like that.

Instead, build the filter dynamically. You can then use it in the Filter of
a form, the WhereCondition of OpenReport, or the entire SQL statement (e.g.
for the RecordSource of a form, or the RowSource of a combo, or the SQL
property of a QueryDef.)

Here's a simple example:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

Here's a more comprehensive one:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[DS]

I'm Trying to set the Criteria in a Query from TextBoxes on a form but I
can't get the syntax.
Any help appreciated.
Thanks
DS

Me.TxtDateCrit = " & >= & " " & Forms!frmFX!TxtDateStart & " AND " & <=
&" "& Forms!frmFX!TxtDateEnd & "

Should end up as this in the Criteria field of the Query...

= Forms!frmFX!TxtDateStart AND <= Forms!frmFX!TxtDateEnd

But its not

 

[DS]

You cannot have the query read an entire expression including the
operators like that.

Instead, build the filter dynamically. You can then use it in the Filter
of a form, the WhereCondition of OpenReport, or the entire SQL statement
(e.g. for the RecordSource of a form, or the RowSource of a combo, or
the SQL property of a QueryDef.)

Here's a simple example:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

Here's a more comprehensive one:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Thanks Allen I looked at the second link and decided on an unbound form
with unbound textboxes, I will use these textboxes to set my criteria
for an unbound listbox on another form...here is what I have but it
isn't quite returning anything but an empty listbox. Any suggestions
welcomed!
Thanks
DS

With Forms!frmFXReproduce!ListStats
.RowSource = "SELECT Format([ChkDate],""m/dd/yyyy"") AS DT,
tblChecks.ChkTime, tblChecks.CheckID, " & _
"tblChecks.ChkAlias, [EmpFirstName] & "" "" & [EmpLastName] AS SRV,
" & _
"tblChecks.ChkPaid, tblChecks.ChkCancelled, tblChecks.ChkTabID,
tblChecks.ChkAlias & "" / "" & tblChecks.ChkGuests AS INFO, " & _
"tblChecks.ChkServer, tblChecks.ChkKillTax,
tblChecks.ChkDividedCheck, tblChecks.ChkTotal, tblChecks.ChkBizDay " & _
"FROM tblChecks INNER JOIN tblEmployees ON tblChecks.ChkServer =
tblEmployees.EmployeeID " & _
"WHERE (((tblChecks.ChkTime) >=
[Forms]![frmFXReproduceSearch]![TxtTimeStart]Is Null) " & _
"OR (tblChecks.ChkTime) >=
[Forms]!frmFXReproduceSearch]![TxtTimeStart]) " & _
"And (tblChecks.ChkTime) <=
[Forms]![frmFXReproduceSearch]![TxtTimeEnd]Is Null) " & _
"OR (tblChecks.ChkTime) <=
[Forms]![frmFXReproduceSearch]![TxtTimeEnd]) " & _
"And ((tblChecks.CheckID) >=
[Forms]![frmFXReproduceSearch]![TxtCheckStart]Is Null) " & _
"OR ((tblChecks.CheckID) >=
[Forms]![frmFXReproduceSearch]![TxtCheckStart] " & _
"And (tblChecks.CheckID) <=
[Forms]![frmFXReproduceSearch]![TxtCheckEnd]IS Null) " & _
"OR (tblChecks.CheckID) <=
[Forms]![frmFXReproduceSearch]![TxtCheckEnd]) " & _
"And ((tblChecks.ChkPaid) = -1) And ((tblChecks.ChkCancelled) = 0)
And ((tblChecks.ChkTabID) = [Forms]![frmFXReproduceSearch]![TxtTabID]) " & _
"And ((tblChecks.ChkServer) =
[Forms]![frmFXReproduceSearch]![TxtServer]Is Null) " & _
"OR ((tblChecks.ChkServer) =
[Forms]![frmFXReproduceSearch]![TxtServer]) " & _
"And ((tblChecks.ChkTotal) >=
[Forms]![frmFXReproduceSearch]![TxtAmountStart]Is Null) " & _
"OR ((tblChecks.ChkTotal) >=
[Forms]![frmFXReproduceSearch]![TxtAmountStart] " & _
"And (tblChecks.ChkTotal) <=
[Forms]![frmFXReproduceSearch]![TxtAmountEnd]Is Null) " & _
"OR (tblChecks.ChkTotal) <=
[Forms]![frmFXReproduceSearch]![TxtAmountEnd]) " & _
"And ((tblChecks.ChkDate) >=
[Forms]![frmFXReproduceSearch]![TxtDateStart]Is Null) " & _
"OR ((tblChecks.ChkDate) >=
[Forms]![frmFXReproduceSearch]![TxtDateStart] " & _
"And (tblChecks.ChkDate) <=
[Forms]![frmFXReproduceSearch]![TxtDateEnd]Is Null)) " & _
"OR (tblChecks.ChkDate) <=
[Forms]![frmFXReproduceSearch]![TxtDateEnd])) " & _
"ORDER BY tblChecks.ChkTime DESC;"
.ColumnCount = 14
.ColumnWidths = "0.8 in;0.75 in;1 in;1.2 in;1.4 in;0 in;0 in;0 in;0
in;0 in;0 in;0 in;0.8 in;0 in"
.Requery
End With

 

[DS]

I'm Trying to set the Criteria in a Query from TextBoxes on a form but I
can't get the syntax.
Any help appreciated.
Thanks
DS

Me.TxtDateCrit = " & >= & " " & Forms!frmFX!TxtDateStart & " AND " & <=
&" "& Forms!frmFX!TxtDateEnd & "

Should end up as this in the Criteria field of the Query...

But its not

This is simpler but the Listbox returns a blank......

Perhaps its a syntax error?
DS

With Forms!frmFXReproduce!ListStats
..RowSource = "SELECT tblChecks.ChkServer, tblChecks.ChkTabID,
tblChecks.ChkDate, " & _
"FROM tblChecks " & _
"WHERE (([Forms]![frmFXReproduceSearch]![TxtServerID] Is Null)" & _
"OR (tblChecks.ChkServer =
[Forms]![frmFXReproduceSearch]![TxtServerID]))" & _
"AND (([Forms]![frmFXReproduceSearch]![TxtTabID] Is Null) " & _
"OR (tblChecks.ChkTabID = [Forms]![frmFXReproduceSearch]![TxtTabID])) " & _
"AND (([Forms]![frmFXReproduceSearch]![TxtDateStart] Is Null) " & _
"OR (tblChecks.ChkDate >=
[Forms]![frmFXReproduceSearch]![TxtDateStart])) " & _
"AND (([Forms]![frmFXReproduceSearch]![TxtDateEnd] Is Null) " & _
"OR (tblChecks.ChkDate <= [Forms]![frmFXReproduceSearch]![TxtDateEnd])))
" & _
"ORDER BY tblChecks.ChkDate DESC;"
..ColumnCount = 3
..ColumnWidths = ".5 in;.5 in;.5 in"
..Requery
End With

 

[Allen Browne]

There's several problems with the WHERE clause as it is:

a) The first expression is not testing whether the parameter is null: you
still have the field name in there. It needs to be along the lines of:
WHERE (([Forms]![frmFXReproduceSearch]![TxtTimeStart] Is Null) " & _
"OR (tblChecks.ChkTime >=
[Forms]!frmFXReproduceSearch]![TxtTimeStart])) " & _

b) You have mixed AND and OR operators in the expression, so you will need
to bracket them carefully because:
a AND (b OR c)
is not the same thing as:
(a AND b) OR c
The example above uses one bracket between the OR parts, and then another
bracket around the lot before the AND.

c) You did not declare the parameters.

Since you are building the entire SQL statement, IMHO it would be easier
(and more efficient) to include only the parts where the text boxes are not
Null, instead of including a test for Null for each one in the SQL
statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

You cannot have the query read an entire expression including the
operators like that.

Instead, build the filter dynamically. You can then use it in the Filter
of a form, the WhereCondition of OpenReport, or the entire SQL statement
(e.g. for the RecordSource of a form, or the RowSource of a combo, or the
SQL property of a QueryDef.)

Here's a simple example:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

Here's a more comprehensive one:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Thanks Allen I looked at the second link and decided on an unbound form
with unbound textboxes, I will use these textboxes to set my criteria for
an unbound listbox on another form...here is what I have but it isn't
quite returning anything but an empty listbox. Any suggestions welcomed!
Thanks
DS

With Forms!frmFXReproduce!ListStats
.RowSource = "SELECT Format([ChkDate],""m/dd/yyyy"") AS DT,
tblChecks.ChkTime, tblChecks.CheckID, " & _
"tblChecks.ChkAlias, [EmpFirstName] & "" "" & [EmpLastName] AS SRV, "
& _
"tblChecks.ChkPaid, tblChecks.ChkCancelled, tblChecks.ChkTabID,
tblChecks.ChkAlias & "" / "" & tblChecks.ChkGuests AS INFO, " & _
"tblChecks.ChkServer, tblChecks.ChkKillTax, tblChecks.ChkDividedCheck,
tblChecks.ChkTotal, tblChecks.ChkBizDay " & _
"FROM tblChecks INNER JOIN tblEmployees ON tblChecks.ChkServer =
tblEmployees.EmployeeID " & _
"WHERE (((tblChecks.ChkTime) >=
[Forms]![frmFXReproduceSearch]![TxtTimeStart]Is Null) " & _
"OR (tblChecks.ChkTime) >=
[Forms]!frmFXReproduceSearch]![TxtTimeStart]) " & _
"And (tblChecks.ChkTime) <=
[Forms]![frmFXReproduceSearch]![TxtTimeEnd]Is Null) " & _
"OR (tblChecks.ChkTime) <=
[Forms]![frmFXReproduceSearch]![TxtTimeEnd]) " & _
"And ((tblChecks.CheckID) >=
[Forms]![frmFXReproduceSearch]![TxtCheckStart]Is Null) " & _
"OR ((tblChecks.CheckID) >=
[Forms]![frmFXReproduceSearch]![TxtCheckStart] " & _
"And (tblChecks.CheckID) <=
[Forms]![frmFXReproduceSearch]![TxtCheckEnd]IS Null) " & _
"OR (tblChecks.CheckID) <=
[Forms]![frmFXReproduceSearch]![TxtCheckEnd]) " & _
"And ((tblChecks.ChkPaid) = -1) And ((tblChecks.ChkCancelled) = 0) And
((tblChecks.ChkTabID) = [Forms]![frmFXReproduceSearch]![TxtTabID]) " & _
"And ((tblChecks.ChkServer) =
[Forms]![frmFXReproduceSearch]![TxtServer]Is Null) " & _
"OR ((tblChecks.ChkServer) =
[Forms]![frmFXReproduceSearch]![TxtServer]) " & _
"And ((tblChecks.ChkTotal) >=
[Forms]![frmFXReproduceSearch]![TxtAmountStart]Is Null) " & _
"OR ((tblChecks.ChkTotal) >=
[Forms]![frmFXReproduceSearch]![TxtAmountStart] " & _
"And (tblChecks.ChkTotal) <=
[Forms]![frmFXReproduceSearch]![TxtAmountEnd]Is Null) " & _
"OR (tblChecks.ChkTotal) <=
[Forms]![frmFXReproduceSearch]![TxtAmountEnd]) " & _
"And ((tblChecks.ChkDate) >=
[Forms]![frmFXReproduceSearch]![TxtDateStart]Is Null) " & _
"OR ((tblChecks.ChkDate) >=
[Forms]![frmFXReproduceSearch]![TxtDateStart] " & _
"And (tblChecks.ChkDate) <=
[Forms]![frmFXReproduceSearch]![TxtDateEnd]Is Null)) " & _
"OR (tblChecks.ChkDate) <=
[Forms]![frmFXReproduceSearch]![TxtDateEnd])) " & _
"ORDER BY tblChecks.ChkTime DESC;"
.ColumnCount = 14
.ColumnWidths = "0.8 in;0.75 in;1 in;1.2 in;1.4 in;0 in;0 in;0 in;0
in;0 in;0 in;0 in;0.8 in;0 in"
.Requery
End With

 

[DS]

There's several problems with the WHERE clause as it is:

a) The first expression is not testing whether the parameter is null:
you still have the field name in there. It needs to be along the lines of:
WHERE (([Forms]![frmFXReproduceSearch]![TxtTimeStart] Is Null) " & _
"OR (tblChecks.ChkTime >=
[Forms]!frmFXReproduceSearch]![TxtTimeStart])) " & _

b) You have mixed AND and OR operators in the expression, so you will
need to bracket them carefully because:
a AND (b OR c)
is not the same thing as:
(a AND b) OR c
The example above uses one bracket between the OR parts, and then
another bracket around the lot before the AND.

c) You did not declare the parameters.

Since you are building the entire SQL statement, IMHO it would be easier
(and more efficient) to include only the parts where the text boxes are
not Null, instead of including a test for Null for each one in the SQL
statement.

Thanks Allen I'll work on it further and get back.
DS