query expression help tying Year filter to unbound text box

[Kemidan2014]

I have a pair of querys that fills graphs for a report. my original expression for a Date field for the querys was simply "Year(Date())-1" which correctly graphed the data for the last year

I know that if i want to view this year only i would just do Year(Date()) but since we review the data for the previous month instead of the current month when January rolls around to review Decembers data then everything would be zeroed out or with only January of current year data

I put an unbound text box on my Frontpage form called [evalyear] i tried the following syntax Year([Forms]![FrontPage]![evalyear]) so that we can leave 2023 for this year in there and until we have the meeting in next january before we change the year but the results blanked out all the graphs to which i KNOW there should be some small amount of data in it i even put 2022 to see if it would populate the original data I was confirming with.

essentially i want the user on the front end to not have to mess with the query expression so the unbound text box was my way of making it a little more dynamic t filter for the year

 

[Micron]

Seems like your unbound textbox is pre-popluated with data. Why not just let user type in 2023 or 2022 or whatever? Then your expression could use one of the date functions to build a date from that. I have no idea what the expression that populates the chart looks like now, but perhaps it could be like
"...WHERE Year(someField) = evalyear"

 

[Kemidan2014]

Here is how i had it originally - When i hit run it gave me 5 rows of data

Then i changed Criteria piece to Year(Forms!Frontpage!Evalyear) then typed 2023 in the that field and results was a blank table

I will try your expresssion there and see what happens. i am wondering if i need to change the way the Field name is formatted

 

[Kemidan2014]

omgeesh i just had to remove the word year( and it works. I dont understand why i had to use Year( ) if im using Date() as opposed to using an unbound box where its not needed. thats the part i dont get.

 

[Micron]

Not exactly sure what you're saying but if this Forms!Frontpage!Evalyear is a year value (e.g. 2023) then you would not use the year function to extract a year value from that. For one thing, the value is not a date so can't use date functions on it. You'd want to compare the input 2023 to the year portion of the table field date, assuming that looks like 01/01/2023. Then Year(myDateField) would evaluate the year portion and return records where that matches whatever is in the textbox.
If you've got a solution then perhaps mark this one as solved so as to remove it from the forum list?

 

[Kemidan2014]

Well perhap and perhaps not, 2 of the graphs i am using for some reason will not add the correct data. When i attempt to remake the graphs it complains that [Forms]![Frontpage][evalyear] is not a valid expression even though in the Qeury builder for that graph that field is not being used but i assume its related to simply being derived from the same query that contains this expression.

 

[Micron]

even though in the Qeury builder for that graph that field is not being used

This is often caused by using something in a reference that is overlooked. Example: it's valid in a query and looks right there but there is something (perhaps a textbox) somewhere that uses the same reference, like in a calculated control on a report or form. As for graphs in Access, I won't be of much help I guess, because long ago I formed the opinion that I would rather rub sand in my eyes than use Access graphs.

 

[Kemidan2014]

ha! I agree Access graphs are difficult but my report has 9 seperate graphs 1 is on its own query while other 8 are off the query i was referencing above. i dont know if the issue is the graphs i am struggling with are STACKED BAR types while the others are simple donuts or paretos.
how would i approach putting these graphs to excel instead? in an automatic way i am not familiar with how to output query into a counted data like it would show when you review the datasheet in the generated access graph itself

 

[Micron]

There are several methods you can use, possibly all of them from either app (Access or Excel). Transferspreadsheet would be one, automation another. I know nothing about Excel Power Query so can't help with that. Then you can link spreadsheets as tables in Access so pretty sure you can link Access tables to Excel, but have not done so for a long time.

 

[Kemidan2014]

Micron thanks for your help. I was able to successfully transferspreadsheet and set up a consistant workbook that is using that data to count and product graphs i need. the only thing left is to figure out how to get my unbound object frames in my report in Access to correctly display the graphs from that excel sheet.