Re: Criteria linked to form combo 'Yes/No or All' expression help

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The "<> False" - that's what Access does to a function. It's supposed
to return something. The way I typed it in, the return value is
implied. Access just makes it explicit. Not to worry - it should work
the same.

I don't know why the whole criteria is not working. Sorry.

The way I'd debug it would be to start w/ one OR clause in the criteria.
Run the query & see if it works for that criteria. If it doesn't
troubleshoot until it does. Keep on doing that w/ each OR clause until
all criteria phrases are in the query & working.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSih+KIechKqOuFEgEQI20QCcCuUh4Uj3HwEBs0xSlJGVeymZDSMAnRze
4IP2SUm4bRTMFHfsvLK84ptl
=Pv4w
-----END PGP SIGNATURE-----


efandango wrote:
MG,

I pasted your where clause into the sql and ran the query from the form. If I choose -1 or 0, I simply get a blank reply and completely blank forms; and if I choose 'both', I get query too complex. I followed your suggestion of pasting the sql, and not opening query designer. However, when the query failed, I looked at it in the QBE grid; and for the criteria for the 'field': IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds] Is Null,True,[tbl_points].[Getround_Flag]=[Forms]![frm_Runs]![cbo_Point2Point_GetRounds])

I see <>False on each line in that field column.



"MGFoster" wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you misunderstood my description of how to use the "Both", -1,
0 values in the criteria.

The IIf() formula is correctly stated as:

IIf(Forms!frm_Runs!cbo_Point2Point_GetRounds = 3, TRUE,
tbl_points.Getround_Flag=Forms!frm_Runs!cbo_Point2Point_GetRounds)

It should not be set up like this:

tbl_points.GetRound_Flag = IIF(Forms!.... etc. ... )

(see the WHERE clause I included [at the bottom])

The GetRound_Flag comparison to the Form value is made inside the IIf()
function.

The IIf() function will produce these results:

Both = 3 - TRUE : this ignores the values in GetRound_Flag, which
means ALL records are selected (consistent w/ the

other criteria).
True = -1 GetRound_Flag = -1 : only -1 values.
False = 0 GetRound_Flag = 0 : only 0 values.

===

To eliminate the "too complex" error, try this criteria. I've grouped
the ORs into the first section - enclosed in parentheses, and each
separate OR clause is enclosed in parentheses. Then I factored out the
common AND expressions and put them last.

If you change back to the Design Grid view the WHERE clause will be re
formatted by Access. I don't know if this is the query optimizer or
Access trying to get the SQL to match the Design Grid. Anyway, once you
have this working don't switch to the Design Grid - just save the query
from SQL View.

When I'm working on a query that I don't want the WHERE clause to
change, I copy the WHERE clause to a Notepad file before I switch back
to Design View. Then, when I switch back to SQL View, I replace the
Access formatted WHERE clause with the saved WHERE clause. Of course I
don't change the criteria in the Design Grid view, only in the SQL View.
This means learning the syntax of Boolean logic (i.e., where to put the
parentheses).


*** BE SURE TO BACK UP BEFORE TRYING THIS ***

=== My version of the criteria ===

WHERE (
tbl_points.Run_No BETWEEN Forms!frm_Runs!cbo_Point2Point_From
And Forms!frm_Runs!cboPoint2Point_to

OR (tbl_points.Run_Point_Postcode BETWEEN
Forms!frm_Runs!cbo_Point2Point_Postcode_From
And Forms!frm_Runs!cbo_Point2Point_Postcode_To
AND tbl_points.Run_Point_Postcode<Forms!frm_Runs!txt_Run_Limit)

OR (tbl_PostCodes.Area BETWEEN Forms!frm_Runs!cbo_Point2Point_Area_From
And Forms!frm_Runs!cbo_Point2Point_Area_To
AND tbl_PostCodes.Area<Forms!frm_Runs!txt_Run_Limit)

OR (tbl_PostCodes.District BETWEEN
Forms!frm_Runs!cbo_Point2Point_District_From
And Forms!frm_Runs!cbo_Point2Point_District_To
AND tbl_PostCodes.District<Forms!frm_Runs!txt_Run_Limit)

OR (tbl_points.Point_Type BETWEEN
Forms!frm_Runs!cbo_Point2Point_PointType_From
And Forms!frm_Runs!cbo_Point2Point_PointType_To
AND tbl_points.Point_Type<Forms!frm_Runs!txt_Run_Limit)
)

