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

#1 (**permalink**) 12-Nov-2005, 22:40 PM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/7255-display-value-field-based-value-2-a.html
though??? Bizare, no? Any suggestions? Other methods I could use? (I've
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7255
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 (**permalink**) 12-Nov-2005, 22:40 PM

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] & """)")
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7255

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" Canberra@discussions.microsoft.com>
wrote in message news:9F5A91A7-AAF4-4E89-8671-A872C9F97FD6@microsoft.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7255
> 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

#3 (**permalink**) 12-Nov-2005, 22:40 PM

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] ="
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7255
& Forms![MyFormName]!MySecondFieldControlName)

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

http://msdn.microsoft.com/library/de...HV05187182.asp
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7255

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" 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 (**permalink**) 15-Nov-2005, 14:10 PM

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/de...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" Canberra@discussions.microsoft.com>
wrote in message news:9F5A91A7-AAF4-4E89-8671-A872C9F97FD6@microsoft.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7255
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7255
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 (**permalink**) 16-Nov-2005, 14:50 PM

"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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7255
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Andrew R in Canberra" 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7255
> > 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