Results 1 to 12 of 12
  1. #1
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167

    separating USD from CAD


    I have a form with $ and currency amounts (along with a bunch of other fields) that i would like to sum the amounts for each currency (USD CAD)

    typically i would add a text field at the form footer as follows: =sum([amt])

    this works perfectly if there is only 1 currency. Typically there are both USD and CAD though. Can someone suggest how to sum the [amt] fields for each [currency] ?

    thanks in advance

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    how are you specifying what the currency is?

    Assuming you have a field called say currType and populated with "USD" or "CAD" your formula in a query would be something like

    USDTtl:sum(-Amt*currType="USD")
    CADTtl:sum(-Amt*currType="CAD")

  3. #3
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    The record has either USD or CAD entered by the user.
    (continuous form) being used.

    my two fields names are [sumofamt] and [curr]

    i've tried using the two suggestions you've given but neither works.

    * note i'm creating a text field on the form and pasting the expression on the CONTROL property of the text field.

    Can you suggestion another solution?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try:

    =Sum(IIf(curr="CAD", [amt], Null))

    =Sum(IIf(curr="USD", [amt], Null))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    missed the fact it was on a form footer. try

    =sum(-[Amt]*[curr]="USD")
    =sum(-[Amt]*[curr]="CAD")

  6. #6
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    I seem to be having difficulty doing a simple sum expression in this form.
    typically I would just add a txt field and set the control =sum[amt] to sum the values in [amt] field.

    This form however is based on a query that has the [amt] field summed (i need to do this as there may be multiple charges on a particular invoice that i want summed before they show up on my total invoice list.
    (eg.meaning i invoice a customer for 3 items for a total of $99 against invoice # 123, I want to show all my invoices on another form and on that form i don't want to see all the individual charges what i want to see is all the charges per invoice summed together for a total invoice price - 1 total amount per invoice. Then want i want to do with this data is show all the invoices in a continuous form and sum all of these using different filters eg show total amount of invoices in May, and separate both USD from CAD.

    I believe having a continuous form based a query that has the [amt] field summed before it is displayed is preventing me from further summing this [amt] field at the footer of the continuous form.

    Does that make sense?

    Is there a way to sum a field that is previously summed in the underlying query?

    in summary i am trying to sum the [amt] field and group the [curr] field. Where [amt] = the amount of the invoice and the [curr] field is the currency (CAD or USD) of the invoice.

    Any suggestions are greatly appreciated.

    thanks

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if your [amt] control is unbound with a controlsource something like [fld1]+[fld2]

    then you need to repeat that calculation in your total control

    =sum(-([fld1]+[fld2])*[curr]="USD")

  8. #8
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    Thanks Ajax, that isn't working either.

    The [amt] field is bound to a query that summing the total before it appears on the form.
    I'm trying to sum all the [amt] fields that are "CAD" and "USD" in the form footer with an unbound txt box. this is the field i'm having trouble with.

    any other suggestions?

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    since we are only quoting the way to do it and it is not working, the only suggestion I can make is provide more information. 'isn't working' doesn't help. Suggest provide some screenshots of what your form looks like - indicating where the controls in question are, the nature of the calculation (i.e. the query you are using) etc

    for example this does not help as it does not make sense
    The [amt] field is bound to a query that summing the total before it appears on the form.
    fields are bound to controls, not queries.

  10. #10
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    Click image for larger version. 

Name:	1.JPG 
Views:	11 
Size:	24.3 KB 
ID:	41638
    This is my continuous form. I want to sum the AR field and sort by the Curr field.
    Click image for larger version. 

Name:	2.JPG 
Views:	11 
Size:	24.9 KB 
ID:	41639 This is the query that is the record source for the Form. You can see the [amt] field is totalled by "sum" and the [curr] field is totally by "group by". This allows me to make a query of all my May invoices for instance. within this record set there will be USD and CAD invoices (which show up on the continuous form) . In the footer of the form i want to sum the total CAD and total USD invoices. Click image for larger version. 

Name:	3.JPG 
Views:	12 
Size:	42.4 KB 
ID:	41640
    hopefully this makes sense now?

    thanks in advance.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Not sure what error or value you are getting but try putting brackets around the boolean part. I've just tried it here and works OK


    =sum(-[SumofAmt]*([curr]="USD"))

    only other thought is that Curr is a lookup field to a table with a PK and currency description so the actual value is a number, not text

  12. #12
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    Thanks Ajax, will give this a try.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Separating Controls Question
    By TinaSchoolSecretary in forum Reports
    Replies: 2
    Last Post: 11-04-2015, 07:44 AM
  2. Separating fields
    By markbitman in forum Queries
    Replies: 2
    Last Post: 07-31-2014, 09:05 AM
  3. Separating data into 2-fields?
    By djclntn in forum Queries
    Replies: 2
    Last Post: 11-01-2012, 10:04 AM
  4. Separating information in a table
    By Lupson2011 in forum Queries
    Replies: 27
    Last Post: 05-15-2012, 06:36 AM
  5. Replies: 5
    Last Post: 02-26-2011, 07:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums