Calculated Field on form (beginner question)

[Williams485]

The SQL is very simple. This is it, exactly as it appears in the SQL view:

SELECT [Opportunities].[FTSP LG Days]+[Opportunities].[SA LG Days] AS qryLGDaysFROM Opportunities;

Having thought about this I can see that the query is looking for data in the Opportunities table, but my form is a data entry form, and the two fields being added together appear on the form, not on the table. I'm looking through Access now, to see if I can spot where the form name is...

[Williams485]

The SQL is exactly as follows:

SELECT [Opportunities].[FTSP LG Days]+[Opportunities].[SA LG Days] AS qryLGDays
FROM Opportunities;

However, I think I now understand the error of my ways (having looked at the SQL). My form is for data entry only and the calculation I want to do uses figures that exist only on the form. In which case, the query does the wrong thing. I think what I need to do is type an expression into the expression builder (which is what I achieved first).

Once I've created the expression, I will be back to the point I was at initially - getting the field to update when an entry is made into one of the fields (instead of updating once a value is inserted into both fields in the expression). I think the solution will be to look at the 'update-related' events...

Please correct me if I'm wrong on this - I don't want to be heading down the wrong track!

[RuralGuy]

Try:
SELECT [FTSP LG Days], [SA LG Days], [FTSP LG Days]+[SA LG Days] AS qryLGDays
FROM Opportunities;
...and then bind two controls to the other two fields.

[Williams485]

thanks again for your continued input - this is helping with my general understanding of Access and it's tools and hopefully moving towards a solution to the current issue.

I've tried your last suggestion but the error remains. I wonder if you could explain why this might work. My understanding tells me that if the data on the form has not yet been added to the database, because it will be solely for data entry, then referencing the data from the Opportunities table won't work (because the data i want to 'query' doesn't yet exist in the database).

I've tried a number of different ways to get this query working, based on some SQL knowledge, but can't figure it out (yet!). The factors that I think might be affecting the issue and that I've checked (and re-checked) are:

- The text boxes [FTSP LG Days] and [SA LG Days] are bound with the Control Source to the appropriate fields in the database to which their values will be added;
- The Record Source for the Form is set to the 'Opportunities' table;
- There are no ambiguous names (that I can find);
- The text box that contains the calculated value has a Control Source of =[Ttl Lead Gen Days]![qryLGDays] (I recreated the query from scratch, with a slightly different name from the one I used previously);
- The SQL expression reads as follows:

SELECT [FTSP LG Days], [SA LG Days], [FTSP LG Days]+[SA LG Days] AS qryLGDays
FROM Opportunities;

Have I missed anything??

I know that this shouldn't be such a difficult thing to resolve, which makes me think that I'm missing something very fundamental...

[RuralGuy]

The calculated control should have a ControlSource of qryLGDays.

[Williams485]

Hello,
Had to move on with the project but have come back around to this particular part - this time with a workable solution. Because of other functionality I wanted to achieve with the form, based on user input (i.e. setting enabled/locked properties, default values, etc) I chose to put the calculation in a code module triggered by the 'AfterUpdate' event of the field. This does what I want it to and updates the field even if one of the value fields in the sum is empty. The code is as follows (nice and simple!):

Private Sub txtFTSPLGDays_AfterUpdate()
Me.txtLGDays = Nz(Me.txtFTSPLGDays, 0) + Nz(Me.txtSACLDays, 0)
End Sub

Thanks for your input on this one, RuralGuy - your comments helped to accelerate my learning of Access (although, I'm still not sure why the query method wasn't working!).

[RuralGuy]

You're welcome. Thanks for posting back with your solution. I marked this thread as Solved for you.