Change datasource of form field based on current year (1 Viewer)

[selvsagt]

Hi.

I have a little "y2k issue".
My DB has many (!) forms that are based on Crosstabs queries. The headers are years, and therefore my formfields has controlsources like 2014, 2015 and so on.

Example:

The table it comes from looks something like this:
tblProject
Project: Myproject
Value: 1000
EndYear: 2014

The crosstab then takes the EndYear as a coloumn header.
Then my forms use these fields, now named 2014, 2015, 2016 .... and give me a nice looking table showing the accumulated "end" for multiple projects.

The problem is when the year changes after 31/12, all fields must manually be changed.
This means I have to manually change 2014 to 2015, 2015 to 2016 and so on in all of my formsfields controllsource.

I have played around to find a way to change the source of the field, and hoped I could say something like =year(now()) and for the next year say =year(now())+1 but this only returns that year as a value, and not as a controlsource...

The query functions nicely (ive changed the system time, and it has no issues with this structure), but the forms source is still in the previous year, and returns and error (of course).
When the year changes past new years eve, so will the headers (fieldname) change... but how to make the form change fieldsource???


I'v would really appreciate any help with this problem!

 

[CJ_London]

not tested, but try including a row value in your crosstab for your minimum year. It does not need to be displayed on your form, but if it is called minyear then your controlsources would be =[minyear], =[minyear]+1, etc

 

[selvsagt]

solved!
Thank you!

I added a value to the query which said year0, year1 and so on based on a loooong iif sentence. After this i replaced the form controll to this field rather than the actual year, and it works

 

[namliam]

The way I solved this is to place a query over the Crosstab query.
This "show" query is created by some VBA placed in the button that opens the form.
It simply "adjusts" the SQL so that my show query has fixed columns but variable contents...

i.e.

dim myQry as string
myqry = " select Rowheading, [2013] as FirstYear, [2014] as secondYear "
myqry = " from crosstabquery "
currentdb.Querydefs("ShowQuery").sql = myqry

Obviously 2013 and 2014 would be variable depending on which 2 years are to be compared in this case.
Substituting them in would be something like:

myqry = " select Rowheading, [" & me.CboFirstYear& "] as FirstYear, [" & me.cboLastYear & "] as secondYear "