Solved Totalling Unbound Field On Continuous Form (1 Viewer)

LGDGlen

Member
Local time
Today, 14:46
Joined
Jun 29, 2021
Messages
229
Hi All

I have the following form:
1636557758706.png

So far i have the form query supplying the columns of data up to the "Selected Currency" section. The 3 fields on the end (Currency indicator, estimated and actual amounts) are calculated based on the Currency selected on the drop down (it defaults to Euros fyi) and uses either the date of the invoice or the arrival date of the delivery to do a lookup into my currency rate conversion table.

So the last 2 columns are unbound text fields that do a number of steps to get to the converted figures. The issue is that i want to have the totals underneath like the ones shown against the bound fields. But if i add the Sum() below those fields the whole footer shows #Error so i took a while to look up why and it seems that what i should be doing is the calculation in the query itself as the sum function won't work on the unbound text fields.......unless i've misread something.

So i'm trying to use the combox box field from the form in the query to do the calculations but i can't seem to get it to use the value. I found that it might have been that the combobox might be the problem so i'm putting the ID of the currency selected from the combobox into the text field to the right of it to try and use that but if i try and just show that form field in a query field it just shows as blank.

I tried creating a field in the form query as:
Code:
EstConvertedTotal: [Forms]![form-Rep-DeliveryInvoicesAndCNs].[txtCurrencyToConvertTo]

And if i add that to the form it just shows as blank. i'm a bit lost tbh and going round in circles and need some help. I am sure there is more information that is required to resolve this so apologies if i haven't provided it at the start and will be happy to try and supply it if i know what it is i need to supply

anyway, hopefully someone can point me in the right direction

kind regards

Glen
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:46
Joined
May 21, 2018
Messages
8,463
You cannot sum the unbound controls you need to sum the expression of the unbound controls

So if your unbound control txtUnbound is [quantity]*[Price]
you cannot Sum([txtUnbound])
you must Sum([quantity] * [Price])
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:46
Joined
Oct 29, 2018
Messages
21,358
Hi. When you want to use the Sum() function against a calculated value, you'll have to use the same expression used for the calculation. For example, rather than simply using:

=Sum([CalculatedColumn])

you would use something like:

=Sum([Field1] * [Field2])

Hope that helps...

Edit: Oops, too slow...
 

LGDGlen

Member
Local time
Today, 14:46
Joined
Jun 29, 2021
Messages
229
ok so i tried that previously but i'll try again, i think i got the same issue in that it just showed #Error, but gimme a mo and i'll try again
 

LGDGlen

Member
Local time
Today, 14:46
Joined
Jun 29, 2021
Messages
229
yeah i just get #Error in all the footer fields if i do that i think its because there are lookups in the formula that calculates the converted currency amounts:

Code:
=IIf(Nz([EstTotal],0)=0,0,IIf([currency_id]=[cboCurrencyToConvertTo],1,DLookUp("ex_rate_" & DLookUp("currency_short_code","DIM-Currencies","currency_id = " & [currency_id]) & DLookUp("currency_short_code","DIM-Currencies","currency_id = " & [cboCurrencyToConvertTo]),"FACT-DailyExchangeRates","ex_rate_date = " & csql([ConversionDate])))*[EstTotal])

Thats the expression that calculates the estimated amount in the selected currency
 

LGDGlen

Member
Local time
Today, 14:46
Joined
Jun 29, 2021
Messages
229
i wanted to avoid creating fields in the query converting between source currency and all the possible currencies we have in case we add more but would a solution be:
  • Calculate estimated and total amounts into currency 1,2,3 and 4 in 8 separate fields in the form query
  • In the unbound have an IIF that just displays the correct field depending on the combobox selection
  • Total fields sum that IIF statement that way there are no lookups on currencies being down in the footer totals section
like i say i was trying to avoid hard coding loads of fields converting between all the currencies for both the estimated and actual totals but if this works and is the only way to do it i'll do it that way.

this is, however, my inexperienced n00b solution and probably not the best/most efficient so would prefer to see whether there is a better way
 

bastanu

AWF VIP
Local time
Today, 07:46
Joined
Apr 13, 2010
Messages
1,401
What happens if you add this to your form's recordsource query:

Code:
EstimatedAmount:IIf(Nz([EstTotal],0)=0,0,IIf([currency_id]=[Forms]![form-Rep-DeliveryInvoicesAndCNs]![cboCurrencyToConvertTo],1,DLookUp("ex_rate_" & DLookUp("currency_short_code","DIM-Currencies","currency_id = " & [currency_id]) & DLookUp("currency_short_code","DIM-Currencies","currency_id = " & [Forms]![form-Rep-DeliveryInvoicesAndCNs]![cboCurrencyToConvertTo]),"FACT-DailyExchangeRates","ex_rate_date = " & csql([ConversionDate])))*[EstTotal])

Cheers,
 

LGDGlen

Member
Local time
Today, 14:46
Joined
Jun 29, 2021
Messages
229
@bastanu

1636577094421.png


1636577121489.png


no matter what i do i can't get the value of the selected currency from the form................ i'm wondering if i'm fundamentally missing something with this and its really starting to annoy me, i hate not knowing/understanding and just blindly trying different things over and over and getting nowhere :(
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:46
Joined
Oct 29, 2018
Messages
21,358
You might consider posting a demo copy of your db. Just a thought...
 

LGDGlen

Member
Local time
Today, 14:46
Joined
Jun 29, 2021
Messages
229
will try, things a bit of a behemoth and split fe/be so not sure how to post it without ALOT of stressing on my part.............. but i'll take a look tomorrow
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:46
Joined
Oct 29, 2018
Messages
21,358
will try, things a bit of a behemoth and split fe/be so not sure how to post it without ALOT of stressing on my part.............. but i'll take a look tomorrow
Just need to post enough to show the problem - don't need the whole thing.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:46
Joined
May 7, 2009
Messages
19,169
you must have an Updated (daily) Conversion table to be able to compute that.
 

LGDGlen

Member
Local time
Today, 14:46
Joined
Jun 29, 2021
Messages
229
@arnelgp i do have a table for the conversions, the lookups work and i get the right results based on dates and selected currency. the issue really isn't the creation of converted amounts its the totalling of said amounts
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:46
Joined
May 7, 2009
Messages
19,169
maybe you can pick-up something on this demo.
 

Attachments

  • sample_compute_currency.accdb
    1 MB · Views: 398

LGDGlen

Member
Local time
Today, 14:46
Joined
Jun 29, 2021
Messages
229
@arnelgp ok so if i understand things the example doesn't use the sum function but loops through the record set and totals the values and writes that to the footer.... is that right, if so that makes sense and i can certainly take this approach with my form, never really thought about doing it that way but it certainly helps looking at this in a different manner
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:46
Joined
May 7, 2009
Messages
19,169
if i understand things the example doesn't use the sum function but loops through the record set and totals the values and writes that to the footer.
you are quick to notice.. i see in you the future..
and the query is based on TempVars.
 

Users who are viewing this thread

Top Bottom