I am currently having issues filtering data in a query. I have linked an Excel Spreadsheet and a data table in Access. The Excel table contains Sales Order data. The Access table is linked to an Access form in which a Start and End date are retrieved from a user. These user dates are then used in the Access query, which is filtered against a Scheduled Ship Date in the linked Excel table. The Scheduled Ship Date has the format of 'm/d/yyyy h:nn'. I currently have no format set on the Access dates. I can't seem to find the correct format.
I use 'Between [REPORT-SEL]![SDAT] And [REPORT-SEL]![EDAT]' in the Query Criteria for the Scheduled Ship Date filter.
I have tried going into the Excel and setting the format there. Then I try to set the format to the same thing in the form used to input the dates in Access. To look at the linked Excel file, it doesn't appear to have the time stamp with the date, UNTIL you click on one of them - then you can see it is still there.
Consequently, as an example, I enter a Start Date of '01/01/2006' and an End Date of '01/20/2006', even though there are definitely records with the Scheduled Ship Date of '01/20/2006', I get everything BUT those dates - whatever the end date value is, I get everything up to, but not including the end date itself.
Any one have any suggestions? I'ved tried making all the formats match between the user input form and the Excel spreadsheet. Even though I have experience, it's not alot. I have a work-around in place - I use 'Between [REPORT-SEL]![SDAT] And DateAdd("d",1,[REPORT-SEL]![EDAT])', and this seems to work, but I have run up against this very same problem in 2 different Access databases in the past week. If there is a way to do it correctly, I'd rather do it that way - I want to do it right! Not use a work-around.
Please forgive me for using CAPS in some of my question (other than the filter statement) - I use it as a form of emphasis - not yelling. Believe me. I man a Help Desk here where I work and I roil at people who submit their problems in all caps - I feel like I'm being yelled at....
Anyway, thanks for any help you can suggest. It's would be very much appreciated!
I use 'Between [REPORT-SEL]![SDAT] And [REPORT-SEL]![EDAT]' in the Query Criteria for the Scheduled Ship Date filter.
I have tried going into the Excel and setting the format there. Then I try to set the format to the same thing in the form used to input the dates in Access. To look at the linked Excel file, it doesn't appear to have the time stamp with the date, UNTIL you click on one of them - then you can see it is still there.
Consequently, as an example, I enter a Start Date of '01/01/2006' and an End Date of '01/20/2006', even though there are definitely records with the Scheduled Ship Date of '01/20/2006', I get everything BUT those dates - whatever the end date value is, I get everything up to, but not including the end date itself.
Any one have any suggestions? I'ved tried making all the formats match between the user input form and the Excel spreadsheet. Even though I have experience, it's not alot. I have a work-around in place - I use 'Between [REPORT-SEL]![SDAT] And DateAdd("d",1,[REPORT-SEL]![EDAT])', and this seems to work, but I have run up against this very same problem in 2 different Access databases in the past week. If there is a way to do it correctly, I'd rather do it that way - I want to do it right! Not use a work-around.
Please forgive me for using CAPS in some of my question (other than the filter statement) - I use it as a form of emphasis - not yelling. Believe me. I man a Help Desk here where I work and I roil at people who submit their problems in all caps - I feel like I'm being yelled at....
Anyway, thanks for any help you can suggest. It's would be very much appreciated!
