Replies

Thanks for the info but I cant even spell sofisticated let alone be it!!
At present the queries are just in a macro that runs each one in sequence. The Macro is chosen from a switchboard item. I tried messages in the macro but as these require you to click on OK, it defeats the purpose of letting this loong process run unattended.
Any illumination on either Wendells or Pats approach would be much appreciated
Steve

Think I got it – subtract 4 days from date used to determine week no
DateWorked By Week: Format$(HoursWorked.DateWorked-4,’ww’)

Hans,
minorish issue
While I can build the interest rate and loan period into the query, is there a way to have a form where these are entered then used for the calculation. What’s throwing me is there is no actual link to the individual record, just a variable I’d like to be able to set in one place and re-use but also be able to change to see the flow through effect on the amount that can be borrowed. Hope this makes sense
Steve

Excellent – Thanks Again
Steve

Thanks yet again Hans (when do you ever sleep ?)
I had understood the PV function was probably what I need but am lost on the VBA side
Steve

Yes – all date fields
Adding brackets didnt help
Note the final result looks OK eg 1/10/04 but it is being treated as text when sorted so that netx item in sort is 1/11/04 not 2/10/04

Got there – Thanks again for your great assistance
Steve

Sorry Hans – I should have been more specific
I have two accounting systems, Encounter and MYOB. At the end of each day, Encounter exports data on sales and purchases in a form that can be imported by MYOB and I need to ensure these balance or explain any differences. I am working on the Sales side only now.
One of the source of differences is that data can be directly entered into MYOB, by passing Encounter and resulting in a different Profit and Loss than that generated by Encounter. The union query consolidates data from the 3 different sources (1 Encounter Export, 2 MYOB Imports from Encounter and 3 direct input into MYOB) into one table giving each source as a specific name (Operation) eg Encounter Exports vs MYOBImport vs MYOBReceipts. This then gives me the ability to have a cross tab report that shows columns for each source and enables me to manipulate the data for comparisons – there should be no difference between Encounter Exports and MYOB Imports, therefore any difference in the P&L should be explained by MYOB direct entries- ie MYOB Receipts
In my problem case, as there were no MYOBReceipts (direct entries into MYOB) for this date, there is no column in the CrossTab query for this date. This column therefore gets Gets reset to Expr1 (The crosstab is used for later calculations ). When the final query tries to add that non existent number to the MYOBImported number, I get “The Microsoft Jet Database does not recognize ‘AllTransactionsbyAcct_Crosstab.MYOBReceipts’as a valid field name or expression.
Hope this is clearer. Main intent is to take data from 3 sources and match them, identifying where there are differences
Steve

Thank you both very much for the time and trouble taken
Steve

Hans
Thanks again for your quick and excellent reposnse.
I do have the problem with invoices including letters which show up as #Error in the query. Is there a way I can check if the CLng process gives an error, in which case use the invoice no as it, otherwise use the Clng invoice no
Steve

The database are running on the server and are not split into FE and BE

Sorry I should have mentioned that all Office and Windows patches have been applied (first thing I do with a new machine)

No outlook addins (except backup) but am synchronising to a Pocket PC (Dell Axim running Windows ver 2)

No outlook addins (except backup) but am synchronising to a Pocket PC (Dell Axim running Windows ver 2)

It’s bad enough when Hans says something cant be done (which really means it can’t) but having Wendell say the same thing means even some one as pig headed as me needs to give up on my plan A and follow your advice – Thank you both for your time , trouble and knowledge- you have no idea how much the help is appreciated
Steve