date greater than text

C

Cynthia

I have created a form that sets filters for another form.
The first field is a drop down of fields available (one of these is a date)
The second field is a drop down of starts with = > < like etc
The third field is typed in by the users.
This works fine until I try and use a date.

I need to have my date field > or < the date the users types in the box.
When I do this the filter is not applied. Any help would be greatly
appreciated.
Thank you
Cynthia
 
R

Rick Brandt

Cynthia said:
I have created a form that sets filters for another form.
The first field is a drop down of fields available (one of these is a
date) The second field is a drop down of starts with = > < like etc
The third field is typed in by the users.
This works fine until I try and use a date.

I need to have my date field > or < the date the users types in the
box. When I do this the filter is not applied. Any help would be
greatly appreciated.
Thank you
Cynthia
Click to expand...

I assume you are building up a filter string in some code after the user
makes their selection. Post what that code is. Are you using # to delimit
your date values? You need to.
 
C

Cynthia

Thank you, I left out the #

Rick Brandt said:
I assume you are building up a filter string in some code after the user
makes their selection. Post what that code is. Are you using # to delimit
your date values? You need to.
Click to expand...
 
C

Cynthia

Rick,
My filter is ([qryCircuit].[RouteRelDate]> #8/15/2006#). It is filtering
out some items, but not what I would expect. The routereldate field is
coming from a sql table and is a text field, this is linked to access.
 
R

Rick Brandt

Cynthia said:
Rick,
My filter is ([qryCircuit].[RouteRelDate]> #8/15/2006#). It is
filtering out some items, but not what I would expect. The
routereldate field is coming from a sql table and is a text field,
this is linked to access.
Click to expand...

Well if it's a text field then you are going to get text comparisons. That
wouldn't be a big deal if the format was ISO (yyyy-mm-dd) because then an
alphabetical sort/comparison and a DateTime sort/comparison are identical.
With your format you have littel choice but to convert the field to a
DateTime on-the-fly and apply the criteria to that which will be very
inefficient.

WHERE CDate([qryCircuit].[RouteRelDate]) > #8/15/2006#

There is also the problem that since it is a text field it might contain
values that cannot be converted to proper dates.

Do you know why the creator of that table chose to use a Text field for
that? Is there any possibility of changing it?
 
Top