filtering on form from unbound calculated control

[Ted]

hi, my query has a field Time_on_List: = DateDiff("d", Me.Date_on_List, Date())

my form has an unbound field Time_on_List on it which uses Time_on_List from
the query record source as its control source.

my question comes from what i observe results when i click on "Filter by
Form" and place my cursor in the "Time_on_List" control. it, a text box,
displays a downward pointing arrow which when depressed reveals a litany of
calendar dates, e.g. 9/27/04, etc. etc. etc.

i presume that since this an unbound control that the "Time_on_List"
quantity displayed when the form's being viewed is a kind of virtual now you
see it now you don't sort of thing but what am i to make of these dates. and
more to my central point, is there any way to filter by the "Time_on_List
quantity?

ted

 

[Allen Browne]

Your text box is not unbound: it is bound to a calculated query field. That
means you can filter it.

If I understand correctly, your Time_on_List field returns a number of days.
Access frequently gets the data type of calculated controls wrong. If it is
understanding it as a date instead of a number, try:
1. Set the Format property of the text box to General Number.

2. If that does not solve the problem, typecast the field in the query,
i.e.:
Time_on_List: CLng(Nz(DateDiff("d", Me.Date_on_List, Date()),0))

You should now see the field correctly recognised as a Number type, and so
you can filter accordingly.

More information:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

 

[Ted]

by jove, alan, you got it right. the general number ploy worked as you hoped
it would

many thanks from a relative newbie.


ted

 

[Sandra Daigle]

Well this is intensely frustrating - you've got it working and *I* don't!

I'm using the Clng function (also tried CInt as indicated in my post to the
other thread) and have the control formatted as General Number yet I still
get the drop down full of dates. I wonder what's different?

 

[Ted]

i hate to have to say this, because i'm scratching me head as i do, but for
some strange reason it's NOT working again. i even tried defining the
Time_on_List: CLng(Nz(DateDiff("d", Me.Date_on_List, Date()),0))
AND using the approach involving the Format Property you suggest together
and when i apply the 'Filter by Form' button and click the drop down arrow,
calendar dates are appearing once again.

