HELP!! With adding fields

[chrisguk]

I wanted my database to have the ability to add fields together within my table. I read it wasnt possible in my version and it was best to use a query based on the table.

The name of my is: tblsavings

With the query in design view I have it setup like below. But everytime I save it displays:

Syntax error in query expression 'tblsavings.[std_cost_av_fig:[std_cost_av_base]/[no_days]*365]

Field 1: I_saving
Field 2: ac_saving (NUMERIC VALUES)
Field 3: std_cost_av_base (NUMERIC VALUES)
Field 4: std_cost_av_fig: [std_cost_av_base]/[no_days]*365
Field 5: total_saving:[I_saving] + [ac_saving] + [std_cost_av_fig]
Field 6: b_start_date (DATE VALUES)
Field 7: b_end_date (DATE VALUES)
Field 8: no_days: [b_end_date]-[b_start_date]

The results of the query will be included it in a form with other fields that I need to update. It has been saying that the recordset is not updateable too.

Can anyone help and understand what I am trying to achieve. I wish to ask for a detailed explanation as I am a beginner.

Thanks

 

[jdraw]

You may want to read this carefully
http://www.access-programmers.co.uk/forums/showthread.php?t=172363

 

[chrisguk]

You may want to read this carefully

Dont quite understand why you pasted that. Im just asking for assistance to point me in the right direction.

 

[jdraw]

Well these are the most common mistakes people make, and in most cases , regret what they have done because of the rework involved.

I was trying to ensure you saw these issues first so you could avoid same.

Also, in the second post at that location you will see

> Thou shalt not use "I don't have permission to change it" as an excuse for not fixing bad table structure.

> thou shalt never store values from calculated fields

So that is why I made the post.

 

[CraigDolphin]

I don't see that he is trying to save the calculated values. Seems like he is displaying them in his form.

Are you sure there's no extraneous [ or ] somewhere? If you can post the actual SQL for the query that might help. Better yet, post an example database that has the table(s) and the query (or post the sql of the query if you can't save the query itself)

 

[chrisguk]

I don't see that he is trying to save the calculated values. Seems like he is displaying them in his form.

Are you sure there's no extraneous [ or ] somewhere? If you can post the actual SQL for the query that might help. Better yet, post an example database that has the table(s) and the query (or post the sql of the query if you can't save the query itself)

Hi,

Here is the SQL of the query:

SELECT tblsavings.savingid, tblsavings.queryid, tblsavings.I_saving, tblsavings.ac_saving, tblsavings.std_cost_av_base, Nz([std_cost_av_base])/Nz([no_days])*365 AS std_cost_av_fig, tblsavings.saving_type, Nz([I_saving])+Nz([ac_saving])+Nz([std_cost_av_fig]) AS total_saving, tblsavings.b_start_date, tblsavings.b_end_date, tblsavings.no_days, tblsavings.approval, tblsavings.evidence
FROM tblsavings;

 

[vbaInet]

For all the fields that are calculated, i.e. these:

Field 4: std_cost_av_fig: [std_cost_av_base]/[no_days]*365
Field 5: total_saving:[I_saving] + [ac_saving] + [std_cost_av_fig]
Field 8: no_days: [b_end_date]-[b_start_date]

Remove the table name in the Table row. The individual parts that make up the calculation are fields in the table, but the calculation as a whole is not a field in the table hence, the error.

 

[chrisguk]

Many thanks for that.

The issue I have now is when I type values in the form they do not update the table. Obviously it will not store the calculations which I dont want.

I have explored the normal issues for example a union etc

 

[vbaInet]

It will get saved if the Controls are bound to the fields, i.e. the Control Source property is set to the fields you want updating.

If you use a UNION it will make your table read-only.

Here's a comprehensive list:

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

 

[chrisguk]

It will get saved if the Controls are bound to the fields, i.e. the Control Source property is set to the fields you want updating.

If you use a UNION it will make your table read-only.

Here's a comprehensive list:

Thanks so far, I am learning bit by bit.

So the control in my form is the query which is made from my tblsavings. So I have called the query = qrysavings as the main control within the form. I then select each field from the field list onto the form.

Important to note though that the form, lets call it frmsavings is opened from another linked form called frmlog. They are both joined as a one to many in the relationships via siteid. Just in case that would make a difference. Here is the code I use to link:

Private Sub qry_saving_frm_Click()
Dim stDocName As String
Dim stLinkCriteria As String
    stDocName = "tblsaving_frm"
    DoCmd.OpenForm stDocName, , , , acFormEdit
    DoCmd.GoToRecord , , acNewRec
    Forms![tblsaving_frm]![tblsavings.queryid] = Forms![tbllog_frm]![queryid]
End Sub

 

[vbaInet]

I don't understand what you're getting at with your last post.

If you make changes to the form tblsaving_frm, you may need to requery tbllog_frm to see the changes made in tblsaving_frm.