Bear of a file, date query problem

[Stacey]

I have a file that was provided on CD that is over 2 million records. I have successfully imported it into Access 97. Now I am building queries against it. There are a few different queries where I need to have a date range as the criteria. My problem lies in the way the date fields exist. There is a Century field, a year, month and day field. There is also a field that consolidates all of those fields called the Deposit Date field. The format of the date is yyyy/mm/dd. Unfortunately, the Data Type for that field is Text. I cannot convert the Data Type because it is giving me an error that there are too many records. I tried putting fields on a form for the user to manually type in the criteria and the query refers to those fields, but it is not pulling back correct data. Does anyone have any suggestions for me on how to get around this?

Thanks
Stacey

 

[The_Doc_Man]

This might take a little longer, but try converting the date on the fly.

Build a query that pulls in every record from your humongous table.

For the Deposit Date field, instead of pulling [Deposit Date], pull back CDate$([Deposit Date])

Now write your date-range queries against this query rather than directly against the table.

 

[Stacey]

Thank you! It is working fine now.

Stacey