f



Filter on check box

I have a continuous form that contains a number of check boxes. The Control Source for a typical check box comes from a calculated field in a query, such as:

hasArrived: Not IsNull([Vehicles].[ArrivalDate])

When I attempt to filter the column with a right click I am offered:

Sort smallest to largest
Sort largest to smallest

Equals -1
Does not equal -1
Less than or equal to -1
Greater than or equal to -1


On another Form check boxes have as their Control Sources, Yes/no fields in a table.  Here, when I press right click I am offered:

Sort Selected to Cleared
Sort Cleared to Selected

Is -1
Is not -1
Is Selected
Is Not Selected.


This latter set is more meaningful to the end user who does not want to know about -1.  He/she will think in terms of Selected or Cleared.

Is there any way in which I can convince the first setup that I am presenting boolean values and not just any integer?   A check box is only displaying true or false after all.  I would like the second list of options to be shown in the first setup.

Jim
0
internet
12/8/2016 2:28:19 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

7 Replies
496 Views

Similar Articles

[PageSpeed] 2

On Thursday, December 8, 2016 at 9:28:26 AM UTC-5, internet...@foobox.com wrote:
> I have a continuous form that contains a number of check boxes. The Control Source for a typical check box comes from a calculated field in a query, such as:
> 
> hasArrived: Not IsNull([Vehicles].[ArrivalDate])
> 
> When I attempt to filter the column with a right click I am offered:
> 
> Sort smallest to largest
> Sort largest to smallest
> 
> Equals -1
> Does not equal -1
> Less than or equal to -1
> Greater than or equal to -1
> 
> 
> On another Form check boxes have as their Control Sources, Yes/no fields in a table.  Here, when I press right click I am offered:
> 
> Sort Selected to Cleared
> Sort Cleared to Selected
> 
> Is -1
> Is not -1
> Is Selected
> Is Not Selected.
> 
> 
> This latter set is more meaningful to the end user who does not want to know about -1.  He/she will think in terms of Selected or Cleared.
> 
> Is there any way in which I can convince the first setup that I am presenting boolean values and not just any integer?   A check box is only displaying true or false after all.  I would like the second list of options to be shown in the first setup.
> 
> Jim

Maybe you can modify the value in the query?  Instead of
  hasArrived: Not IsNull([Vehicles].[ArrivalDate]) 
use
  hasArrived: IIF(Not IsNull([Vehicles].[ArrivalDate]),"Yes","No")

I don't know if that would work in your case...just throwing mud at the wall hoping something will stick.
0
Patrick
12/8/2016 3:03:58 PM
On Thursday, December 8, 2016 at 10:04:07 AM UTC-5, Patrick Finucane wrote:
> On Thursday, December 8, 2016 at 9:28:26 AM UTC-5, internet...@foobox.com wrote:
> > I have a continuous form that contains a number of check boxes. The Control Source for a typical check box comes from a calculated field in a query, such as:
> > 
> > hasArrived: Not IsNull([Vehicles].[ArrivalDate])
> > 
> > When I attempt to filter the column with a right click I am offered:
> > 
> > Sort smallest to largest
> > Sort largest to smallest
> > 
> > Equals -1
> > Does not equal -1
> > Less than or equal to -1
> > Greater than or equal to -1
> > 
> > 
> > On another Form check boxes have as their Control Sources, Yes/no fields in a table.  Here, when I press right click I am offered:
> > 
> > Sort Selected to Cleared
> > Sort Cleared to Selected
> > 
> > Is -1
> > Is not -1
> > Is Selected
> > Is Not Selected.
> > 
> > 
> > This latter set is more meaningful to the end user who does not want to know about -1.  He/she will think in terms of Selected or Cleared.
> > 
> > Is there any way in which I can convince the first setup that I am presenting boolean values and not just any integer?   A check box is only displaying true or false after all.  I would like the second list of options to be shown in the first setup.
> > 
> > Jim
> 
> Maybe you can modify the value in the query?  Instead of
>   hasArrived: Not IsNull([Vehicles].[ArrivalDate]) 
> use
>   hasArrived: IIF(Not IsNull([Vehicles].[ArrivalDate]),"Yes","No")
> 
> I don't know if that would work in your case...just throwing mud at the wall hoping something will stick.

