query expression help tying Year filter to unbound text box

K

Kemidan2014

Board Regular
Joined
Apr 4, 2022
Messages
226
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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"
 
Upvote 0
Here is how i had it originally - When i hit run it gave me 5 rows of data

1678373758037.png


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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Kemidan2014 said:
even though in the Qeury builder for that graph that field is not being used
Click to expand...
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Solution
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Help repairing a Query Expression show records by a specified year
Replies
1
Views
285
Micron
M
K
  • Question
Displaying Last modified Date in unbound text box
Replies
8
Views
550
Kemidan2014
K
Anigito
  • Solved
MS Access Union Query Changes Numeric values to Text values
2
Replies
11
Views
989
Anigito
Anigito
K
  • Question
Exporting Query Data to Excel for making Charts
Replies
3
Views
715
Micron
M
K
  • Question
Query that can filter using field name
Replies
1
Views
512
Micron
M

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top