Replies

Already poured my first wine! It’s a long weekend here in Sth Aust. All we need is sunshine and it will be perfect!

Kerry

F A N T A S T I C!!!!!

I can’t believe it! You did it!!!! I have been trying to get this to work for about a month on and off!!

I cannot thank you enough!!!

I hope you have the BEST weekend!! Because I will, knowing that I don’t have to think about this ANYMORE!

It makes my database complete.

Regards Kerry.

Another thought Pat……………

Is it possible to put something into or change the Expression

Spent: Sum(IIf(IsNull([AmountSpent]),0,IIf([AmountSpent]0,[AmountSpent])))

that will eliminate the non reconciled transactions from the “spent” field??

Kerry

Booh Hoo.

Sorry, Pat, no I dont want the reports to have the same data. But I do want them to LOOK the same. Just changing the criteria from one to the other.

There is an amount of $1000.00 to DX 6926 that is not reconciled. I want that excluded. It shows up if you run the qry_offCostCentreRpt.

Another way to look at it is”-
In the field Amount Spent (Reconciled rept) it needs to show zero if nothing has been spent and only the sum of the amounts spent that have been reconciled. Always leaving the allocation as balance remaining if nothing has been spent.

It’s times like this I wish I have paid more attention in English classes as school!!! It is so hard to explain things.

Regards
Kerry

Dear Pat

Firstly, I have attached another version of the database with less records in it so that it is easier to see report results on one page.

I appreciate your patience on this one! It is very difficult to demonstrate.

If you run and print each of the reports and lay them side by side – look at the amount allocated on the Budget commitment $68,550 Then on the Reconciled report it is $53,500. A discrepancy of $15,050.00

This is because when I put in the criteria in the Reconciled report, if there has been no expenditure in a line (ie DX6816) the wnole line is not shown, therefore making the Amount allocated total $1,500 short.

I want to have the Reconciled Report to look like the Commitment report and where there is $0.00 spent, show it as $0.00 and leaving in the amount allocated.

You can see by the two reports that DV, DR and DD aren’t even shown on the Reconciled report. Therefore the allocations are not represented.

Unless you can think of another way, the only thing I could think of was, on running the Reconciled Rpt to run the qry_OffCostCentreRpt, delete or transfer the transactions that have not been reconciled to temporary table, and then return them back the the tbl_invoices afterward. Now I know that sounds long winded and cumbersome, and I have no idea how to do it, but that would give the correct totals.

I hope you are still with me after all this!!!

Kerry

Hi there

If you run the other report “Budget Commitment to date” under DX Administration look at 6712 Private Motor Mileage. There is a budget of $50.00 and no expenditure (amount spent). Also straight under that 6713 Travel Expenses $1,200 with no amount spent.

Hope this is what you are refering to?

I am most appreciative of your help.

Regards
Kerry

unfortunately this does not work in 97.

The list appears on the sent email but I cannot print the listing.

Thankyou for your reply. It worked and I have learnt yet another trick I can use in future!

Here is the finished result for anyone who is interested.

Kind Regards

Kerry

THANKYOU!!!!

It seems to work. You are a lifesaver.

Have a wonderful day!

Kerry

HELP!!

I tested and tested this darn thing and guess what! A calculation problem! Naturally I am a little stressed at the moment, as I have about 50 people out there trying to get this thing to work.

I have looked and looked and it appears to this adrenalin filled brain that the formula in Row 22 says add all cells total cells except 21 if the code TOIL or LWOP are present. This doesnt seem to be happening and I dont know WHY?

Please help before the phones start ringing?

Kerry

Between yourself and Lagare Coleman, all of the issues appear to have been sorted out now.

Thank you again for your help – it is greatly appreciated.

Regards

Kerry

Lagare you are truly fantastic! Not only are you very clever, but also extremely patient and obliging.

It seems to do everything I can think of now. I will put it to the test in the real world of work this week.

Your help has been nuch appreciated

Have a great weekend

Kerry

Hi There

This works like a treat – Thankyou very much. Only one small problem the lookup formula in P20 is not following through on all other cells. Not sure why. Are you able to take a peek at this for me please.

Then it will work perfectly.

Kerry

(PS This version looks a little different as I have had to modify the shading colour and put in an instruction sheet for users)

Still not the most desirable work around but I will give it a go.

the Leave codes are part of a Lookup – Not a macro.

=IF(D19=””,””,VLOOKUP(D19,Master!Leave,4))

Kerry

Hi Glenn

Thanks for your suggestion. See my response to Legare.

Kerry