Form Query

[CM]

Hi

I have a form, Called "Centsys" which contains information on which I have
address details from a Table called "Central", I want to have on the form a
TOTAL box, which will retrieve the information from a query.

I have made a query that is called "Paymentsquery" and has 2 columns. One
for Case Ref and One (SUM of Amount:Amount) which gives me the total amount
paid out under that particular case reference, even if there have been 6
payments of £20 for example, it would only say £120 (i.e below

8561 £120.00
7851 £145.63
8574 £201.52

I just need to get the right hand column data in my form and based on the
ref on the left?

I have tried to link to this query without success, to display the Total
amount for the reference that is currently dispalyed on the form, It works
with the address and other details from a qry, but I cannot seem to get this
to work. I just get #name? in the box instead of the amount?

Any idea's would be grateful!

Cheers

Colin

 

[Ken Snell]

Try using the DLookup function in the ControlSource for the TOTAL control:

=DLookup("SUM of Amount", "Paymentsquery", "[Case Ref]=" & [Case Ref])

The above assumes that your form has a control/field named Case Ref in its
RecordSource and that that control/field has the value that matches the Case
Ref value in the query.

 

[CM]

Thanks Ken,

It is now showing Error?,

I tried another way, with some success...but not totally what I
needed.....The form in question is based on a query and if I add to that
query the sum of amount and the table to which I refer, it only shows 3
records instead of 56 records, the reason being is that only those 3 showing
have payments made against them. Is there a way I can avoid this to show
0.00 if nothing is registered?

Any additional help would be appreciated!

Cheers

Colin

Try using the DLookup function in the ControlSource for the TOTAL control:

=DLookup("SUM of Amount", "Paymentsquery", "[Case Ref]=" & [Case Ref])

The above assumes that your form has a control/field named Case Ref in its
RecordSource and that that control/field has the value that matches the Case
Ref value in the query.

--
Ken Snell
<MS ACCESS MVP>

Hi

I have a form, Called "Centsys" which contains information on which I have
address details from a Table called "Central", I want to have on the

form

a
TOTAL box, which will retrieve the information from a query.

I have made a query that is called "Paymentsquery" and has 2 columns. One
for Case Ref and One (SUM of Amount:Amount) which gives me the total amount
paid out under that particular case reference, even if there have been 6
payments of £20 for example, it would only say £120 (i.e below

8561 £120.00
7851 £145.63
8574 £201.52

I just need to get the right hand column data in my form and based on the
ref on the left?

I have tried to link to this query without success, to display the Total
amount for the reference that is currently dispalyed on the form, It works
with the address and other details from a qry, but I cannot seem to get this
to work. I just get #name? in the box instead of the amount?

Any idea's would be grateful!

Cheers

Colin

 

[Ken Snell]

You're welcome.

Thanks Ken,

What you told me about the Right and Left joins worked!! The sum shows up ok
now and all 56 are shown regardless.

Thanks very much for you rhelp!

Cheers

Colin

Without knowing more about your form's setup, the names of the fields in its
recordsource, and the names of its controls, I can't give any explanation
for the #Error that you're seeing. If you provide this info, I'm sure we can
identify the source.

With respect to what you have now tried in the query, what you need to

do

is
to change the linking line between the table and the query so that it's a
LEFT or RIGHT join (depending on how the link is established) so that all
records from the table/query (regardless of whether is an amount or not) are
shown, and only the records from the query where there is a SUM of Amount
are shown, and then use a calculated field in the query instead of the SUM
of Amount field. This calculated field would have an expression similar to
this:

SumAmount: Nz([SUM of Amount], 0)

That will show a zero whenever the SUM of Amount value is Null.

If you can post more info about the SQL statement that you're using in the
query that is now showing just three records, I can point to how this change
would be made.
--
Ken Snell
<MS ACCESS MVP>

Thanks Ken,

It is now showing Error?,

I tried another way, with some success...but not totally what I
needed.....The form in question is based on a query and if I add to that
query the sum of amount and the table to which I refer, it only shows 3
records instead of 56 records, the reason being is that only those 3 showing
have payments made against them. Is there a way I can avoid this to show
0.00 if nothing is registered?

Any additional help would be appreciated!

Cheers

Colin



Try using the DLookup function in the ControlSource for the TOTAL control:

=DLookup("SUM of Amount", "Paymentsquery", "[Case Ref]=" & [Case Ref])

The above assumes that your form has a control/field named Case Ref

in
its

RecordSource and that that control/field has the value that matches the
Case
Ref value in the query.

--
Ken Snell
<MS ACCESS MVP>

Hi

I have a form, Called "Centsys" which contains information on

which

I been to
get