rxmijares
asked on
Run a query specifying a date range.
This is a two part question, but first let me explain a few things. I am using Microsoft Access 2003 with linked tables from an AS400. We typically use Access to run small or quick reports that we do not need often. In this case, I wish to pull a report that shows what was shipped during a specific period. Obviously, I can enter the date as YYYYMMDD and it will run the query and show me what I need.
Now for the first question. How can I tell Access that the specific column with the date is an actual date column? In other words, as far as Access is concerned now, it is just a column with numbers in its records. I want Access to recognize that column from the linked AS400 table as a date.
The second question is then how do I ask Access to run a report using a range of dates?
Now for the first question. How can I tell Access that the specific column with the date is an actual date column? In other words, as far as Access is concerned now, it is just a column with numbers in its records. I want Access to recognize that column from the linked AS400 table as a date.
The second question is then how do I ask Access to run a report using a range of dates?
1. In your query, use an expression like:
DateShip: CDate(Format([YourFieldNam e], "0000/00/00"))
2. Use the Filter property of the report:
DateShip Between StartDate And EndDate
Set the property FilterOn to True.
/gustav
DateShip: CDate(Format([YourFieldNam
2. Use the Filter property of the report:
DateShip Between StartDate And EndDate
Set the property FilterOn to True.
/gustav
ASKER
ok, I used the expression DateShip: CDate(Format([YourFieldNam e], "0000/00/00")) and when I run the report, it now shows the value as a date. However, I dont know how to apply the FilterOn property. Could you please elaborate a bit more?
The Property sheet of the report. Pick the tab Data.
/gustav
/gustav
ASKER
Oh, I see where I was confused. I mistated my earlier explanation. I refered to it as a report when in fact I should have stated that I was just using the datasheet. My user prefers to be able to run the query and then export it to Excel.
I have already formatted it that the column is seen as a date. Is there a way to pick a range of dates through the query itself?
I apologize for not explaining this properly.
I have already formatted it that the column is seen as a date. Is there a way to pick a range of dates through the query itself?
I apologize for not explaining this properly.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. That worked great. Once again, sorry for the earlier misunderstanding.
You are welcome!
/gustav
/gustav
CDate : See link below:
http://www.techonthenet.com/access/functions/datatype/cdate.php
Of course, the data has to be order in a good order.