Lookup value in a query and display it in a text box on a form

  • Thread starter Thread starter mglowacki
  • Start date Start date
M

mglowacki

Hi,
I have a query set up to sum up all premium payments for a policy, and all
it does is state Policy number and SumOfPremium Payments. I want to
reference this query within the main part of my form, and look up the value
for the record i am on (policy number) and have it return the total premiums
for that policy number.

How can i accomplish this?? Thanks for your help in advance.

Matt
 
Hi,
I have a query set up to sum up all premium payments for a policy, and all
it does is state Policy number and SumOfPremium Payments. I want to
reference this query within the main part of my form, and look up the value
for the record i am on (policy number) and have it return the total premiums
for that policy number.

How can i accomplish this?? Thanks for your help in advance.

Matt
Click to expand...

You could reference the query in a DLookUp function in the textbox's control
source: e.g.

=DLookUp("[SumOfPremiumPayments", "[queryname]", "[PolicyNumber] = " &
[PolicyNumber])

The details might differ a bit (i.e. if PolicyNumber is a Text field you need
quotes around it).
 
Thanks for the reply John, Unfortunately that still is not working for me,
here is what i have put into the control box -

=DLookUp("[SumOfPremium Amount","[Qry_SumByPSP]","[PSP Number] = 800000078")

I hardcoded the PSP number since i know that does show up in the query,
figuring getting that to work is the first step.

Not sure if it matters, but the query is a select query...

Any other suggestions???

John W. Vinson said:
Hi,
I have a query set up to sum up all premium payments for a policy, and all
it does is state Policy number and SumOfPremium Payments. I want to
reference this query within the main part of my form, and look up the value
for the record i am on (policy number) and have it return the total premiums
for that policy number.

How can i accomplish this?? Thanks for your help in advance.

Matt
Click to expand...

You could reference the query in a DLookUp function in the textbox's control
source: e.g.

=DLookUp("[SumOfPremiumPayments", "[queryname]", "[PolicyNumber] = " &
[PolicyNumber])

The details might differ a bit (i.e. if PolicyNumber is a Text field you need
quotes around it).
Click to expand...
 
Okay, i see what my problem was with that, i forgot the other "]" in the
first section, Thanks very much for your help!!!!

Matt

mglowacki said:
Thanks for the reply John, Unfortunately that still is not working for me,
here is what i have put into the control box -

=DLookUp("[SumOfPremium Amount","[Qry_SumByPSP]","[PSP Number] = 800000078")

I hardcoded the PSP number since i know that does show up in the query,
figuring getting that to work is the first step.

Not sure if it matters, but the query is a select query...

Any other suggestions???

John W. Vinson said:
Hi,
I have a query set up to sum up all premium payments for a policy, and all
it does is state Policy number and SumOfPremium Payments. I want to
reference this query within the main part of my form, and look up the value
for the record i am on (policy number) and have it return the total premiums
for that policy number.

How can i accomplish this?? Thanks for your help in advance.

Matt
Click to expand...

You could reference the query in a DLookUp function in the textbox's control
source: e.g.

=DLookUp("[SumOfPremiumPayments", "[queryname]", "[PolicyNumber] = " &
[PolicyNumber])

The details might differ a bit (i.e. if PolicyNumber is a Text field you need
quotes around it).
Click to expand...
Click to expand...
 
i forgot the other "]" in the
first section
Click to expand...

Well, actually *I* forgot it and you just trusted my typing... <g> Sorry about
that and glad you got it working.

Note that you might be able to avoid the query altogether by using DSum() on
the table instead of DLookup() on the query. Just have DSum do the calculation
directly.
 
Would there be an advantage to using Dsum rather than Dlookup?? What is the
sytax for Dsum???

John W. Vinson said:
i forgot the other "]" in the
first section
Click to expand...

Well, actually *I* forgot it and you just trusted my typing... <g> Sorry about
that and glad you got it working.

Note that you might be able to avoid the query altogether by using DSum() on
the table instead of DLookup() on the query. Just have DSum do the calculation
directly.
Click to expand...
 
Would there be an advantage to using Dsum rather than Dlookup??
Click to expand...

Advantages and disadvantages: your database is simpler by one query, and your
query may run faster because it's doing the sum all in one step rather than
first calculating a sum and then looking up the result.
What is the
sytax for Dsum???
Click to expand...

Clearly described in the VBA Help (along with the syntax for DLookUp). It's
actually the same as the syntax for DLookUp:

DSum("field-to-sum", "table-or-query", "optional-criteria")
 
Back
Top