Set textbox field to depend on current date

[lilanngel]

Hi All,

Is it possible to link a textbox to a different field, depending on the current date?

For example:

If the Current Year: 2011
Textbox should display: [Total Spend in 2010] field from "All Spend Query"

But if Current Year: 2012
Textbox to display: [Total Spend in 2011] field from "All Spend Query"



Basically... it's the same textbox (but I want it to display different content from the same query, but different fields). I think I can set up another query to equal the field...depending on the year?

Thank you!!!

[William McKinley]

Hi All,

Is it possible to link a textbox to a different field, depending on the current date?

For example:

If the Current Year: 2011
Textbox should display: [Total Spend in 2010] field from "All Spend Query"

But if Current Year: 2012
Textbox to display: [Total Spend in 2011] field from "All Spend Query"

Basically... it's the same textbox (but I want it to display different content from the same query, but different fields). I think I can set up another query to equal the field...depending on the year?

Thank you!!!

Can you post a listing of the fields in your table?

[lilanngel]

Can you post a listing of the fields in your table?

Hi William!

Sure! We're setting this Access Database up to handle all of our "Spend" information and we plan to update this with spend numbers each year.

So the "All Spend Query" Query has the following fields:

[Total Spend for 2010]
[Total Spend for 2011]
[Total Spend for 2012] (currently equals $0)
[Total Spend for 2013] (currently equals $0)
[Total Spend for 2014] (currently equals $0)
Those fields sum up spend information in another table.

We have a textbox in a form that we want to populate with last year's total spend. I know I can just select the "Total Spend for 2010" field for right now, but I want to make it so that the field will change automatically next year, depending on the current year (Year(Date()). Is there some way I can do this?

Thank you!!

[William McKinley]

Well, I'm no expert, but initially it would seem that you have some normalization issues. Instead of having a field represent a range of dates, you should have a query to aggregate the individual spending records from your table into these groups.


See attached database for my quick attempt.

[lilanngel]

Thank you so much for your response!!

Unfortunately, as I mentioned before, the Totals are the column headings (fields) and not the year, so dlookup doesn't work.

We have totals as column headings because we have different departments that we want to keep track of, and we want to display what each department spent last year... which is why we need the textbox to display a different "field" so to say.

:/

[lilanngel]

I figured it out!! Thanks!!

[William McKinley]

I figured it out!! Thanks!!

You should really consider analyzing the structure of your database before you get to invested into the current state.