Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    field on a form based on value of two other cells (2003)

    Hello,
    I have a form that has company and program field in the same table. I want to add another field to bring in the budget from a different table based on the selected company and program. I don't want to do a subform. Since one program can have multiple line items for their budget, I have created a query to sum all the line items for each program and give me total. What command do I need to execute to have this happen or how can I do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: field on a form based on value of two other cells (2003)

    You can use the DLookup or DSum function for this. The exact syntax depends on whether the value you want to return is uniquely determined by the program, or by the combination of program and company.

    (If each program belongs to a single company, the total budget depends on the program alone; if several companies can "share" a program, the total budget depends on program and company).

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: field on a form based on value of two other cells (2003)

    OK. Can you provide the following information?
    1) Name of the budget table.
    2a) Name of the field that identifies the company.
    2b) Data type of this field (number or text).
    3a) Name of the field that identifies the program.
    3b) Data type of this field (number or text).
    4) Name of the field that you want to sum.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: field on a form based on value of two other cells (2003)

    It is going to be basd on by a combination of a program and company. When I used the dlookup, I get the "#Error" number. Below is a syntax I used
    =DLookUp("[BudgetAmount]","[qryBudgetsumbyProgram]",[companyNumber]=Forms!frminvoicemain!companynumber and [program]=forms!frminvoicemain!program)
    What am I doing wrong?

  5. #5
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: field on a form based on value of two other cells (2003)

    See previous post. I wanted to use a field from a query which gives me the sum for each company based on their program.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: field on a form based on value of two other cells (2003)

    The third argument of DLookup must also be a string:

    =DLookUp("[BudgetAmount]","[qryBudgetsumbyProgram]","[companyNumber]=Forms!frminvoicemain!companynumber and [program]=forms!frminvoicemain!program")

  7. #7
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: field on a form based on value of two other cells (2003)

    Hi Hans,

    I am still getting an "error" when I do the statement above. I think I am doing something wrong. Maybe my understatement of hte lookup fuction is incorrect, but below is a summary of how I understanding.

    =Dlookup ("[Field that will be use to display]", "<!t>[tablequery where the field resides]", "[field from the tabelquery-in the domain or after the first comma]= forms!name of forms!name of field and second selection same as first criteria statement but with different field selected.")

    Expr = Budgetamount field
    Domain = qrybudgetsumbyprogram
    Criteria 1= company Name in query field matches the company name field in form
    criteria 2 = program in query field matches the program field in form

    What am I doing wrong?



    What could the error be?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: field on a form based on value of two other cells (2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •