Calculated field with DLookUp (1 Viewer)

setis

Registered User.
Local time
Yesterday, 23:38
Joined
Sep 30, 2017
Messages
127
Dear all,

I am trying to calculate an amount on a text box based on a TotalAmount present on the form and the discount percentage related to the provider selected on the same form with is in the table "ProviderDatabase" in the field "SavingsDetails"

With the help of an user here, I got to the expression:

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

I am getting a debug error: "data type mismatch in criteria espression"

Could somebody please point me out where the error can be?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:38
Joined
May 7, 2009
Messages
19,169
check [ProviderID], do you have this in the form?
if so, check its Datatype is it String or Numeric?

sorry, i thought on Post#1 from your previous
thread you are going to use this as a
Calculated Field in the table?@
 

setis

Registered User.
Local time
Yesterday, 23:38
Joined
Sep 30, 2017
Messages
127
check [ProviderID], do you have this in the form?
if so, check its Datatype is it String or Numeric?

sorry, i thought on Post#1 from your previous
thread you are going to use this as a
Calculated Field in the table?@

Hi arnelgp, Thanks a lot for your help.

I would prefer to use it as a calculated field in the table, since I don't need the info in the form. But since you said that it was too complex for being in a calculated field, I don´t mind if I get it in the form.

The ProviderID is in the form with the name of the provider and the bound column is the actual ProviderID (the PK). It shows the provider name and registers in the table the provider key.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:38
Joined
May 7, 2009
Messages
19,169
i think your ProviderID is numeric
and is showing only the Provider Name
because it is in a combobox.

if [ProviderID] is the Name of the combobox,
substitute it in the expression below:

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

setis

Registered User.
Local time
Yesterday, 23:38
Joined
Sep 30, 2017
Messages
127
i think your ProviderID is numeric
and is showing only the Provider Name
because it is in a combobox.

if [ProviderID] is the Name of the combobox,
substitute it in the expression below:

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

Arnelgp, it works partially, thanks a lot.

I need to include that if "SavingsDetails" is 0 or Null, Me.testsaving should be 0.

Could you please help me with that?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:38
Joined
May 7, 2009
Messages
19,169
add another variable in your code:


Dim dblSavings As Double
dblSavings=Nz(Dlookup("SavingsDetails","ProviderDatabase", "ProviderID=" & [comboboxName]),0)
Me.testsaving=Iif(dblSavings=0,0,[TotalAmounttoPay(GBP)]*(1 - dbSavings))
 

setis

Registered User.
Local time
Yesterday, 23:38
Joined
Sep 30, 2017
Messages
127
add another variable in your code:


Dim dblSavings As Double
dblSavings=Nz(Dlookup("SavingsDetails","ProviderDatabase", "ProviderID=" & [comboboxName]),0)
Me.testsaving=Iif(dblSavings=0,0,[TotalAmounttoPay(GBP)]*(1 - dbSavings))

Now it works if "SavingsDetails" is 0 or Null ; the result is 0. But if not, it just shows the same amount that TotalAmounttoPay(GBP)

Edit: No worries, I made it work without the Dim, just inserting the whole thing instead of dblSavings.

Thank you so much!
 
Last edited:

Users who are viewing this thread

Top Bottom