date parameter not accurate...

[perryclisbee via AccessMonster.com]

I have a table where each record has a resolution date ranging from 1/1/2006
thru 12/31/2006. If I do the followingin the res date field: Between
#1/1/2006# And #3/31/2006#, it will show me all records where the resolution
date is in that range. If I do the following in the res date field: Between
[Start Date:] And [End Date:], where start date is 1/1/2006 and end date is
3/31/2006, it will show me records ranging from 1/1/2006 thru 12/31/2006. Is
there any explanation for this?

Thanks,

Perry

 

[Douglas J. Steele]

Is the resolution date being stored in a Date/Time field, or is it being
stored in a Text field? What you're seeing is consistent with it being a
text field (Text numbering sorts 1, 10, 11, 12, 2, 3, 4, ...)

 

[perryclisbee via AccessMonster.com]

the resolution date is a created field in the query that takes either the
sign date field date, and if that is blank, then the denial date field. Both
of these fields are date/time.

Is the resolution date being stored in a Date/Time field, or is it being
stored in a Text field? What you're seeing is consistent with it being a
text field (Text numbering sorts 1, 10, 11, 12, 2, 3, 4, ...)

I have a table where each record has a resolution date ranging from
1/1/2006

[quoted text clipped - 12 lines]

Perry

 

[John Spencer]

That should be returning a date, but if not try wrapping the whole
expression in CDate or DateValue to force it to be a date.

I would suggest you post the SQL of your query or at a minimum the
expression. That way we have a better chance of analyizing the problem
and giving you an accurate solution.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

the resolution date is a created field in the query that takes either the
sign date field date, and if that is blank, then the denial date field. Both
of these fields are date/time.

Is the resolution date being stored in a Date/Time field, or is it being
stored in a Text field? What you're seeing is consistent with it being a
text field (Text numbering sorts 1, 10, 11, 12, 2, 3, 4, ...)

I have a table where each record has a resolution date ranging from
1/1/2006

[quoted text clipped - 12 lines]

Perry