Query Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DS - 03 Jul 2007 02:48 GMT

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

Reply to this Message

Allen Browne - 03 Jul 2007 03:10 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I'm Trying to set the Criteria in a Query from TextBoxes on a form but I
> can't get the syntax.
[quoted text clipped - 9 lines]
>
> But its not

Reply to this Message

DS - 03 Jul 2007 18:53 GMT

> You cannot have the query read an entire expression including the
> operators like that.
[quoted text clipped - 13 lines]
> 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

Reply to this Message

Allen Browne - 04 Jul 2007 10:55 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>> You cannot have the query read an entire expression including the
>> operators like that.
[quoted text clipped - 76 lines]
> .Requery
> End With

Reply to this Message

DS - 04 Jul 2007 18:36 GMT

> There's several problems with the WHERE clause as it is:
>
[quoted text clipped - 18 lines]
> 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

Reply to this Message

DS - 04 Jul 2007 01:00 GMT

> I'm Trying to set the Criteria in a Query from TextBoxes on a form but I
> can't get the syntax.
[quoted text clipped - 9 lines]
>
> 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

Reply to this Message