i have a line chart in a report that gets its info from a query, right now it is inputting all the dates from when we started, how do i tell it to only look at the current month and the past 12? so it will only be from feb 00 to feb01 or mar 00 to mar 01. B-)
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
newbie needs help with chart 2
- Thread starter smiley0q0
- Start date
-
2
- #2
Your query will have to do that work for you:
enter into the criteria under your "Date" field something like:
Between [Enter a start date] And [Enter an ending date]
You'll then be prompted to only show data between these dates. If you positively know the dates type in Between ThenYourStartDate And YourEndDate. Gord
ghubbell@total.net
enter into the criteria under your "Date" field something like:
Between [Enter a start date] And [Enter an ending date]
You'll then be prompted to only show data between these dates. If you positively know the dates type in Between ThenYourStartDate And YourEndDate. Gord
ghubbell@total.net
- Thread starter
- #3
the dates will change every month, also i have the dates formated as "mmm"" ""yy", if i have the query bring up a form for the user to input the first and last date they want seen on the chart, i can't format that txtbox to be the same as the formated date in my query.
- Thread starter
- #4
i figured out that i can put "Between Date() And DateAdd("m",-11,[MyDate])" in my query and it works like a charm, and i also figured out the date formating thing, but now when i try to preview my report i get the error "The Microsoft Jet database engine does not recognise '[MyDate]' as a valid field name or expression." what does that mean? how can i fix it?
The jet engine doesn't understand your field you have named "[MyDate]". Use the builder to correct this for you as it should probably look like:
"Between Date() And DateAdd("m",-11,[YourTableName]![MyDate])" ... Gord
ghubbell@total.net
"Between Date() And DateAdd("m",-11,[YourTableName]![MyDate])" ... Gord
ghubbell@total.net
- Thread starter
- #6
that didn't work either, you mean use the builder in my query right? it allows my to enter the date but then gives me the error after i hit enter. i haven't worked with graphs or charts very much at all so i am pretty much in the dark...
If your query is giving you good results try going to the report in design view. Click on the chart once and look at its properties. It has made a "crosstab" query for you and you can run this query on its own to see if all is well. I did mean your query before however now you'll have to look in this query for a field name [MyDate] that the chart doesn't recognize.
P.S. sometimes it is faster just to let the wizard build you a new report/chart based on your now perfected query!
Gord
ghubbell@total.net
P.S. sometimes it is faster just to let the wizard build you a new report/chart based on your now perfected query!
Gord
ghubbell@total.net
- Thread starter
- #8
yea i tried to build a new report from scratch and it gave me the same error.
a little more detail of what i am trying to do... i have a table with dates as far back as 1998 up till now, i would like a chart of the info for a rolling 12 month period. it will be updated every month. but i only want the last 12 months to chow on the chart. i would also like the user to be able to input any month they would like and be able to see the 12 previous months from that date. i thought this would be pretty simple, but i could really use some help. my query works fine. either way, from the current date or any date. i just need help on getting my chart to be a rolling 12 month period.
a little more detail of what i am trying to do... i have a table with dates as far back as 1998 up till now, i would like a chart of the info for a rolling 12 month period. it will be updated every month. but i only want the last 12 months to chow on the chart. i would also like the user to be able to input any month they would like and be able to see the 12 previous months from that date. i thought this would be pretty simple, but i could really use some help. my query works fine. either way, from the current date or any date. i just need help on getting my chart to be a rolling 12 month period.
Try this one if you always want one year back:
Between Date() And Date()-365 as criteria for your "[MyDate]" field. If this works then your "working in the query expression" just isn't happy when the graph transforms it. Gord
ghubbell@total.net
Between Date() And Date()-365 as criteria for your "[MyDate]" field. If this works then your "working in the query expression" just isn't happy when the graph transforms it. Gord
ghubbell@total.net
- Thread starter
- #10
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question