If Yes/No doesn't work then try True/False
0
Patrick
12/8/2016 3:13:47 PM
Thanks

true and false make no change but 'yes' and 'no' produce:

Sort A to Z
Sort Z to A

Equals 'yes'
Does not equal 'yes'
Contains 'yes'
Does not contain 'yes'

Now it thinks that it is a text value and not an integer value.  

However using 'true' and 'false' instead of true and false yields
Equals 'true'
Does not equal 'true'
Contains 'true'
Does not contain 'true'

It still thinks that is text - I guess any wording would do!

Not perfect but better.  Wish I could make it Boolean

Jim


On Thursday, 8 December 2016 15:13:52 UTC, Patrick Finucane  wrote:
> On Thursday, December 8, 2016 at 10:04:07 AM UTC-5, Patrick Finucane wrote:
> > On Thursday, December 8, 2016 at 9:28:26 AM UTC-5, internet...@foobox.com wrote:
> > > I have a continuous form that contains a number of check boxes. The Control Source for a typical check box comes from a calculated field in a query, such as:
> > > 
> > > hasArrived: Not IsNull([Vehicles].[ArrivalDate])
> > > 
> > > When I attempt to filter the column with a right click I am offered:
> > > 
> > > Sort smallest to largest
> > > Sort largest to smallest
> > > 
> > > Equals -1
> > > Does not equal -1
> > > Less than or equal to -1
> > > Greater than or equal to -1
> > > 
> > > 
> > > On another Form check boxes have as their Control Sources, Yes/no fields in a table.  Here, when I press right click I am offered:
> > > 
> > > Sort Selected to Cleared
> > > Sort Cleared to Selected
> > > 
> > > Is -1
> > > Is not -1
> > > Is Selected
> > > Is Not Selected.
> > > 
> > > 
> > > This latter set is more meaningful to the end user who does not want to know about -1.  He/she will think in terms of Selected or Cleared.
> > > 
> > > Is there any way in which I can convince the first setup that I am presenting boolean values and not just any integer?   A check box is only displaying true or false after all.  I would like the second list of options to be shown in the first setup.
> > > 
> > > Jim
> > 
> > Maybe you can modify the value in the query?  Instead of
> >   hasArrived: Not IsNull([Vehicles].[ArrivalDate]) 
> > use
> >   hasArrived: IIF(Not IsNull([Vehicles].[ArrivalDate]),"Yes","No")
> > 
> > I don't know if that would work in your case...just throwing mud at the wall hoping something will stick.
> 
> If Yes/No doesn't work then try True/False
0
internet
12/8/2016 5:09:14 PM
On Thursday, December 8, 2016 at 12:09:18 PM UTC-5, internet...@foobox.com wrote:
> Thanks
> 
> true and false make no change but 'yes' and 'no' produce:
> 
> Sort A to Z
> Sort Z to A
> 
> Equals 'yes'
> Does not equal 'yes'
> Contains 'yes'
> Does not contain 'yes'
> 
> Now it thinks that it is a text value and not an integer value.  
> 
> However using 'true' and 'false' instead of true and false yields
> Equals 'true'
> Does not equal 'true'
> Contains 'true'
> Does not contain 'true'
> 
> It still thinks that is text - I guess any wording would do!
> 
> Not perfect but better.  Wish I could make it Boolean
> 
> Jim
> 
Maybe try 
  CBool( expression ) 
to ensure conversion to boolean.
0
Patrick
12/8/2016 6:34:02 PM
That has no effect.

It would appear that expressions from a query are regarded as either numeric or text.  Can't see how to force it be boolean.

Jim


