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

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-----
Hash: SHA1

I don't know why you have 2 ComboBoxes for this requirement - you only
need one.

I'll assume you're using a ValueList for the ComboBoxes' RowSource
properties.... Change them to this: Both, -1, 0

For your 2 ComboBoxes the criteria would be 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
OR (Forms!frm_Runs!cbo_Point2Point_GetRounds_From = "Both"
OR Forms!frm_Runs!cbo_Point2Point_GetRounds_To = "Both")

If you had just one ComboBox your criteria would be like this:

IIf(Forms!frm_Runs!cbo_Point2Point_GetRounds="Both", True,
Forms!frm_Runs!cbo_Point2Point_GetRounds)

The "True" will cause the query to retury all records (if that is the
only criteria); otherwise the criteria would use -1 or 0, depending on
which one the user selected.

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/AwUBSiBolIechKqOuFEgEQIEYgCgmtS/Sl7ClFYinlhTOp6tv4stdIUAnRZz
8n8j/Tjy1oQpnYKJ4joqVAWW
=0oDb
-----END PGP SIGNATURE-----


.

Relevant Pages

  • Re: Using If expressions with an Or expression, and a Count questi
    ... The addition of the commas inside the was to ensure exact matches to Bill and Steve and preclude a record with STE being returned. ... "John Spencer" wrote: ... You did not give us any table or field names so what I wrote was a generic example of a query. ... To do this in design view, you would put the IIF statement into a field "box" and the Like into a criteria box under the IIF. ...
    (microsoft.public.access.queries)
  • Re: Criteria linked to form combo Yes/No or All expression help
    ... "John Spencer MVP" wrote: ... The logic being that if the combobox returns 3 you will search for the value ... Again this query could get too complex to run. ... You could change the criteria in the query to use this slightly more ...
    (microsoft.public.access.queries)
  • Re: Narrowing query results
    ... It should return ALL items that match criteria unless you enter criteria on the outer query. ... controlname is the Name property of the control ... John Spencer wrote: ...
    (microsoft.public.access.queries)
  • Re: searching for similar records
    ... "John Spencer" wrote: ... anything at all with criteria or joins on partnumber. ... should only the partnumber and customer be ... Open a new query ...
    (microsoft.public.access.queries)
  • RE: updated numbers in report
    ... it is looking as you have described, when using only the criteria from the ... for the input in the query it is not. ... i was thinking this, that the combobox ... have a list from the table, and the textbox have not. ...
    (microsoft.public.access.dataaccess.pages)
Loading