Update Query dosn't do the calculation

[ardy]

Hello All:
This is very confusing:banghead:, maybe one of you guys can shed some light on this or give me a solution. I am trying to do a update query on a table. I have constructed a formula which is not working in an update query but it will work in a select query. I have the database attached.

Any Ideas.......Why......?

Ardy

 

[CJ_London]

It would help if you said what didn't work - that way I wouldn't have wasted 10 minutes trying to work out the problem. We give our time freely to help and are not mind readers.

The reason it does not work is because your Water Elevation calculation includes a value which although calculated, has not been saved. Substitute the calculation of this value for this value.

By way of note it is bad practice to include calculated values in a table (with a few exceptions to do with performance) so you should just be presenting this through a query

 

[plog]

Your using a field with a null value in your update query. So when you try do any math, it fails because that field is null. That's the answer to your question.

Now, here's the solution to your issue: you shouldn't store calculated values in a table. Instead you use a query to calculate them when you need them. You don't need that UPDATE query, you just need a SELECT query to do that calculation for you.

Also, why do you have a TIMESTAMP field, a Date field and a Time field? That's wrong for a few reasons: 'Date' and 'Time' are reserved words in Access and make coding and queries harder when you try and write them. Also, you're storing redundant data, the date and time values are in TIMESTAMP, so why store it in 3 seperate fields? You just need one.

 

[ardy]

Thank you both for your prompt help, I really do appreciate it......

CJ_London:
Sorry for the confusion, but I thought if i have the database attached it would be evident, My mistake. I will be more careful in the future, please accept my apology......

plog:
It totally makes sense, I see how it can't do it after your explanation, Thanks......Maybe I should explain the project, I am not really using access to store any thing to be truthful, Access(forms) is just a temporary tool for me to parse and calculate various *.txt and *.csv and prep an EDD to be able to upload to another system which is actually not adequte either but never the less is what we have. Our new system which will be online by hopefully mid this year is doing exactly what you have suggested to calculate on the fly and not store. As for the timestamp you are right. I need to make some changes there.......

I really want to thank you both, it is really helpful to know there are resources like this when you need it.........

Ardy