A tricky calculation (maybe)

[anb001]

I'm looking for some assistance with some (maybe) complex calculations. As I'm not sure whether to use vba code or the Expression builder, I'm putting the post here.

A screenshot of the form is attached, which hopefully will make my explanation easier to understand.

The user is supposed to enter the contents of the tanks "7P" and "7S" into the corresponding text boxes. Furthermore the user should enter the total amount of water produced on the Water Maker in the text box "Wtr Maker".
And finally, if any water has been received from a boat, it should be entered into the "Received" text box. If nothing has been received, that text box should be left with a zero.

The remaining three text boxes (Total, Made & Used) should be calculated. For these calculations, I need to use the data just entered, and also some of the data entered "yesterday". Yesterdays data is obtainable from a tabel through the qryTank query (the date is the Primary key for the table).

- The formula for the "Total" I got already through the Expression builder

- "Made" text box:
The formula should go something like this:
[Made] = [Wtr Maker (the number from today)] - [Wtr Maker (the number from yesterdat)].

- "Used" text box:
This might be a bit more tricky. The formula should be something like this:
[Used] = [Total (Yesterdays number)] + [Made] + [Received] - [Total (Todays number)]

I'm not sure whether above is possible, but if it is, I will appreciate if somebody can assist.

I hope above is clear and understandable. If not, please let me know.

/Anders

 

[vbaInet]

Can we see an image of some records in qryTank.

Also, based on the records, explain which records make up [Total (Yesterdays number)] for example.

 

[anb001]

I have attached a new picture showing both the text boxes, how they should be calculated (if possible), and also relevant data from the qryTanks.

The "yesterday" figures, are the data which was entered, and stored, in the db yesterday. In the attached example, the data in the text boxes is from today (Jan. 5th 2011), and "yesterdays" data will then be the data from Jan. 4th 2011.

/Anders

 

[Insane_ai]

For the yesterday value, maybe a hidden textbox with a Dlookup statemtent for populating the value will work. Once you have that, the calculations should be easy to do in the expression builder.

You may want to employ the Nz function to avoid errors: Nz([field],0) to default values to zero. Nz inspects for null or zero length strings and allows you to specify an alternate value if true.

 

[anb001]

Well, I guess I can delete the "maybe" in the title text

I didn't think about using hidden text boxes etc. That made it a whole lot easier! That'll work fine for know. Should anyone know of a more complex way of doing this (without using hidden text boxes etc, then I still would like to learn about that).

/Anders

 

[Insane_ai]

You could use a complex calculation in the data source employing a dlookup statement.

[Used] = [Total (Yesterdays number)] + [Made] + [Received] - [Total (Todays number)]
Replace [Total (Yesterdays number)] with:
Dlookup("[field]","

","Where [condition] = [value]")

 

[Simon_MT]

Here some dirty options:

1) A hidden multiple combiboxes descending and grabbing the value using the column.
2) Going to the last record (yesterday) then New Record and using send keys Crtl + single quote (post the previous values).

A more elegant way, which bob and & co are much better than me, is to DAO using a VB script to store the last records values and store these values and return these to the New Record.

Apologises in advance

Simon

 

[Dairy Farmer]

I have two almost identical queries to work out milk produced for a milking session.

We have 2 milking sessions a day. One in the morning and one in ther afternoon. A tanker collects the milk at midday. The way we calculate milk produced for a day in by adding PM session from yesterday to AM session from today.

In order to calculate the AM sessions milk I need to take the full reading from AM (milk on hand) and subtract the previous day's PM reading.

Query1 gives me a total of milk on hand after AM session (tank1am + tank2am + tank3am).
Query2 gives me a total of milk on hand after PM session (tank1pm + tank2pm + tank3m). The only difference is that the date field has been altered to take the date and add 1.

Now the two queries are "linked" via DateQuery1 and DateQuery2+1. Now I just have to subtract PM milk on hand (from Query2) from AM milk on hand (from Query1) to get AM milk produced.

One obvious thing is that we milk twice a day, every day, 7/365. So there is always a reading. I also have a check box field should the milk not be collected.

Query1
MilkingDate
Tank1PM
Tank2PM
Tank3PM
MilkOnHandPM = Tank1PM+Tank2PM+Tank3PM
Tank1AM
Tank2AM
Tank3AM
MilkOnHandAM = Tank1AM+Tank2AM+Tank3AM
Collected = Yes/No
MilkBySessionPM = If Collected =Yes Then Tank1PM+Tank2PM+Tank3PM
MilkBySessionPM = If Collected =No Then Tank1PM+Tank2PM+Tank3PM-MilkOnHandAM

Query2
MilkingDate+1
Tank1PM
Tank2PM
Tank3PM
MilkOnHandPM+1 = Tank1PM+Tank2PM+Tank3PM

MilkBySessionAM(Today) = MilkOnHandPM+1(Query2) - MilkOnHandAM(Query1)

 

[vbaInet]

Should anyone know of a more complex way of doing this (without using hidden text boxes etc, then I still would like to learn about that).

I thought you would be seeking a less complex way

You're better off just using a subform that will return only yesterday's record. Create a query, pull in all the necessary fields, Use this query as the source object of the subform, and make the subform invisible.

Drop a textbox on the main form and lets say we call it txtLink, put this as its control source:

=DateAdd("d", -1, DateTextboxName)

In the Link Master Fields property of the subform type in txtLink, don't click the button. In the Link Child Fields property type DateReport.

But the date may not be yesterday's date, e.g. if today is a Monday and we want "yesterday's" records, we probably want Friday's records and not yesterday. Use the DMax() function to get the date less than today's date.

For the first calculation, Made, you do something like this:

= IIF(Nz([COLOR=Red]Wtr Maker (Today)[/COLOR], "") <> "", [COLOR=Red]Wtr Maker (Today)[/COLOR] - [SubformControlName]![PWWaterMaker], Null)

Obviously Wtr Maker (Today) should be pointing to that Water Maker textbox.

In the used textbox:

=IIF(Nz(TotalTextbox, "") <> "" And  Nz(MadeTexbox, "") <> "", [SubformControlName]![PWTotal] + MadeTextbox + ReceivedTextbox - TotalTextbox, Null)

 

[anb001]

VbaInet,

The 'more complex' part, is just to learn.

Thanks a lot for all the assistance

/Anders

 

[vbaInet]

VbaInet,

The 'more complex' part, is just to learn.

Alrighty Anders

Happy to share.