Date Range Problem

[lansel]

In setting up a new database I had to import an Excel spreadsheet with all the information which consisted of several years of load status information. I had problems with the dates as they wanted to be text fields instead of date fields. I did a Make Table query converting the dates to the short date MM/DD/YY and it seems to work fine in my table. Now when I created my query for a date range report, it pulls all years within the month/day range that I request. Do you know what I can do to correct this? I have been researching for a couple of hours and don't find that type of problem posted.

Thanks,

 

[pbaldy]

It sounds like Access is doing exactly what you've asked it to do: restricted to a month/day range, but not year. You need to include the year in your criteria.

 

[lansel]

It sounds like Access is doing exactly what you've asked it to do: restricted to a month/day range, but not year. You need to include the year in your criteria.

I am including the year in my criteria, for example I am asking for 04/01/04 through 04/26/04 and Access is giving me all years I have data, i.e. 04/01/00 through 04/26/00, 01, 02, 03. It is not limiting to my request of year 2004. I thought that I might have done something wrong when I changed the text field to a date field.

(Left([PU Date],2) & "/" & Mid([PU Date],3,2) & "/" & Right([PU Date],2))

Thanks for your help.

 

[Mile-O]

Why would you have a date as text and, for safety's sake, use a four digit year instead of a 2 digit year.

 

[lansel]

Date Range PRoblem

I didn't want to have a date as a text field. When I imported the spreadsheet it made all my fields text fields and when I tried to change them to a date field, I lost all the data in that field. I tried to get around that with the Make Table Query and format the text-form date in the query in the mm/dd/yy format by using the left, right and mid string manipulation functions. This seem to work fine in the table but now I am experiencing the date range problem.

 

[Mile-O]

I didn't want to have a date as a text field. When I imported the spreadsheet it made all my fields text fields and when I tried to change them to a date field, I lost all the data in that field. I tried to get around that with the Make Table Query and format the text-form date in the query in the mm/dd/yy format by using the left, right and mid string manipulation functions. This seem to work fine in the table but now I am experiencing the date range problem.

If you have the table defined before hand and then import the spreadsheet you should have the field defined as a date - Access will be able to convert the text to a date unless there are some anomalous styles of date input.

Also, to ensure a date from something you posted earlier it would be better to surround it with the CDate() function.

CDate(Left([PU Date],2) & "/" & Mid([PU Date],3,2) & "/" & Right([PU Date],2)))