Solved Totalling Unbound Field On Continuous Form

[LGDGlen]

Hi All

I have the following form:

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:

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 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]

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]

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]

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:

=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]

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]

What happens if you add this to your form's recordsource query:

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]

@bastanu

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]

You might consider posting a demo copy of your db. Just a thought...

 

[LGDGlen]

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]

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.

 

[LGDGlen]

@theDBguy will try in the morning

 

[arnelgp]

you must have an Updated (daily) Conversion table to be able to compute that.

 

[LGDGlen]

@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

 

[arnelgp]

maybe you can pick-up something on this demo.

 

[LGDGlen]

@arnelgp downloading now and taking a look

 

[LGDGlen]

@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]

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.

 

[LGDGlen]

@arnelgp brilliant, thank you, i have it working now using your examples, thank you so much.