Ask Your Question
0

Base query question on calculated feilds

asked 2017-06-09 11:15:21 +0100

Avvy65 gravatar image

With 2 of my databases I have several calculated fields which are done in a query. For example, I buy an item costing £12 including delivery, so I then want to know what both the net cost and vat figures are. I have fields for both net cost and vat, and in the query the calculations are done more or less immediately. But in the form when entering the record data, the same 2 fields are not populated. I found that by entering any figure at all in the fields and then going forward to a next record, and then back again, the 2 fields are populated with the correct data, which is fine.

I'm just curious as to why this is the situation.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-06-09 16:14:40 +0100

Ratslinger gravatar image

updated 2017-06-09 16:25:22 +0100

It is all in the understanding of the process. Both the Query and the form are the same. When the Query gets the information from the DB record, it has the information available to calculate the necessary fields. It does so as it is preparing the record for output to whatever. When you enter data on a form, the only place that data resides is in being prepared to be written to the DB. The data is available to be manipulated in whatever manner, but at this point only through macro coding. It does not exist in the database. Once you complete the entry and proceed to the next or previous record, a return to that record requires the query to pull that info, which contains the necessary info to calculate the data. Now, the information is displayed on the form.

It should also be noted, if you simply update the current screen data, the new information is simply written to the DB and nothing is returned since the record has not changed. In other words, there was no need to use query to get new information and therefore no calculated data was returned.

As for you putting information in these fields, it is a total waste of time. Whether you put something in or not, the result will be the same. In fact, since they are based upon a calculation, they should be set as display only to the user. No entry should be allowed.

This question is a bit surprising since in a previous question, in one of your comments you stated that you understood this process. Also, the immediate display of the calculated information on a form, using macros, was also presented to you in my answer here.

If this answers your question please click on the ✔ (upper left area of answer).

edit flag offensive delete link more

Comments

I'm not a young PC guru who knows everything about linux and Libre Office Base, I'm an older person who know next to nothing about either, and I learn by asking questions. If you think I should not, just say so.

Avvy65 gravatar imageAvvy65 ( 2017-06-09 21:49:05 +0100 )edit

@Avvy65 No, your questions are welcomed. It they were not, I would not have taken the time to answer as I did. I was simply stating that your previous comment stated you understood. However, if not the case, you should ask for more information at that point while the topic is fresh.

Ratslinger gravatar imageRatslinger ( 2017-06-09 21:56:30 +0100 )edit

Also, just to avoid any misunderstandings, I only pointed to the sample so you can see that a form can, under certain circumstances, do what the query does. Unfortunately, it does require macro coding.

Ratslinger gravatar imageRatslinger ( 2017-06-09 22:09:35 +0100 )edit

I do see what you mean about having no need to put any random data into the said fields. I have never used macros at all, not even in MSAccess, so I don't have any idea how to code one. The form will just do me great as it is, and again thanks for your help.

Avvy65 gravatar imageAvvy65 ( 2017-06-12 18:11:56 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-06-09 11:15:21 +0100

Seen: 59 times

Last updated: Jun 09 '17