Report Problem

[Keiath]

I have a field in the report to show a return (based on a subform)

=[Forms]![Main Site Table]![Invoices All Groups Totals subform2].[Form]![Text25]

its returning the same result for every record when it would be different.

The key is the Site Number, and I am not sure what I am doing wrong here please help

 

[JHB]

I would suggest to use DLookup, based on the subform's record source, (for the correct syntax of DLookup, look in the Help file).

 

[Keiath]

I tried this

= DLookUp([Invoices All Groups Totals]![SumOfE])

but it errors saying wrong number of arguments

 

[JHB]

Have you look up the DLookup in the Help file, (it need 3 parameter like DLookup(Expr, Domain, Criteria))?
Example:

[B]DLookup([/B]"[CompanyName]", "Shippers", "[ShipperID] = 1"[B])[/B]

 

[Keiath]

Well I have this now

=DLookUp("[Invoices All Col E]![Site Code]","[Invoices All Col E]![SumOfE]=1")

but I dont know what the 1 is meant to be?

And at the moment it returns an error message

Site Code is the Key

Sorry still learning access here

Thanks for your help

 

[JHB]

It is still not correct, (3 parameters, you've only 2), post a stripped version of your database with some sample data, (zip it) + info in which report you have the problem.

 

[Keiath]

Hi

Please see Zipped file, Its the Cash Report when you open you can see the error section.

Once I have that then I can do the rest.

Thanks again for your help

 

[JHB]

The correct syntax is below:

=DLookUp("[SumOfE]";"[Invoices All Col E]";"[Site Code]='" & [Site Number] & "'")

What are you trying to provide?
Because what you have now is a report on 108 pages with the same data in it, I don't think it is what you want!

 

[Keiath]

The correct syntax is below:

=DLookUp("[SumOfE]";"[Invoices All Col E]";"[Site Code]='" & [Site Number] & "'")

What are you trying to provide?
Because what you have now is a report on 108 pages with the same data in it, I don't think it is what you want!

Yeah thats Strange why is it doing that, it should be a 1 page report for that site number,

For in the main database there's over a 1000 sites

Also I copied and pasted your coding and it returns an error message?

 

[JHB]

Yeah thats Strange why is it doing that, it should be a 1 page report for that site number,

NO - it isn't strange - it is you record source for the main report that do it, look at it again it returns 108 records.

Also I copied and pasted your coding and it returns an error message?

Not by me - I've attached the database again.
But you are still not explaining what are you try to provide?
How do you want the report to looks like?

 

[Keiath]

If you look at the report attached

Basically we are a property company that has to produce accounts for each site, as all the information is in the same format, I have built this database that receives all its data from various sources via excel.

Once I up load that data, if you open the Main Site Table you can see how that all works.

Then I need to put that into a report.

This database speeds up the production of the process by a huge amount the Accountant then only has to ensure it all balances and input 3 or 4 numbers.

Now I have that coding thank you (maybe i didn't copy the whole line correctly) I will do that page again something is a little odd there, and It will just be quicker to re do it

 

[Keiath]

Hi Also I noticed just now that your code only returns the first number in the query where it actually needs to add all the amounts togeather

=DLookUp("[SumOfInvoice Amount]","[Invoice All Group Totals]","[Site Code]'")

this should have returned £87668.87, but actually only shows the first amount in the query

Hope that makes sense

 

[JHB]

Hi Also I noticed just now that your code only returns the first number in the query where it actually needs to add all the amounts togeather...

I took the field you pointed to, but so far I can see none of your queries or a single sub form's recordsource, (as I interpreted it from your post #1 it would), actually give the amount you are looking for, only if you add them together. And for this you can't use DLookUp.
Instead the value can be taken direct from a control on the form.

=[Forms]![Main Site Table]![Text351]

 

[Keiath]

Hi Thanks for this, but in the database you attached the field show Name, or an error or 0!

And I seem to have to have the form open on the record to return the right amount but that then goes back to original posting that its showing the same result on every page

 

[Keiath]

Hi Again,

I have worked this out by creating a button on the form that opens the report just for that form, it then returns all the correct data.

If I go to another form and click the button it returns the correct results for that form as well.

It only works when the form in question is 'in view' but that's okay as I would only be printing/viewing a report in the form I have open anyway.