On Thursday, 8 December 2016 18:34:08 UTC, Patrick Finucane  wrote:
> On Thursday, December 8, 2016 at 12:09:18 PM UTC-5, internet...@foobox.com wrote:
> > Thanks
> > 
> > true and false make no change but 'yes' and 'no' produce:
> > 
> > Sort A to Z
> > Sort Z to A
> > 
> > Equals 'yes'
> > Does not equal 'yes'
> > Contains 'yes'
> > Does not contain 'yes'
> > 
> > Now it thinks that it is a text value and not an integer value.  
> > 
> > However using 'true' and 'false' instead of true and false yields
> > Equals 'true'
> > Does not equal 'true'
> > Contains 'true'
> > Does not contain 'true'
> > 
> > It still thinks that is text - I guess any wording would do!
> > 
> > Not perfect but better.  Wish I could make it Boolean
> > 
> > Jim
> > 
> Maybe try 
>   CBool( expression ) 
> to ensure conversion to boolean.
0
internet
12/9/2016 4:22:38 PM
On Thursday, 8 December 2016 14:28:26 UTC, internet...@foobox.com  wrote:
> I have a continuous form that contains a number of check boxes. The Control Source for a typical check box comes from a calculated field in a query, such as:
> 
> hasArrived: Not IsNull([Vehicles].[ArrivalDate])
> 
> When I attempt to filter the column with a right click I am offered:
> 
> Sort smallest to largest
> Sort largest to smallest
> 
> Equals -1
> Does not equal -1
> Less than or equal to -1
> Greater than or equal to -1
> 
> 
> On another Form check boxes have as their Control Sources, Yes/no fields in a table.  Here, when I press right click I am offered:
> 
> Sort Selected to Cleared
> Sort Cleared to Selected
> 
> Is -1
> Is not -1
> Is Selected
> Is Not Selected.
> 
> 
> This latter set is more meaningful to the end user who does not want to know about -1.  He/she will think in terms of Selected or Cleared.
> 
> Is there any way in which I can convince the first setup that I am presenting boolean values and not just any integer?   A check box is only displaying true or false after all.  I would like the second list of options to be shown in the first setup.
> 
> Jim

I don't know if it would work, but have you considered creating a shortcut menu specifically for this control?

Phil
0
pstanton54
12/9/2016 6:25:59 PM
On Friday, December 9, 2016 at 11:22:41 AM UTC-5, internet...@foobox.com wrote:
> That has no effect.
> 
> It would appear that expressions from a query are regarded as either numeric or text.  Can't see how to force it be boolean.
> 
> Jim
> 
> 
> On Thursday, 8 December 2016 18:34:08 UTC, Patrick Finucane  wrote:
> > On Thursday, December 8, 2016 at 12:09:18 PM UTC-5, internet...@foobox.com wrote:
> > > Thanks
> > > 
> > > true and false make no change but 'yes' and 'no' produce:
> > > 
> > > Sort A to Z
> > > Sort Z to A
> > > 
> > > Equals 'yes'
> > > Does not equal 'yes'
> > > Contains 'yes'
> > > Does not contain 'yes'
> > > 
> > > Now it thinks that it is a text value and not an integer value.  
> > > 
> > > However using 'true' and 'false' instead of true and false yields
> > > Equals 'true'
> > > Does not equal 'true'
> > > Contains 'true'
> > > Does not contain 'true'
> > > 
> > > It still thinks that is text - I guess any wording would do!
> > > 
> > > Not perfect but better.  Wish I could make it Boolean
> > > 
> > > Jim
> > > 
> > Maybe try 
> >   CBool( expression ) 
> > to ensure conversion to boolean.

OK.  This may be a convoluted method.  I opened up a select query.  One column was
  Expr1:IIF(Year(DateAdded) = 2016,True, False)

Ok, I changed the select query to a Make Table query.  I called the table Junk.  I then ran the query.  It created table Junk.

I opened up table Junk in design mode.  Expr1 was a numeric field.  I changed it to Yes/No.

I then changed my query from MakeTable to Append.  I append my records into table Junk.  Now my values for Expr1 are Yes/No in the selection list when I press the down arrow next to name.

So...If your list is not large.  When you run the query, first delete all records in Junk table.  Run the append query.  Then link your form's query to the Junk table.

Hope you follow this process I outlined.
0
Patrick
12/9/2016 6:44:05 PM
Reply: