Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Display a value in a field based on the value of 2 other fields

Discussion in 'Information Technology' started by Andrew R in Canberra, Nov 12, 2005.

  1. Andrew R in Canberra

    Andrew R in Canberra
    Expand Collapse
    Guest

    I'm sure this should be easy, but can't make it work, can anyone help me ??
    I have a form based on a table. In that table there are 2 fields that are
    filled in then a 3rd field should display a result that depends on what is in
    the other 2 fields.
    I am trying to use an unbound field that has a query as its control source,
    the query has the 2 filled in fields in it with criteria [Forms]![form
    name]![field name]. This query works, ie it reads the fields in the form and
    based on those criteria presents the value I want from another table. I
    can't seem to get that value to display in the current forms 3rd field
    though??? Bizare, no? Any suggestions? Other methods I could use? (I've
    also tried an action query to update the value from the query to the table,
    but for some reason can't get that to display the result either???)
    Many thanks in anticipation of a genius solution!
    Andrew R in Canberra
     
  2. Loading...

    Similar Threads Forum Date
    Heritage Sikh Artwork On Display At Texas University History of Sikhism Mar 6, 2015
    UK Sikh Turbans on display at Bradford exhibition Breaking News Mar 1, 2014
    History Rare photographs’ display: Sikh community and culture presented in vibrant colour Punjab, Punjabi, Punjabiyat Dec 8, 2013
    Events Manuscripts on display at National Museum of Pakistan. History of Sikhism Mar 3, 2013
    Turban Display Uncovers Sikh History Sikh Youth Jun 7, 2011

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    As you found, you can't put a query statement into the ControlSource of a
    text box. The nearest thing is probably a DLookup() expression, where
    literal values from the other 2 controls is concatenated into the 3rd
    argument. Something like this:
    =DLookup("CreditLimit", "tblCreditLimit", "(ClientID = " &
    Nz([ClientID],0) & ") AND ([Status = """ & [Status] & """)")

    For more help on how to form the 3rd argument, see:
    Getting a value from a table: DLookup()
    at:
    http://allenbrowne.com/casu-07.html

    There might be a better solution. Could you create a query that contains the
    lookup table as well as the main table for your form, and set the
    RecordSource of your form to that query? If so, you can include the lookup
    field in the query, and just treat it like any other field in your form.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Andrew R in Canberra" <Andrew R in Canberra@discussions.microsoft.com>
    wrote in message news:9F5A91A7-AAF4-4E89-8671-A872C9F97FD6@microsoft.com...
    > I'm sure this should be easy, but can't make it work, can anyone help me
    > ??
    > I have a form based on a table. In that table there are 2 fields that are
    > filled in then a 3rd field should display a result that depends on what is
    > in
    > the other 2 fields.
    > I am trying to use an unbound field that has a query as its control
    > source,
    > the query has the 2 filled in fields in it with criteria [Forms]![form
    > name]![field name]. This query works, ie it reads the fields in the form
    > and
    > based on those criteria presents the value I want from another table. I
    > can't seem to get that value to display in the current forms 3rd field
    > though??? Bizare, no? Any suggestions? Other methods I could use? (I've
    > also tried an action query to update the value from the query to the
    > table,
    > but for some reason can't get that to display the result either???)
    > Many thanks in anticipation of a genius solution!
    > Andrew R in Canberra
     
  4. David Lloyd

    David Lloyd
    Expand Collapse
    Guest

    Andrew:

    One alternative you might want to consider is the DLookup function. This
    function allows you to find the value in another table or query based on
    specified criteria. You could use this function to lookup your third value
    based on the value of the other two fields. For example, you can set the
    Control Source property of the form textbox:

    =DLookup("[MyThirdFieldName]", "MyTableOrQueryName", "[MyFirstFieldName] ="
    & Forms![MyFormName]!MyFirstFieldControlName & " AND [MySecondFieldName] ="
    & Forms![MyFormName]!MySecondFieldControlName)

    The following link is an MSDN reference page on how this function works.

    http://msdn.microsoft.com/library/d...n-us/vbaac11/html/acfctDLookup_HV05187182.asp

    You will need to adjust this example for data type, and of course your form,
    control and field names.

    --
    David Lloyd
    MCSD .NET
    http://LemingtonConsulting.com

    This response is supplied "as is" without any representations or warranties.


    "Andrew R in Canberra" <Andrew R in Canberra@discussions.microsoft.com>
    wrote in message news:9F5A91A7-AAF4-4E89-8671-A872C9F97FD6@microsoft.com...
    I'm sure this should be easy, but can't make it work, can anyone help me ??
    I have a form based on a table. In that table there are 2 fields that are
    filled in then a 3rd field should display a result that depends on what is
    in
    the other 2 fields.
    I am trying to use an unbound field that has a query as its control source,
    the query has the 2 filled in fields in it with criteria [Forms]![form
    name]![field name]. This query works, ie it reads the fields in the form
    and
    based on those criteria presents the value I want from another table. I
    can't seem to get that value to display in the current forms 3rd field
    though??? Bizare, no? Any suggestions? Other methods I could use? (I've
    also tried an action query to update the value from the query to the table,
    but for some reason can't get that to display the result either???)
    Many thanks in anticipation of a genius solution!
    Andrew R in Canberra
     
  5. David Lloyd

    David Lloyd
    Expand Collapse
    Guest

    Andrew:

    One alternative you might want to consider is the DLookup function. This
    function allows you to find the value in another table or query based on
    specified criteria. You could use this function to lookup your third value
    based on the value of the other two fields. For example, you can set the
    Control Source property of the form textbox:

    =DLookup("[MyThirdFieldName]", "MyTableOrQueryName", "[MyFirstFieldName] ="
    & Forms![MyFormName]!MyFirstFieldControlName & " AND [MySecondFieldName] ="
    & Forms![MyFormName]!MySecondFieldControlName)

    The following link is an MSDN reference page on how this function works.

    http://msdn.microsoft.com/library/d...n-us/vbaac11/html/acfctDLookup_HV05187182.asp

    You will need to adjust this example for data type, and of course your form,
    control and field names.

    --
    David Lloyd
    MCSD .NET
    http://LemingtonConsulting.com

    This response is supplied "as is" without any representations or warranties.


    "Andrew R in Canberra" <Andrew R in Canberra@discussions.microsoft.com>
    wrote in message news:9F5A91A7-AAF4-4E89-8671-A872C9F97FD6@microsoft.com...
    I'm sure this should be easy, but can't make it work, can anyone help me ??
    I have a form based on a table. In that table there are 2 fields that are
    filled in then a 3rd field should display a result that depends on what is
    in
    the other 2 fields.
    I am trying to use an unbound field that has a query as its control source,
    the query has the 2 filled in fields in it with criteria [Forms]![form
    name]![field name]. This query works, ie it reads the fields in the form
    and
    based on those criteria presents the value I want from another table. I
    can't seem to get that value to display in the current forms 3rd field
    though??? Bizare, no? Any suggestions? Other methods I could use? (I've
    also tried an action query to update the value from the query to the table,
    but for some reason can't get that to display the result either???)
    Many thanks in anticipation of a genius solution!
    Andrew R in Canberra
     
  6. Andrew R in Canberra

    Andrew R in Canberra
    Expand Collapse
    Guest

    Re: Display a value in a field based on the value of 2 other field

    "Allen Browne" wrote:

    > As you found, you can't put a query statement into the ControlSource of a
    > text box. The nearest thing is probably a DLookup() expression, where
    > literal values from the other 2 controls is concatenated into the 3rd
    > argument. Something like this:
    > =DLookup("CreditLimit", "tblCreditLimit", "(ClientID = " &
    > Nz([ClientID],0) & ") AND ([Status = """ & [Status] & """)")
    >
    > For more help on how to form the 3rd argument, see:
    > Getting a value from a table: DLookup()
    > at:
    > http://allenbrowne.com/casu-07.html
    >
    > There might be a better solution. Could you create a query that contains the
    > lookup table as well as the main table for your form, and set the
    > RecordSource of your form to that query? If so, you can include the lookup
    > field in the query, and just treat it like any other field in your form.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Andrew R in Canberra" <Andrew R in Canberra@discussions.microsoft.com>
    > wrote in message news:9F5A91A7-AAF4-4E89-8671-A872C9F97FD6@microsoft.com...
    > > I'm sure this should be easy, but can't make it work, can anyone help me
    > > ??
    > > I have a form based on a table. In that table there are 2 fields that are
    > > filled in then a 3rd field should display a result that depends on what is
    > > in
    > > the other 2 fields.
    > > I am trying to use an unbound field that has a query as its control
    > > source,
    > > the query has the 2 filled in fields in it with criteria [Forms]![form
    > > name]![field name]. This query works, ie it reads the fields in the form
    > > and
    > > based on those criteria presents the value I want from another table. I
    > > can't seem to get that value to display in the current forms 3rd field
    > > though??? Bizare, no? Any suggestions? Other methods I could use? (I've
    > > also tried an action query to update the value from the query to the
    > > table,
    > > but for some reason can't get that to display the result either???)
    > > Many thanks in anticipation of a genius solution!
    > > Andrew R in Canberra

    >
    >
    > Thanks - I'll try both those - in the end I used 3 hidden controls - ie 2 that displayed numbers relating to the 2 choosing fields and another that added the 2 together - I then used a table with a list of possible results and displayed the result using a combo box - It doesnt show the result automatically, but allows only the correct result to be shown!!! strange, but it works!


    Thanks Allan
     

Share This Page