AND IIf(Forms!frm_Runs!cbo_Point2Point_GetRounds IS NULL, TRUE,
tbl_points.Getround_Flag=Forms!frm_Runs!cbo_Point2Point_GetRounds)

AND tbl_Points.Run_No<Forms!frm_Runs!txt_Run_Limit

===================== end SQL ======================

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSiXuWYechKqOuFEgEQLptACgnpgaXavAxQIU1Gvchl7+SLcWkokAoIq7
czJNGl4OF5MevDGs17NXox8d
=AByO
-----END PGP SIGNATURE-----

efandango wrote:
John,

I spoke too soon. When I reopened the database, I got the dreaded 'Expression too complex error....

your consistent predictions that it may be too complex have come to be true...

Is there another way of cracking this, or am I to live with the limitations apparent?



"John Spencer MVP" wrote:

My fault, that should be OR between the two not AND

(tbl_points.Getround_Flag=
IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,True, [Forms]![frm_Runs]![cbo_Point2Point_GetRounds]
OR
tbl_points.Getround_Flag=
IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,False, [Forms]![frm_Runs]![cbo_Point2Point_GetRounds])

The logic being that if the combobox returns 3 you will search for the value being True or False. Otherwise you will search for the value being True or True; or you will search for the value being False or False.

Again this query could get too complex to run.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

efandango wrote:
John,

when I try your solution, the query/form fails to return anything when I select 'both' from the combobox. MG Fosters also fails on the 'both' criteria, so something must be wrong perhaps somewhere else, or have I got the syntax wrong somehwere?

here is the line I am pasting into the SQL designer on field: [Getround_Flag]

here is the full SQL:


SELECT TOP 18 tbl_points.Run_No, tbl_points.Run_Point_Postcode, tbl_PostCodes.Area, tbl_PostCodes.District, tbl_points.Point_Type, tbl_points.Getround_Flag, tbl_points.Point_ID, tbl_points.Run_point_Venue, tbl_points.Run_point_Address
FROM tbl_points LEFT JOIN tbl_PostCodes ON tbl_points.Run_Point_Postcode = tbl_PostCodes.Postcode
WHERE (((tbl_points.Run_No) Between [Forms]![frm_Runs].[cbo_Point2Point_From] And [Forms]![frm_Runs].[cbo_Point2Point_To] And (tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,True,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds]) And (tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,False,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds]))) OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Run_Point_Postcode) Between [Forms]![frm_Runs].[cbo_Point2Point_Postcode_From] And [Forms]![frm_Runs].[cbo_Point2Point_Postcode_To] And (tbl_points.Run_Point_Postcode)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,True,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds]) And (tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,False,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds]))) OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_PostCodes.Area) Between [Forms]![frm_Runs].[cbo_Point2Point_Area_From] And [Forms]![frm_Runs].[cbo_Point2Point_Area_To] And (tbl_PostCodes.Area)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,True,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds]) And (tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,False,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds]))) OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_PostCodes.District) Between [Forms]![frm_Runs].[cbo_Point2Point_District_From] And [Forms]![frm_Runs].[cbo_Point2Point_District_To] And (tbl_PostCodes.District)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,True,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds]) And (tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,False,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds]))) OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Point_Type) Between [Forms]![frm_Runs].[cbo_Point2Point_PointType_From] And [Forms]![frm_Runs].[cbo_Point2Point_PointType_To] And (tbl_points.Point_Type)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,True,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds]) And (tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,False,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds])))
ORDER BY Rnd([Point_ID]) DESC;





"John Spencer" wrote:

If you have one combobox with two columns and its properties set as
--Row source type: Value List
--Column widths: 0
--Row Source: 3;"Both",-1;"True",0;"False"
--Column Count: 2
--Bound Column: 1

You could change the criteria in the query to use this slightly more complex statement.
tbl_points.Getround_Flag=IIF([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,True,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds])
and
tbl_points.Getround_Flag=IIF([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]=3,False,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds])

MG Foster's proposed solution should work also; but you might run into a query too complex error with his solution since Access often significantly restructures the query's where clause using that solution.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

efandango wrote:
Yes, you're correct, I didn't need two combo boxes. I have been using 2 combos for the rest of the form where they were required and just had tunnel vision regarding this particular combo. No I have a combo that has: Both;-1;0 which works perfectly, except I would prefer somthing more plain english, like both; Yes; No

But if I use Yes, No, the query doesn't understand it. It seems to only like the -1;0 criteria. Is their a way of having Yes and No either in the query or perhaps a mask in the combo box, which I think is what Karl was trying to explain to me in his reply?

my full SQL is:

SELECT TOP 18 tbl_points.Run_No, tbl_points.Run_Point_Postcode, tbl_PostCodes.Area, tbl_PostCodes.District, tbl_points.Point_Type, tbl_points.Getround_Flag, tbl_points.Point_ID, tbl_points.Run_point_Venue, tbl_points.Run_point_Address
FROM tbl_points LEFT JOIN tbl_PostCodes ON tbl_points.Run_Point_Postcode = tbl_PostCodes.Postcode
WHERE (((tbl_points.Run_No) Between [Forms]![frm_Runs].[cbo_Point2Point_From] And [Forms]![frm_Runs].[cbo_Point2Point_To] And (tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]="Both",True,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds]))) OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Run_Point_Postcode) Between [Forms]![frm_Runs].[cbo_Point2Point_Postcode_From] And [Forms]![frm_Runs].[cbo_Point2Point_Postcode_To] And (tbl_points.Run_Point_Postcode)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]="Both",True,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds]))) OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_PostCodes.Area) Between [Forms]![frm_Runs].[cbo_Point2Point_Area_From] And [Forms]![frm_Runs].[cbo_Point2Point_Area_To] And (tbl_PostCodes.Area)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]="Both",True,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds]))) OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_PostCodes.District) Between [Forms]![frm_Runs].[cbo_Point2Point_District_From] And [Forms]![frm_Runs].[cbo_Point2Point_District_To] And (tbl_PostCodes.District)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]="Both",True,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds]))) OR (((tbl_points.Run_No)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Point_Type) Between [Forms]![frm_Runs].[cbo_Point2Point_PointType_From] And [Forms]![frm_Runs].[cbo_Point2Point_PointType_To] And (tbl_points.Point_Type)<[Forms]![frm_Runs].[txt_Run_Limit]) AND ((tbl_points.Getround_Flag)=IIf([Forms]![frm_Runs]![cbo_Point2Point_GetRounds]="Both",True,[Forms]![frm_Runs]![cbo_Point2Point_GetRounds])))
ORDER BY Rnd([Point_ID]) DESC;




"MGFoster" wrote:

efandango wrote:
I have a query field linked to a form combo box, like this:

Between [Forms]![frm_Runs].[cbo_Point2Point_Getrounds_From] And [Forms]![frm_Runs].[cbo_Point2Point_Getrounds_To] And <[Forms]![frm_Runs].[txt_Run_Limit]

In the form combo box I have a -1 or 0 choice. I want a third choice which is All (or in plain english 'Both'. In other words if the user chooses All/Both in the combobox, I want the query criteria to show all records.

The format in the combobox can be any format that will get the job done, at the moment it is plain text. I have tried a Yes/No format but it will not allow a third state. I want to avoid a checkbox because it is not obvious to the user that a third state exists with that control.

How can I make the query criteria return All values when the user selects All/Both instead of either just the -1 or 0s?
-----BEGIN PGP SIGNED MESSAGE-----
.

Relevant Pages

  • Re: SQL Syntax Error
    ... match all the criteria you input. ... SQL view and never open it in the query grid view. ... The syntax for the "where" statement is incorrect becuase of this. ...
    (microsoft.public.access.queries)
  • Re: export form filtered data to excel
    ... ' There's no ORDER BY in the SQL. ... ' Remove the semi-colon from the end, then append the WHERE clause ... Or, better yet, look at the actual SQL for your query and see what's ... Dim qdfTemp As DAO.QueryDef ...
    (microsoft.public.access.formscoding)
  • Re: Query to Count Duplicate Values in a Given Date Range
    ... Show" to note whether the client showed up at the pantry. ... Now, if I try to run the query with that field, it returns no results. ... Here's my final SQL code if you see anything else that may need adjusting. ... use a WHERE clause rather than HAVING for the dates, ...
    (microsoft.public.access.queries)
  • Re: Using combo box to search form
    ... "Jeff Boyce" wrote: ... Since you "started adding" additional criteria, ... about SQL syntax to know how to form a grammatically-correct SQL statement. ... One way to do that is to see what Access generates when you build a query. ...
    (microsoft.public.access.formscoding)
  • Re: Adjusting SQL in Query
    ... from the RESPEL table which satisfy the criteria in the PRICELIST Table. ... RESPEL INNER JOIN PRICELIST ON RESPEL.PRICELIST = ... Subject: Adjusting SQL in Query ...
    (microsoft.public.access.queries)