Calculated field in Table (1 Viewer)

setis

Registered User.
Local time
Today, 11:27
Joined
Sep 30, 2017
Messages
127
Dear all,
Apologies for the newbie question.

I have a form calculating a discount on a "TotalPrice" field based on the supplier name.
On the suppliers table there is a "discount" field for every supplier (0,1 for 10% disc)

I did the calculation on the actual form, based on a query with a calculated field.

I would actually prefer to to do this as a calculated field on the table, as I don't need the information on the form.

I can imagine that I should still base the calculation on the query, since it would actually be the vlookup-"ish" function, is this correct? In that case, how should I do this? in the actual table on the back end? I am confused, since the back end does not contain the query.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:27
Joined
May 7, 2009
Messages
19,169
set the control source to:


=[Total Price]*(1-Nz(Dlookup("DiscountField","SupplierTable","SupplierID=" & [SupplierID]),0))


if supplier id is string:


=[Total Price]*(1-Nz(Dlookup("DiscountField","SupplierTable","SupplierID=" & """" & [SupplierID] & """"),0))


***
you need to Requery the textbox whenever there is changes on the record.
 

setis

Registered User.
Local time
Today, 11:27
Joined
Sep 30, 2017
Messages
127
set the control source to:


=[Total Price]*(1-Nz(Dlookup("DiscountField","SupplierTable","SupplierID=" & [SupplierID]),0))


if supplier id is string:


=[Total Price]*(1-Nz(Dlookup("DiscountField","SupplierTable","SupplierID=" & """" & [SupplierID] & """"),0))


***
you need to Requery the textbox whenever there is changes on the record.

Thanks a lot.

I am using the following:

=[TotalAmounttoPay(GBP)]*(1-Nz(Dlookup("SavingsDetails","ProviderDatabase","ProviderID=" & """" & [ProviderID] & """"),0))

and I am getting the error "The expresion you entered contains invalid syntax"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:27
Joined
May 7, 2009
Messages
19,169
try it piece by piece if it will work, start with Dlookup first, then build the Expression if it is ok.
 

setis

Registered User.
Local time
Today, 11:27
Joined
Sep 30, 2017
Messages
127
try it piece by piece if it will work, start with Dlookup first, then build the Expression if it is ok.

I tried only with the Dlookup : =Nz(DLookUp("SavingsDetails","ProviderDatabase","ProviderID=" & """" & [ProviderID] & """"),0)

I get the error: The expression cannot be used in a calculated column
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:27
Joined
May 7, 2009
Messages
19,169
sorry i missed something in post #1. you cant use it on caculated field. calculated field can only interpret simple expression.
 

setis

Registered User.
Local time
Today, 11:27
Joined
Sep 30, 2017
Messages
127
sorry i missed something in post #1. you cant use it on caculated field. calculated field can only interpret simple expression.
No prob. I am trying to do this on the form in VB but I am still getting a debug error :( "Data type mismatch in criteria expression"

I am trying:

Code:
Private Sub cmbTotalAmountToPayGBP_AfterUpdate()
Me.testsaving = [TotalAmounttoPay(GBP)] * (1 - Nz(DLookup("SavingsDetails", "ProviderDatabase", "ProviderID=" & """" & [ProviderID] & """"), 0))End Sub

I'll try to get help in the VB sub Forum
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:27
Joined
Feb 28, 2001
Messages
26,999
Don't do it in the form, then. Don't do it in the table either. You CAN use a calculated field in a QUERY and there is absolutely NO barrier within Access to using a query as a form's RecordSource. And queries are the best place for calculations as long as all elements are available. You can make it a JOIN query to whatever table is the source of the lookup. And it sounds like this would be the perfect opportunity for you to learn this trick.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:27
Joined
Feb 19, 2002
Messages
42,971
Using the Join suggested by Doc is more efficient than using individual DLookup() functions. Each DLookup() runs a separate query so unless you actually need to do it this way (there are reasons), don't. This isn't onw of the cases where DLookup() is necessary so always choose the more efficient option of a Left Join. make sur it is a LEFT Join to handle the situation where the lookup value is missing.
 

setis

Registered User.
Local time
Today, 11:27
Joined
Sep 30, 2017
Messages
127
Don't do it in the form, then. Don't do it in the table either. You CAN use a calculated field in a QUERY and there is absolutely NO barrier within Access to using a query as a form's RecordSource. And queries are the best place for calculations as long as all elements are available. You can make it a JOIN query to whatever table is the source of the lookup. And it sounds like this would be the perfect opportunity for you to learn this trick.

Thank you very much for this suggestion. It makes totally sense.

The problem that I'm facing at the moment is what it should be the easiest part.
I created the query that gives 4 columns as a result, being the result that I want to show in the last one.
The query itself works and get the result based on one of the fields from the form.
I created a text box and in the control source, I find the query and double click on the category that I need. The expression gets like this: [CDSConversionGBP]![TotalAmountToPay(GBP)]

The result in the textbox is: " #Name?"

When I make this to work, how can I bound to the table if I am using the Control Source to get the info from the query?
 

setis

Registered User.
Local time
Today, 11:27
Joined
Sep 30, 2017
Messages
127
As an attempt to make this work, following the advice given to someone with the same problem, I created a non-visible subform with the query result in the form. The amount that I need is now there in the form.

Can I bound that with the field in the main table so it gets registered in there?

I tried creating another text box and referring it to the one from the subform but I get the same error; #Name?

Could anyone suggest a solution, please?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:27
Joined
May 7, 2009
Messages
19,169
good trick and might work.
use your MainForm's Current event to set the Field value:


Private Sub Form_Current
On Error Resume Next
Me.TextBoxOnMainForm = [yourSubFormName]!TextBoxNameOfSubForm
Me.Dirty = False
End Sub
 

setis

Registered User.
Local time
Today, 11:27
Joined
Sep 30, 2017
Messages
127
good trick and might work.
use your MainForm's Current event to set the Field value:


Private Sub Form_Current
On Error Resume Next
Me.TextBoxOnMainForm = [yourSubFormName]!TextBoxNameOfSubForm
Me.Dirty = False
End Sub

I get a "Enter Parameter Value" request on Forms!NameofMyForm!ClaimID (PK of the main table) and then an "Ambiguous name detected: Form_Current
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:27
Joined
May 7, 2009
Messages
19,169
are you using the MainForm's Current Event?
all the variable in my code are just placeholders.
"TextBoxOnMainForm" is the bound textbox that you want to set the value from your query.
"Ambiguous name", meaning you already have one so,just delete the one you created earlier and find the old one.


another thing, set the Link Master/Child Fields on your hidden subform on design view.
the master will be the PK of your main form, the Child is the PK of the subform.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:27
Joined
Feb 19, 2002
Messages
42,971
I get a "Enter Parameter Value" request on Forms!NameofMyForm!ClaimID (PK of the main table) and then an "Ambiguous name detected: Form_Current
When someone posts a line of code and it includes anything similar to "yourname" or "myname", they are trying to tell you to SUBSTITUTE your OWN object name. Please try that. We can't possibly know what you named objects.

One of the causes of a -- #Name? -- error is that you have used a Name for the control that duplicates the name of a bound column and that bound column is not the ControlSource for this control. This happens sometimes when you change things.
Example. You use the wizard to build the form or you drag a column from the other fields list. Access makes the ControlSource the name of the column from the form's RecordSource and it makes the Name property of the control match the column name. So, you have a column named LastName. That becomes the field the control is bound to (ControlSource) and Access also uses it as the Name of the control. If you later decide that you don't want to show separate name parts but want to simply show the full name for the sales person, you change the ControlSource of the LastName field (only because it is already on the form) to
= [FirstName] & " " & [LastName]
But forget to change the Name property of the control. Now you have a control named LastName which is the name of a field in the form's RecordSource but the control is no longer bound to that field. It is now a calculated value and so unbound. Changing the Name property of the control will solve the problem.
 

Users who are viewing this thread

Top Bottom