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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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: 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: 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: 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)
  • Re: Criteria linked to form combo Yes/No or All expression help
    ... But if I use Yes, No, the query doesn't understand it. ... 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, ...
    (microsoft.public.access.queries)
  • RE: updated numbers in report
    ... criteria for a number of queries. ... But i have one more question, its about a textbox. ... textbox will be located alongside with the combobox where the belonging ... will not make the input to the query. ...
    (microsoft.public.access.dataaccess.pages)