473,324 Members | 2,166 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

MS ACCESS CREATE TABLE with a calculated field through VBA

66 64KB
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.

Expand|Select|Wrap|Line Numbers
  1. db.Execute "CREATE TABLE tblFields(FieldName TEXT (55), " _
  2. & "FieldValue TEXT (55), STotal NUMBER, Goods NUMBER, Goods% " _
  3. & "CALCULATED ([Goods]/[STotal]) );"

Any Help pls!!!
Mar 10 '15 #1

✓ answered by 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.

3 3662
twinnyfo
3,653 Expert Mod 2GB
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.
Mar 10 '15 #2
jforbes
1,107 Expert 1GB
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/art...rs=en-IN&ad=IN
http://allenbrowne.com/ser-61.html
Mar 10 '15 #3
johny6685
66 64KB
Thank You both. I am going with both of your input i.e. creating a calculation within query and append to the table
Mar 10 '15 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Cady Steldyn | last post by:
Example: Date | ItemCode | Stock_In_qty | Stock_Out_qty | Bal_qty ------------------------------------------------------------------ 12/09/2003 | A100 | 20 | 0 ...
4
by: Apple | last post by:
Can I edit an calculated field in my form if needed. Thank you in advance for your help. Sincerely Apple
3
by: jburris | last post by:
I have been through enough of these threads to think that this should be an easy fix... but, are there circumstances in which the following code syntax does not work? =!!.Form! (this is out of...
0
by: dhowell | last post by:
I have a datasheet subform which contains values like below: (Datasheets are linked by ID) ID1 item1 %1 ID1 item2 %2 ID1 item3 %3 etc.... (number of entries varies depending on...
2
by: John | last post by:
I am using Access 2000. One table in my database has a field called RankName. Values inlcude: Officer, Sergeant, Lieutenant. I need create a report that groups these three RankNames into two...
5
by: Nour469 | last post by:
Hello, This was supposed to be easy but I could not get it! I need to create a calculated field in Access (preferably in a table or maybe a query) that calculates the BMI. I have fields for the...
0
by: inepu | last post by:
Is there any way to show a field in a pivot table/chart that is calculated based on other fields of the pivot? I have the following table: Date; DescriptionField_1; Slots; EmptySlots I want...
2
by: mver22 | last post by:
In access .mdb I want to create a new table with 5 fields. Code is like: Dim db As DAO.Database Dim tdf As DAO.TableDef Dim fld As DAO.Field Set db = CurrentDb() Set tdf =...
2
by: noclueforid | last post by:
Here is my calculation: =DateAdd("d",-1,DateAdd("d",56,DateAdd("d",-(IIf(Weekday()=1,"0",Weekday(-1))),))) I get "#Error" in the field when displayed. However, when I enter the for the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.