Between...And Trouble in Query Criteria

[CompAnalyst]

Ok... this is very VERY odd and i can't figure out why it is doing this. Please keep in mind this is someone else's access database that i am trying to update and clean up.

Basically there is a field which tracks the start date of a health care benefit called COBRA, a field which tracks the length of the COBRA coverage (in months), and a field created in a query which adds the months of coverage to the Cobra start date, creating an end date for COBRA coverage.

When I added the criteria "between [enter begining of date range:] and [enter end of date range]" to the COBRA end date field in a different query (not the query where it is doing the date math), I returns very strange values:
It appears to be returning values which are between the MONTHS that are entered, without regard to the year. That is to say if i enter: beg. of date range= 1/1/03 and the end of date range is= 3/1/2005, i will receive query results which cover people whose COBRA is ending in the months of January, February, and March, but of ANY year, not just the year range i specified....
Wierd.
Anyone have any ideas?
This particular database has been converted multiple times and i have updated it to Access 2002-03 and i have tried to remove the main table and clean the data in the hopes that is is some strange conversion error. No luck.... HEEEEEEEELLLLLPPPPPPPP please

Thank You!
Rachel
Compensation Analyst

 

[LittleSmudge]

Try using

"BETWEEN Format([enter begining of date range:], "Medium Date") AND Format([enter end of date range],"Medium Date")"


To me 3/1/2005 is two days after new year - and to my PC its the same - but JET has some really confusing way of interpreting it. So using Format will force everyone to use a std format.


'ope-it-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[CompAnalyst]

ok... i tired that... same problem occurs . I have this feeling i have to do something with the format of the field which is the addition of the months to the start date field. Becuase it appears that it is only using the MONTH part of date to get the query results.

Thanks for trying anyway...