MS ACCESS CREATE TABLE with a calculated field through VBA

[johny6685]

Hi

Is there a way to create table with a calculated type column through VBA.

Currently I am using the below code But I am getting, Syntax error in field Expression.

db.Execute "CREATE TABLE tblFields(FieldName TEXT (55), " _
& "FieldValue TEXT (55), STotal NUMBER, Goods NUMBER, Goods% " _
& "CALCULATED ([Goods]/[STotal]) );"

Any Help pls!!!

[twinnyfo]

johny,

One general rule is that tables should not have a calculated field. So, there is no calculated data type. However, you should be able to create a field of the type required and simply insert the value you are calculate.

PS, check the spacing carefully in your code.

Hope this helps.

[jforbes]

Typically, calculated columns are taken care of by a Query.

First create a Table to store all the values entered by the user (or other method of input) then create a Query for any of your calculations. You can attempt to use the Query as the basis for your Form as long as the Query doesn't get to complex.

You might want to refer to the following if you plan on creating an editable Form based on a Query:

https://support.office.com/en-in/article/Use-a-query-as-the-record-source-for-a-form-or-report-e54251f3-57ca-4a7d-8e77-e498966cd41b?ui=en-US&rs=en-IN&ad=IN

http://allenbrowne.com/ser-61.html

[johny6685]

Thank You both. I am going with both of your input i.e. creating a calculation within query and append to the table