AccessN00bFilter: How do I replace data in a report, and limit the results in my report to a single day/week/month?
April 29, 2010 7:19 AM Subscribe
AccessN00bFilter: How do I replace data in a report, and limit the results in my report to a single day/week/month?
I have an access database (Access 2007) with several dozen line-items per day with a dozen or so fields per record, and have created a report that displays the information the way I want it to, but I have a question-- one of my fields is titled "ActualSales", but if the value equals the field (not in the report, but in the same origin-table) "EstimatedSales", then it just displays the value "00 0 00".
I want to set the report up so that there is a it looks up the record and replace "ActualSales" with "EstimatedSales" if that occurs, then be able to set the report to give me only one day's worth of data (or one week, one month, etc--- I have years of information stored, so when I first looked at the report, it was over 8000 pages) I can't change the table because I get all the information automatically, and can't change the incoming format
Is this possible? Thank you for any assistance or directions to look!
I have an access database (Access 2007) with several dozen line-items per day with a dozen or so fields per record, and have created a report that displays the information the way I want it to, but I have a question-- one of my fields is titled "ActualSales", but if the value equals the field (not in the report, but in the same origin-table) "EstimatedSales", then it just displays the value "00 0 00".
I want to set the report up so that there is a it looks up the record and replace "ActualSales" with "EstimatedSales" if that occurs, then be able to set the report to give me only one day's worth of data (or one week, one month, etc--- I have years of information stored, so when I first looked at the report, it was over 8000 pages) I can't change the table because I get all the information automatically, and can't change the incoming format
Is this possible? Thank you for any assistance or directions to look!
Response by poster: Thanks dnash, yes, I was looking up the values in a table, by creating a query that asked for date ranges, then making the report off of the query, it worked well. Thank you!
The other half of the question makes me think I could do something of the same thing-- create a criteria that is sort of like an if/then? I'll look around for tutorials, etc- I tried to guess my way through, but the code didn't take.
posted by Seeba at 8:51 AM on April 29, 2010
The other half of the question makes me think I could do something of the same thing-- create a criteria that is sort of like an if/then? I'll look around for tutorials, etc- I tried to guess my way through, but the code didn't take.
posted by Seeba at 8:51 AM on April 29, 2010
This thread is closed to new comments.
Is this report's source a table? Or a query? Because if it's coming directly from a table that would explain why you're getting the whole thing instead of just a day.
You could make a query that asks for a date. Assuming you have a field in the table called "salesDate," then in the query's "design view" on the "criteria" line for that field, put:
[salesDate?]
- including the square brackets. Now when the query - or any report based on that query - is run, a prompt will come up asking for a date to be typed in.
Also in the query design, you could probably set up an If/then type statement for the ActualSales/EstimatedSales situation - but I'm not adept enough with those, so I'll leave that for someone else.
posted by dnash at 7:41 AM on April 29, 2010