Replies

Does the sum get calculated in the footer of the subform?

I just did a test and found that it worked if I put the NZ function in the subform sum.

=NZ(Sum([Amount]),0)

John,
Long time – hope things are going well.
I have the same issue but am trying to sum on a form created from a Cross Tab Query. I tried your nz process but did’nt seem to work . Is this because its a cross tab as the source
Steve (ex Anna Bay)

Reports have an “HAS DATA” property, which is true or false.

So you should be able test that with an If statement, and either use total from the report, or 0 if the report does not have data.

Sorry – senior moment.
These are in a Form not a Report

Hans/John
Thanks again. I assumed MAX would come into it somewhere but wasnt sure how- Much appreciated

John, I am sure I can get this to work with Quick Books but surprisingly the structure is very different – not quite flat but very non-normalised. However still workable
Steve

Thanks again both of you for your help and interest- I’ll give it a shot

Steve
PS believe it or not John, its not MYOB for a change but ODBC from Quick Books

Records the full date

Hans, Thank you for your help
John (who, by the way Hans, actually has a lot of experience with this particular accounting package) thank you for thinking more laterally than me. After a bit of pain, your suggestion worked like a charm.
Thanks again
Steve.

Sorry John – had replied to Hans before I saw your note.
Yes it is MYOB . The go backwards method may work – let me test it. Thanks
Steve

Thanks for the suggestions.
Sorry Hans, I thought I had explained it properly but I was probably overdoing the detail. I attach a spreadsheet showing some examples.

Basically I need to start with a valid opening Balance and I then add and subtract sales, purchases and adjustments or location movements for the set period to come up with a current closing balance for ALL items

In my example spreadsheet:
Item ID 520 has a quantity of 11 in year 2007 because I have not run the year end and there have been no transactions for this item in 2008
Item ID 652 has a quantity of 3 in year 2007 and a quantity of 2 in year 2008 because there have been transactions for this item in 2008.

I had sorted the year to use process you described but now need help on the following:

If I am doing the stocktake for a period starting in the 2007 year, everything is fine. All items have a 2007 table entry as I have run the 2006 year end or they have been added since then or had transactions since.

However if I am running a stocktake for a period in starting in 2008, I need to use the 2008 balance for item 652 but the 2007 balance for item 520 as there is no 2008 balance yet.

Steve
Hope this is clearer

I am getting “Must use updatable query” – what am I doing wrong.
I have created an Update Query
I have the query which finds the unmatched items and the Table I am trying to update linked and am only including the rows where the joined fields match so only the unmatched (ie no longer manufactured items) are selected – this is working fine
I have one destination field, the Inactive field in the Table which I have set to Update to 0 (or No or False – tried them all. That field type is Yes/No Field.
I am lost
Steve

Hans,
Like most databases I deal with, I have no control over the original data. This is a particularly bad one where the manufacturer has basically lost control of their numbering system eg they allowed 2 digits in their product code for material but now have over 100 materials.
Using a combination of fields, I can get unique identifiers for the products and thats going OK.
My problem is that I want to use a query to update the status of one field to make it “No” (or 0 , -1 I can never rmember which one means No!!) For example there were 1200 different products at one stage, and these are now down to about 1,000. I have identified which ones are no longer produced and I want to use this query to set a field in the main table to inactive. Felt like an update query but I couldnt get there.
Steve
Yes the Word thing did help, thank you – I did have to recreate the Normal.dot as well but that was no problem
Thanks again

Thanks again – I’ve been experimenting with the linking in Excel and its looking pretty good
Steve

Thank you both very much for your help.
As suggested, I have attached a cut down version of the database. The Pivot Table is very close to what I want except for the Total Column after each round (and I even (accidentally !!) got sub totalling working). My perfect world would be to also get this to excel in exactly the same format but I’ll keep working on that.
Re the process to remove the totals – where would I have this activated eg OnReport
Thanks again
Steve

Thanks John
ps did you get the moolah

Hans – thanks yet again
Exactly where should this go – Tried the on Open on main form but got End Sub error, Tried on No data in sub form and its in a loop
Steve

Hans
Thank you – the query is now working well. I had to change the source from the Table STKB to a query as there are calculated fields required in the report and I noticed that it would only read the first 4 letters of the query name (presumably because the original table source was 4 letters long). I just changed the name of the query to be 4 letters long
However, because of my inexperience, I have had to make production of the associated report a 2 step process (2 buttons on the form)
Button 1 calls up the query after selections are made
Button 2 opens the report based on this query
It works but its not pretty. Is there anyway to open the report in preview mode as soon as the groups are selected and the “run query” button clicked
Steve