# Changing date formulas via drop-down list (listbox) in a report (1 Viewer)

#### FrankD

##### New member
For my financial follow-up, I have a report that consists of several hundred fixed text fields per month, each with its own formula.

So every year I make a copy of the report and I have to adjust all formulas. We are talking about 504 formulas. Depending on the type of INFO
=Nz(DSum("[AMOUNT]";"[tbl_BE27001856500073]";"[CURRENCY DATE] between #2022/01/01#and#2022/01/31# AND [INFO]=""ict"""); 0)

I would like to have only 1 report instead of “one report at year”.
Is there the possibility to open the report via a kind of popup form where only a selection list (Listbox) is displayed?
This selection list (Listbox) is only filled with years.

After selecting the year, the calculated values are displayed according to the chosen year in the report.
=Nz(DSum("[AMOUNT]";"[tbl_BE27001856500073]";"[CURRENCY DATE] between #2022/01/01#and#2022/01/31# AND [INFO]=""ict"""); 0)

Is there anyone who can help me with this, please?

#### arnelgp

##### ..forever waiting... waiting for jellybean!
does table [tbl_BE27001856500073] also change every year?

you need to change every occurrence of #2022/01/01# to DateSerial(Tempvars!tvarRptYear, 1, 1)
and every occurence of #2022/01/31# to DateSerial(Tempvars!tvarRptYear, 1, 31)

on the pop-up form (that you will be making), set the Rowsource of

SELECT Year([Currency Date]) From [tbl_BE27001856500073] GROUP BY Year([Currency Date]);

then you add code to the listbox's AfterUpdate Event:

private sub listbox1_AfterUpdate()
If IsNull(TempVars!tvarRptYear) Then
End If
Tempvars!tvarRptYear = Clng(Nz(Me.listbox1, Year(Date())))
end sub

Last edited:

#### moke123

##### AWF VIP
you need to change every occurrence of #2022/01/01# to DateSerial(Tempvars!tvarRptYear, 1, 1)
and every occurence of #2022/01/31# to DateSerial(Tempvars!tvarRptYear, 1, 31)
What would resolve faster? Using between or the Year()?
Code:
``Year([CURRENCY DATE] ) = YourListBoxYear``

#### FrankD

##### New member
does table [tbl_BE27001856500073] also change every year?

you need to change every occurrence of #2022/01/01# to DateSerial(Tempvars!tvarRptYear, 1, 1)
and every occurence of #2022/01/31# to DateSerial(Tempvars!tvarRptYear, 1, 31)

on the pop-up form (that you will be making), set the Rowsource of

SELECT Year([Currency Date]) From [tbl_BE27001856500073] GROUP BY 1;

then you add code to the listbox's AfterUpdate Event:

private sub listbox1_AfterUpdate()
If IsNull(TempVars!tvarRptYear) Then
End If
Tempvars!tvarRptYear = Clng(Nz(Me.listbox1, Year(Date())))
end sub
Thanks for the support, program code, and additional explanation.
I'll get on with this and come back to it later.

The layout and formats of the table tbl_BE27001856500073 have been unchanged for years.
This table is used to register all banking transactions made.
This is done via a CSV file that is uploaded on a weekly basis.

#### theDBguy

##### I’m here to help
Staff member
Hi. Another possible approach is to simply change your Year textbox to refer to the popup form and adjust all your expressions to use it.

#### Pat Hartman

##### Super Moderator
Staff member
So every year I make a copy of the report and I have to adjust all formulas. We are talking about 504 formulas
This is an Access forum. Sure looks like you are working with Excel. The whole problem can almost certainly be solved by using a totals query that takes a variable for year. You would do the overall summing and grouping in the report.

If you want help creating the query, please post your tables or preferably a database.

#### FrankD

##### New member
This is an Access forum. Sure looks like you are working with Excel. The whole problem can almost certainly be solved by using a totals query that takes a variable for year. You would do the overall summing and grouping in the report.

If you want help creating the query, please post your tables or preferably a database.
Maybe I didn't define it clearly.

The CSV file is exported from my bank account and imported into the temporary Access table.
After checking, the new data is added to the correct table via an append query.

In the past, I had experimented with a crosstab query in a report, but I was unable to do any calculations on the results displayed.

This may be due to my lack of Access knowledge.

#### Pat Hartman

##### Super Moderator
Staff member
he CSV file is exported from my bank account and imported into the temporary Access table.
We get it. You would normalize the data on the way in if you need to. I import bank statements and they are already normalized so I doubt you would need to modify your import process.

You do the calculations in a query and base the crosstab on a query rather than a table.

No matter what solution we provide for your direct question, you will be changing 500+ formulas to implement it just as you would if you had a spreadsheet unless you want help with normalizing the data so we can offer a database solution rather than a spreadsheet solution. When you have 500 individual, identical (in concept) calculations, you have a spreadsheet, not a relational database application.

Replies
6
Views
311
Replies
19
Views
623
Replies
6
Views
329
Replies
3
Views
389
Replies
3
Views
560