Calculating consumption (1 Viewer)

[HeshamHatem]

What sort of value can it be ?!!
And how will it affect the results ?!

 

[HeshamHatem]

I entered the date but it showed error that the formula is missing something or it is too complex

 

[The_Doc_Man]

it comes back asking to identify the LatestDate

Invariably, this question comes from either (a) an inputbox (and you would know if you had built one) or (b) a query that uses something that Access cannot find.

If you put a variable defined via VBA into a query that is executed using the .Execute option, the SQL engine (which resides in a different memory space) cannot see variables so easily because it doesn't share the same memory. You need to do a string substitution into the SQL string to make the value appear like a literal value.

If the variable is referenced in a context of DoCmd.RunSQL, there is a chance that Access would find that variable if it is public. But even there it is questionable.

And if the problem is merely that something isn't spelled correctly (variable or field or even Access constant), you'll NEVER find it until you correct the spelling.

 

[HeshamHatem]

So far my problem isn’t solved guys. I have been facing this problem for so long now and it is stopping my work. This is a small part of my database and I want it done. For sure there is a way of making this query run peacefully and to be ever running. Or like I asked before if a finction can be created using VBA

 

[JHB]

Show some "raw" data in a Excel-sheet or MS-Access database.

 

[HeshamHatem]

There is a sample in my second or third post

 

[HeshamHatem]

Here is a sample

Kindly guys help me out here as this is a last item in my database and is putting all my work on hold.

 

[JHB]

Open the query in the attached database.

 

[HeshamHatem]

JHB

A million thanks to you buddy.

Now I have the code and I can modulate it thanks to you.
But that means that whenever I want to add or remove a meter I will have to go and change the code. Is there an easier way to do it without having to play with the SQL in the future ?

 

[JHB]

..
But that means that whenever I want to add or remove a meter I will have to go and change the code. Is there an easier way to do it without having to play with the SQL in the future ?

What do you mean by "add or remove a meter", (a measuring point)?
And also "change the code", what code, (the sub query)?
If you store the data vertically for each measuring point, you could build some VBA code there could create a query.

..
Is there an easier way to do it without having to play with the SQL in the future ?

Do you think DLookUp is easier to deal, (I do not)?

 

[HeshamHatem]

Yes. By adding a meter I mean adding a new measuring point and I will indeed by storing the data vertically so can you help me find a way for this to be done automatically without having me to enter and manipulate the SQL every time I add or remove a measuring point ?

And the Dlookup isn’t the smartest way around this and I tried it many times and it always fails at some point. VBA would be a great way if you can help me with it

 

[JHB]

I'll look into it tomorrow, in the meanwhile you could post some vertically sample data, (3-4 measuring points).

 

[HeshamHatem]

Here is a big sample my friend

I'm really grateful for your efforts

 

[JHB]

The sample data you sent isn't stored vertically, (if you think so, then Google for Normalization), so I've taken a small part of it and made it vertically.
Open the form and click the button.
You can turn measuring points off and on by setting the Active (in the tblCounterName).

 

[Acropolis]

Just had a look through that last example DB posted, the usage shown in the second column for each meter, is the difference between the current meter readings and the first meter reading.

Not sure what the OP is looking for, but I would imagine it's the usage between each meter reading not between the meter reading for the date and the first meter reading.

 

[JHB]

Just had a look through that last example DB posted, the usage shown in the second column for each meter, is the difference between the current meter readings and the first meter reading.

You are completely right, new database attached.

 

[HeshamHatem]

JHB thanks a lot my friend.

May now you tell me how you did it so that I can apply it on my data base ??

I know I'm asking for too much .. thanks for your efforts

 

[JHB]

...
May now you tell me how you did it so that I can apply it on my data base ??
..

Did you look at the code behind the button, (what exactly make you trouble to understand, tell me then I'll explain it)?
But first you've to organize the data vertically, and not like you showed in the sample data you provided - because that is how you would do it in a Excel sheet, horizontally, that is a no go in a database.

 

[HeshamHatem]

JHB

Actually I can't thank you enough for your efforts as you have helped me greatly buddy.

I've gone through the code and now I can say that I understand somehow how it works.
one last thing to ask you.. Some of the measuring points are found to have a constant factor for each one of them than needs to be multiplied to the reading or to the subtraction output to get the actual consumption.

as you might have seen in the excel sheet I attached as sample, some measuring points had a factor like 750 or 50 multiplied to the subtraction process output to get the correct consumption. what should I know do to achieve this ?

I know I have asked for much but I promise this is the last issue in this case.

Hope you still have the desire to help me JHB.

 

[JHB]

..
as you might have seen in the excel sheet I attached as sample, some measuring points had a factor like 750 or 50 multiplied to the subtraction process output to get the correct consumption...

Sorry NO, I have't seen that, could you show me where?

..
Hope you still have the desire to help me JHB.

Oh, I'm still here!