Kyle
What is the value, what do you charge it to. Please give more info

Hi Sami, the table is called TransactionsT.  The form is called TransactionsF.  

There are three controls involved, all which are bound to fields on the TransactionsT table:

TransactionAmount (Currency) where a user can enter a dollar amount
ProcessingFee (Currency) where a user an enter a dollar amount
TotalTransActionAmt (Currnency) which is a locked control with default value of nz([TransactionAmount] + [ProcessingFee],0)

I have to do the calculation of default value in the form because I have to use NZ to treat nulls as 0.  I understand NZ cannot be used in the table field's default value property.  If I do not use NZ, the math does not work.  

All of this works perfectly when a user enters a record through the form, but importing from excel does not work...all of the imported records have a TransactionTotalAmount of $0.00, even though the amount and processing fee are greater than $0.00.

Kyle, you would not need to import this value into Access. Just import the transaction amount and the processing fee. The calculated value will be done in a query that is not stored in the table. Calculated Fields. Don't use the TABLE calculated field type. Those are Evil.

update:  I added a line of code, to the form's onCurrent event, to set the TransTotalAmount control's value accordingly.  So, I no longer need to update a control on the form for this to work.  (previously I was relying on the control's afterupdate event)  

That said, I still need to manually go into each record and move off of it in order to save the update. So, at this point I think I just need something that will open the form, openeach record one by one and save the changes for each record.  

All of this would be so easy if I coulf set the table field's default value to NZ([TransactionAmount] + [ProcessingFee],0)


I hope this makes sense.

Thanks,
Kyle

thanks Richard, I'll try creating a query as you suggest.

There's a deeper reason why you don't store that date in the table, because in the future, anytime any of that data has to change, you have to make sure you make that calculation update. Whereas if you just calculated on the fly with a query, you never have to worry about that. There are very, very few instances where I recommend using a field in a table to store a calculation. Normally, you do it on the fly in a query or even directly in a form field that doesn't go in the table.

Thanks Richard.  I usually don't do them in the tables but i need to do periodic bulk imports (appends) and using the field's default value was the only way I could get that calculation to fire when importing vs. entering manually through a form.  

Is there a better way to do bulk appends?  For example importing from a spreadsheet using a query?  

Thanks again for the help.

Kyle

Richard, I tried doing this as a query and it still does not work.  I'm not sure what I'm doing wrong, but here is what I did.  I created a new query.  I added the following fields (from TransactionsT) to the query:

TransactionAmount (currency)
ProcessingFee (currency)

I then added a calculated query field to add those 2 together in the query, like this:

TransTotal: nz([TransactionAmount] + [ProcessingFee],0)

The query works but its like the NZ function is being ignored.  Here is what happens:

If the amount is $50 and the processing fee is $0, the TransTotal correctly shows $50.
If the amount is $50 and the processing fee is $5, the TransTotal correctly shows $55.

but

If the amount is $50 and the processing fee is blank, the TransTotal is $0.00.  

If I used the same formula in the afterupdate event of textbox control on the form, it works fine.  So, again its like the query is not acknowledging the NZ function.  

Any ideas?

NZ the amount and the fee separately.

What Sami said is correct.

NZ(A+B,0) will give you a 0 if EITHER A or B is Null.

Null Math

wow, I can't believe I missed that.  Thank you guys.  So, now that I have corrected the above query from this:

TransTotal: nz([TransactionAmount] + [ProcessingFee],0)

to this:

TransTotal: nz([TransactionAmount],0) + nz([ProcessingFee],0)

the query works correctly.  It shows the right math, even if one of the 2 fields is empty.    

But this brings up another question for me:
How do I continue to use table fields as controlsource for most of the form controls but still be able to use the query calculated field on the same form?  I went into properties of the control on the form, and I changed the control source to the calculated field in the query.  The result on the form is a value of #name?.   I did check that the name of the query field is unique.

Thanks again for all the help.
Kyle

You need to change the RecordSource property of the form to the query that includes the calculation... and keep watching my lessons. This is all basic stuff that I cover. :)

Hey, so I do know how to set the form's recordsource to a query, that's not my question.  The issue is the form is already based on a table with data in it.  All I need from the query is one field but I can't see a way to add the calculated query field to a form whose record source is a table.  

If I change the form's record source to the query, there would be no data in the table correct?  This also takes me back to the original problem, which is that when I import data using the TransferSpreadsheet method, it appends records directly to the table, bypassing the form and as a result, none of the calculations in form controls or form events happen.

Hi Kyle, when you import data to an Access table and need additional modifications to the imported rows, typically this can be done with an update query and/or VBA code (such as recordset loop).  An update query is probably easier for your level.  Suppose your table looks like this:

ID    A    B    C
100   12   34   46
101   56   78   134
102   4    22   26
103   7    8
104   9    10
105   11   12

where ID is the primary key field, and C is the total column of the sum A+B.  You just imported the last three rows, and they are missing the totals for C.  To fill in C, you need to run an update query that looks like this:

     UPDATE Table1 SET C = A + B WHERE ID > 102

This will put the totals into column C for all the rows where the ID field is greater than 102.  

For this to work, you need to know the highest ID value (assuming your IDs are ascendingly generated) before you import, which can be done with the DMax() function.  And of course you need to be familiar with update queries, and queries in general.  This site should have courses and/or free videos for all of this.

"Default value" is used only when you add a new record.  When you change existing data, the default value setting isn't used.

Thank you Kevin.  I think I understand.  So, using your example I would import A & B to the table.  Unique IDs will be generated as part of the import.  Then use an update query to read A & B from the table, add them together in the query and then populate the sums to field C in the table.  (with where clause to only update rows that were just imported)  Or I guess I would use "where C = null correct?

You import the data into a table. You then set up a query with a calculated field that you need, and base the form on the query. You don't generally need to store data that is calculated directly in the table if it can be calculated in a query.

got it, thanks Richard.  Appreciate all the help.

Kyle