Solved DSum in datasource (1 Viewer)

[hhag]

Hi,
I've entered the following in the rowsource for a textcontrol:

And I get '#Name?' as a result. And I can't figure out why? I'm using a Swedish version of Access, and I need to use semi-colon...
The intention is to summarize records in field 'TotalSum' in table 'tblTender' entered the previous month.

Does anyone have ny idea?
Thanks in advance!
Rgds

 

[arnelgp]

shouldn't you also enclosed in Quote (") the criteria?

=DSum("TotalSum"; "tblTender", "Year([TenderDate) * 12 + DatePart('m'; [TenderDate]) = " & _
Year(Date()) * 12 + DatePart("m"; Date()) -1)

 

[arnelgp]

The intention is to summarize records in field 'TotalSum' in table 'tblTender' entered the previous month.

does it means from the beginning of the transaction up to Last Month?

=DSum("TotalSum"; "tblTender"; _
    "[TenderDate] <= #" & _
    DateSerial(Year(Date()); Month(Date()); 0) & "#")

 

[Gasman]

Where does the Year * 12 come in to it?

 

[jdraw]

hhag,
Please describe your requirement in plain English so readers can understand WHAT you are trying to achieve. You are showing HOW you have tried to do something, but we need the WHAT clearly. There may be options for HOW it may be done.

 

[hhag]

Hi,
Sorry. Pls, let me try again. And pls let me also expand my desire. My intention is always to try myself first, but when error messages don't disappear it gets a little frustrated
Name of the table: tblTender (the domain)
Name of the field to summarize: TotalSum
Name of the field to set a criteria on: TenderDate

I've actually four different summaries that I would like to do and show the result in three different text boxes/controls. My intention is to write the DSum expressions in the rowsource to all three textboxes.

I want to summarize all records which have tender dates
1. =current month only.
2. = previous month only.
3. =current month, previous year.
4. = rolling 12 months, including current month.

And, thank you all for your help! I'm so thankful!

 

[arnelgp]

"current month"

=DSum("TotalSum"; "tblTender"; _ 
"Format([TenderDate]; 'mmyyyy') = '" & _
Format(Date(); "mmyyyy") & "'")

"previous month"
=DSum("TotalSum"; "tblTender"; _ 
"Format([TenderDate]; 'mmyyyy') = '" & _
Format(DateAdd("m";  -1; Date());  "mmyyyy") & "'")

"current month prev year"
=DSum("TotalSum"; "tblTender"; _ 
"Format([TenderDate]; 'mmyyyy') = '" & _
Format(DateAdd("yyyy";  -1; Date());  "mmyyyy") & "'")

"rolling 12 month"
=DSum("TotalSum"; "tblTender"; _ 
"Format([TenderDate]; 'yyyymm') >= '" & _
Format(DateAdd("m";  -12; Date());  "yyyymm") & "'")

 

[onur_can]

shouldn't you also enclosed in Quote (") the criteria?

=DSum("TotalSum"; "tblTender", "Year([TenderDate) * 12 + DatePart('m'; [TenderDate]) = " & _
Year(Date()) * 12 + DatePart("m"; Date()) -1)

"m" 'm'

which one is right ?

 

[onur_can]

I think you should transfer the criteria to the variables.

Dim a,b
a = Year([TenderDate) * 12 + DatePart("m"; [TenderDate])
b = Year(Date()) * 12 + DatePart("m"; Date()) -1)

txtTotal = DSum ("TotalSum";"tblTender";"a = '" & b & "'")

 

[arnelgp]

i used single quote on the first because it is inside the double qoute.

its better to actually run it. im not on pc right now, just typing on my cp.

 

[Gasman]

I think you should transfer the criteria to the variables.

Dim a,b
a = Year([TenderDate) * 12 + DatePart("m"; [TenderDate])
b = Year(Date()) * 12 + DatePart("m"; Date()) -1)

txtTotal = DSum ("TotalSum";"tblTender";"a = '" & b & "'")

No real need now that arnelgp has supplied the correct syntax and code?

 

[arnelgp]

I think you should transfer the criteria to the variables.

Dim a,b
a = Year([TenderDate) * 12 + DatePart("m"; [TenderDate])
b = Year(Date()) * 12 + DatePart("m"; Date()) -1)

txtTotal = DSum ("TotalSum";"tblTender";"a = '" & b & "'")

tenderdate is a fieldname. so it will complain that it cannot find it.

 

[onur_can]

Yes no need it.
It can be stored in a variable. But when you enter the Criterion, you must add the appropriate marks. You know that the data type is important when determining the criteria,
String,
The spelling of the Number and Date fields are separate from each other. Remember.
I gave the parameter as an example, it can be adjusted according to the returned result.

 

[hhag]

Thanks! I'll try when I'm back at pc.

 

[hhag]

After some changes I made it work. My intention was not to use the VBA editor, but then I decided to do so. And here I needed to swap ';' to ','.
I'm so grateful to all of you helping me out with all of my issues. Without your help, I fear I would have given up a long time ago.
Thank you for sharing your knowledge!
And I know I'll soon ask for some more advice.