ugh :-(

 

[Ted]

yes, for some bizarre reason it now is not working for me either :-(
at least now it's not a question of just one of us being krazy.

Well this is intensely frustrating - you've got it working and *I* don't!

I'm using the Clng function (also tried CInt as indicated in my post to the
other thread) and have the control formatted as General Number yet I still
get the drop down full of dates. I wonder what's different?


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

by jove, alan, you got it right. the general number ploy worked as
you hoped it would

many thanks from a relative newbie.


ted

 

[Sandra Daigle]

:-( I'm sorry - But I'm also glad I'm not the krazy one!!

I guess this is one case where the misinterpreted typecasting of the
calculated field is too persistant.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

yes, for some bizarre reason it now is not working for me either :-(
at least now it's not a question of just one of us being krazy.

Well this is intensely frustrating - you've got it working and *I*
don't!

I'm using the Clng function (also tried CInt as indicated in my post
to the other thread) and have the control formatted as General
Number yet I still get the drop down full of dates. I wonder what's
different?


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

by jove, alan, you got it right. the general number ploy worked as
you hoped it would

many thanks from a relative newbie.


ted

:

hi, my query has a field Time_on_List: = DateDiff("d",
Me.Date_on_List, Date())

my form has an unbound field Time_on_List on it which uses
Time_on_List from the query record source as its control source.

my question comes from what i observe results when i click on
"Filter by
Form" and place my cursor in the "Time_on_List" control. it, a text
box, displays a downward pointing arrow which when depressed
reveals a litany of calendar dates, e.g. 9/27/04, etc. etc. etc.

i presume that since this an unbound control that the
"Time_on_List" quantity displayed when the form's being viewed is
a kind of virtual now you see it now you don't sort of thing but
what am i to make of these dates. and more to my central point, is
there any way to filter by the "Time_on_List quantity?

ted

 

[Ted]

hi sandra,

would you mind putting that into a simpler form? i'm not sure what you mean.

ted

:-( I'm sorry - But I'm also glad I'm not the krazy one!!

I guess this is one case where the misinterpreted typecasting of the
calculated field is too persistant.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

yes, for some bizarre reason it now is not working for me either :-(
at least now it's not a question of just one of us being krazy.

Well this is intensely frustrating - you've got it working and *I*
don't!

I'm using the Clng function (also tried CInt as indicated in my post
to the other thread) and have the control formatted as General
Number yet I still get the drop down full of dates. I wonder what's
different?


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Ted wrote:
by jove, alan, you got it right. the general number ploy worked as
you hoped it would

many thanks from a relative newbie.


ted

:

hi, my query has a field Time_on_List: = DateDiff("d",
Me.Date_on_List, Date())

my form has an unbound field Time_on_List on it which uses
Time_on_List from the query record source as its control source.

my question comes from what i observe results when i click on
"Filter by
Form" and place my cursor in the "Time_on_List" control. it, a text
box, displays a downward pointing arrow which when depressed
reveals a litany of calendar dates, e.g. 9/27/04, etc. etc. etc.

i presume that since this an unbound control that the
"Time_on_List" quantity displayed when the form's being viewed is
a kind of virtual now you see it now you don't sort of thing but
what am i to make of these dates. and more to my central point, is
there any way to filter by the "Time_on_List quantity?

ted

 

[Sandra Daigle]

Hi Ted,

Misinterpreted typecasting - Access *thinks* that the calculated field is a
date field. It is not a date field thus the typecasting has been
misinterpreted.

It is too persistant in that there doesn't seem to be a way to make Access
realize that the field is not a date field.

Sorry I was overly complex in my verbage!

The rest was just my witty reparte' to your comment:




--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

hi sandra,

would you mind putting that into a simpler form? i'm not sure what
you mean.

ted

:-( I'm sorry - But I'm also glad I'm not the krazy one!!

I guess this is one case where the misinterpreted typecasting of the
calculated field is too persistant.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

yes, for some bizarre reason it now is not working for me either :-(
at least now it's not a question of just one of us being krazy.

:

Well this is intensely frustrating - you've got it working and *I*
don't!

I'm using the Clng function (also tried CInt as indicated in my
post to the other thread) and have the control formatted as General
Number yet I still get the drop down full of dates. I wonder what's
different?


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Ted wrote:
by jove, alan, you got it right. the general number ploy worked as
you hoped it would

many thanks from a relative newbie.


ted

:

hi, my query has a field Time_on_List: = DateDiff("d",
Me.Date_on_List, Date())

my form has an unbound field Time_on_List on it which uses
Time_on_List from the query record source as its control source.

my question comes from what i observe results when i click on
"Filter by
Form" and place my cursor in the "Time_on_List" control. it, a
text box, displays a downward pointing arrow which when depressed
reveals a litany of calendar dates, e.g. 9/27/04, etc. etc. etc.

i presume that since this an unbound control that the
"Time_on_List" quantity displayed when the form's being viewed is
a kind of virtual now you see it now you don't sort of thing but
what am i to make of these dates. and more to my central point,
is there any way to filter by the "Time_on_List quantity?

ted

 

[Allen Browne]

Hi Ted, and Sandra

Okay, this does look like a bug in the way the Filter By Form algorithm
works.

It seems to look at the calculated field, figure that it is based only on a
date field, and therefore it must be a date/time type calculated field,
which is pure nonsense.

You can avoid that assumption if you include some other field in the
calculation. For example, if you have an AutoNumber named ID, you could use
this in your query:
Time_on_List: DateDiff("d",[zzzz].[Date_on_List],Date())+[ID]-[ID]
Then when you switch to Filter By Form, the drop-down list is unpopulated,
i.e. you have avoided it being populated with dates.

This is a rather stupid bug, really. As you may have guessed, I never use
Filter By Form, because it's slow and cumbersome and not obvious to end
users, and doesn't even work in the runtime. Anyway, another reason to avoid
FBF.

 

[Sandra Daigle]

To be perfectly honest, I rarely use FBF and my users *never* use it.

This does look like a stupid bug and even with the addition of another field
it still doesn't work quite the way I think Ted expects it to work (i.e. a
drop down showing all the choices of Time_on_list). It does get rid of the
dates though. Another way to get rid of the dates is to change the "Filter
Lookup" property of the control to "Never". I wasn't even aware of this
property until now!

Ted - I'm thinking you will either need to retrain your users or add your
own filtering controls to the form or to another custom form.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Ted, and Sandra

Okay, this does look like a bug in the way the Filter By Form
algorithm works.

It seems to look at the calculated field, figure that it is based
only on a date field, and therefore it must be a date/time type
calculated field, which is pure nonsense.

You can avoid that assumption if you include some other field in the
calculation. For example, if you have an AutoNumber named ID, you
could use this in your query:
Time_on_List: DateDiff("d",[zzzz].[Date_on_List],Date())+[ID]-[ID]
Then when you switch to Filter By Form, the drop-down list is
unpopulated, i.e. you have avoided it being populated with dates.

This is a rather stupid bug, really. As you may have guessed, I never
use Filter By Form, because it's slow and cumbersome and not obvious
to end users, and doesn't even work in the runtime. Anyway, another
reason to avoid FBF.

i hate to have to say this, because i'm scratching me head as i do,
but for some strange reason it's NOT working again. i even tried
defining the Time_on_List: CLng(Nz(DateDiff("d", Me.Date_on_List,
Date()),0)) AND using the approach involving the Format Property you
suggest
together and when i apply the 'Filter by Form' button and click the
drop down arrow,
calendar dates are appearing once again.

ugh :-(

 

[Ted]

hi sandra, thanks for the decode -- looks like there's room for improvement
in there yet.

with best regards,

ted

ps: i 'll tell my users not to use the dropdown list feature when they go to
enter the filter criterion on this conttrol. not a biggee.

Hi Ted,

Misinterpreted typecasting - Access *thinks* that the calculated field is a
date field. It is not a date field thus the typecasting has been
misinterpreted.

It is too persistant in that there doesn't seem to be a way to make Access
realize that the field is not a date field.

Sorry I was overly complex in my verbage!

The rest was just my witty reparte' to your comment:




--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

hi sandra,

would you mind putting that into a simpler form? i'm not sure what
you mean.

ted

:-( I'm sorry - But I'm also glad I'm not the krazy one!!

I guess this is one case where the misinterpreted typecasting of the
calculated field is too persistant.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Ted wrote:
yes, for some bizarre reason it now is not working for me either :-(
at least now it's not a question of just one of us being krazy.

:

Well this is intensely frustrating - you've got it working and *I*
don't!

I'm using the Clng function (also tried CInt as indicated in my
post to the other thread) and have the control formatted as General
Number yet I still get the drop down full of dates. I wonder what's
different?


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Ted wrote:
by jove, alan, you got it right. the general number ploy worked as
you hoped it would

many thanks from a relative newbie.


ted

:

hi, my query has a field Time_on_List: = DateDiff("d",
Me.Date_on_List, Date())

my form has an unbound field Time_on_List on it which uses
Time_on_List from the query record source as its control source.

my question comes from what i observe results when i click on
"Filter by
Form" and place my cursor in the "Time_on_List" control. it, a
text box, displays a downward pointing arrow which when depressed
reveals a litany of calendar dates, e.g. 9/27/04, etc. etc. etc.

i presume that since this an unbound control that the
"Time_on_List" quantity displayed when the form's being viewed is
a kind of virtual now you see it now you don't sort of thing but
what am i to make of these dates. and more to my central point,
is there any way to filter by the "Time_on_List quantity?

ted