kind of a funcky problem

[fkeller]

I have a query with 2 modules, 1 that figures average kpg works fine, and one that figures onhand, this one uses a form input for the amount of gallons that we have bought, and subtracts the amount of gallons consumed each time a we fill up. Seems to work fine until you touch one of the list lines then it recalculates.
I thought that once you finished the run things would stay static! not keep changing. the onhand amounts are the only ones that change.

I am not into access that much but have no idea what could be causing this.
have 2 tables 1 hat contains the valid drivers and another that contains the date of fill, amount of gallons used driver and the kilometers on the car. used to figure the kpg, also the amount of gallons used to passed to the other module to figure the amount on hand from gallons bought.

any ideas???

 

[boblarson]

Every time you move the active cursor to another line in the query results, if you have a function, it will run again to ensure that it has the most current calculation. The function also gets run each time for each record upon opening.

So, is there an overriding reason you are using a function to do this instead of in the base query?

 

[fkeller]

Every time you move the active cursor to another line in the query results, if you have a function, it will run again to ensure that it has the most current calculation. The function also gets run each time for each record upon opening.

So, is there an overriding reason you are using a function to do this instead of in the base query?

Other than I am not sure how, no. The function for figuring the KPG does not seem to run multiple times only the one for figuring onhand. How do you decrement the original starting number used for the calculation in the base query?

Original number say 650gals, - the incoming usage from consumption table. needs to decrement the orginal number by the amount coming from the consumption records.

650gl - 8.7gl = 641.3 - 15.8 = 625.5 an so on.

I am new to access, but was a mainframer for years. so a function was easier for me to understand.

 

[fkeller]

I notice 81 views. can someone answer the question on how to decrement the base number, the base query? Not asking someone to do it, but how have tried a few things but have not hit on it yet.

 

[boblarson]

Sorry, been ultra busy the past couple of days. So, do you absolutely need this to be in Query format or can a report work? If you do a report you can do a Running Sum which can be set to be negative to subtract and then it would simplify things immensely (as far as I know).

 

[fkeller]

Sorry, been ultra busy the past couple of days. So, do you absolutely need this to be in Query format or can a report work? If you do a report you can do a Running Sum which can be set to be negative to subtract and then it would simplify things immensely (as far as I know).

So this would be only in the report? does not need to be part of the query that the report executes? sure can set the amount to negative then add the gallons used to that amount thereby decrementing the number.

No way to stop a function from rerunning when you change to an active line?? Like I said am used to mainframe and when you reach the end your done, not reactivate.

 

[boblarson]

So this would be only in the report?

Yes, in a report only.

does not need to be part of the query that the report executes?

Nope, doesn't need to be.

No way to stop a function from rerunning when you change to an active line??

Nope, it is dynamic, alsways going, ESPECIALLY when you move from one field to the next or one record to the next.

 

[fkeller]

Well not doing so good
Have tried the following with running sum
input the number of gallons purchased
have this event gallonsrec - gallons. does not decrement the gallonsrec amount. each line item is subtracted from the orginal amount.

have also tried gallonsrec=gallonsrec-gallons this returns a 0
gallonsrec-gallons=gallonsrec this returns a 0

ideas?

oh also tried to input the gallons purchased as a negative with the same event types on with + instead of - same results

 

[boblarson]

Okay, I put together a sample for you. See the text box on the report marked Running Sum above it. And see that I made the Running Sum property of that text box Group Over All.

 

[fkeller]

Well bob I see what you have done, thanks for the time it took to put together. I will have to rework it a little, as my query also groups by vehicle, and does the average KPG. So running sum overall does not do it. could also just set up 2 reports one for the KPG which is working, and one for the on hand,

thanks a bunch

 

[fkeller]

Well set up the second report to only do the onhand calculation. This works fine, probably another way to do it, but this works so will go with that. Again thanks bob for all the help and time that you put in to show me how.

Working with access is kind of a sideline in my job, so don't get a chance to play with it much, just pucky stuff.