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;
:
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-----
