Form Calculations not Entered into Table (1 Viewer)

[nalgarryn]

Hi Guys,

I created a Form to enter data into a table. Two fields are numeric and typed into, a third field calculates the ratio instantly. I did that by entering "=field1/field2" into the control box in the properties panel for that form element.

However, when I look at the table, all the data goes in except this calculated ratio. Is there a way to get the form to "write" it to the table?

Alternatively, if anyone knows how to get the table to act like a spreadsheet and automagically calculate that third column, then it actually doesn't even need to appear on the form.

Thanks in advance!

 

[John Big Booty]

Storing calculated fields, in all but a small number of special cases is generally considered to be in breach of the rules of Data Normalisation.

Generally if you can calculate the value at the time of input you can use the same method to re-calculate that value at any time you need to display it on either a form or report. That way if one of the underlying values is changed for some reason, you don't then have to worry about updating the stored calculated result.

 

[nalgarryn]

Okay, that's fair enough. I forgot to mention that I also created a field on my form for the date, and I put in =Now(), and i'd like the date that the data is entered to be stored... So same problem, but not a breach of data normalisation.

 

[John Big Booty]

If you bind that field to the date field in the underlying table, and set the Default Value to Now(), any new records will automagically have the date time stored in that field. Be aware however that the Now() function includes the current time in addition to the current date, which can complicate the comparison of dates, so unless you explicitly require a time component in your date use the Date() function instead.

 

[nalgarryn]

If you bind that field to the date field in the underlying table, and set the Default Value to Now(). . .

I've done the latter, but am not quite sure how to do the former.

Thanks, I didn't know I could use Date() instead of Now().

 

[missinglinq]

To do the former (bind a Control to a Field in the underlying Table/Query) you go to its Control Source (where you previously had placed=Now()) and use the dropdown to select the appropriate Field.

Linq ;0)>