UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Iif, Access 2016    
 
   
mike60smart
post Jan 12 2020, 08:54 AM
Post#1


UtterAccess VIP
Posts: 13,717
Joined: 6-June 05
From: Dunbar,Scotland


Hi Every one

I am trying to construct an If statement to be used on the After Update of a Combobox used to select a Record to go to.

The If statement is as follows:-

CODE
If [OriginalContractSum] > 0 And [TotalC] = 0 Then
    [ContractSumToDate] = [OriginalContractSum] And [RunningTotal] = 0
    End If
    If [OriginalContractSum] > 0 And [TotalC] > 0 Then
    [ContractSumToDate] = [OriginalContractSum] + [TotalC] And [RunningTotal] = 0
    End If
    
    If [txtT] > 0 Then
    [ContractSumToDate] -[txtT]
    End If


When I select a record to go to I get the following error:-
Attached File  error.PNG ( 3.21K )Number of downloads: 0

When I click Debug it highlights [txtT]

Any help in resolving this appreciated. Is there a better way to construct the If ??

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
FrankRuperto
post Jan 12 2020, 09:22 AM
Post#2



Posts: 787
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Is txtT a text field? If so then you are trying to do arithmetic calculation with a text field

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
mike60smart
post Jan 12 2020, 09:35 AM
Post#3


UtterAccess VIP
Posts: 13,717
Joined: 6-June 05
From: Dunbar,Scotland


Hi Frank

It is an Unbound Textbox which is formatted as Currency

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 12 2020, 10:05 AM
Post#4


UtterAccess Moderator
Posts: 12,613
Joined: 6-December 03
From: Telegraph Hill


Hi Mike,

I doubt this will ever work as you intend!

The error you receive is because of this line:
CODE
' ...
    If [txtT] > 0 Then
    [ContractSumToDate] -[txtT]     ' <-- Error will happen here
    End If

You need to assign a value in this statement. You can just perform a mathematical calculation. So what should be [ContractSumToDate] -[txtT] ? Perhaps [ContractSumToDate]? If that's the case then use:
CODE
' ...
    If [txtT] > 0 Then
      [ContractSumToDate] = [ContractSumToDate] - [txtT]
    End If

Otherwise, explain exactly what you're trying to do.

BUT ALSO!!!
CODE
  If [OriginalContractSum] > 0 And [TotalC] = 0 Then
    [ContractSumToDate] = [OriginalContractSum] And [RunningTotal] = 0
  End If
' ...

Here you are setting [ContractSumToDate] to a value of True/False, which I doubt is what you want.

CODE
' ...
    If [OriginalContractSum] > 0 And [TotalC] > 0 Then
    [ContractSumToDate] = [OriginalContractSum] + [TotalC] And [RunningTotal] = 0
    End If
' ...

Similarly, here you are setting [ContractSumToDate] to a value of True/False, which again I doubt is what you want.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Jan 12 2020, 10:26 AM
Post#5


UtterAccess VIP
Posts: 13,717
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

In this statement :-
CODE
If [txtT] > 0 Then
      [ContractSumToDate] = [ContractSumToDate] - [txtT]
    End If


[txtT] is an Unbound Textbox which gets its value using a Form reference to Control on a Subform.

If this value is greater than Zero then Deduct from [ContractSumToDate] otherwise leave [ContractSumToDate] as Zero


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 12 2020, 10:38 AM
Post#6


UtterAccess Moderator
Posts: 12,613
Joined: 6-December 03
From: Telegraph Hill


>> If this value is greater than Zero then Deduct from [ContractSumToDate] otherwise leave [ContractSumToDate] as Zero <<

This assumes that [ContractSumToDate] starts off as Zero? Is that the case?

If so, then the expression you quoted should work. If not, you can use:
CODE
    If [txtT] > 0 Then
      [ContractSumToDate] = [ContractSumToDate] - [txtT]
    Else
      [ContractSumToDate] = 0
    End If


It would be good if you explained what *should* be happening in the code above the expression you quoted.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Jan 12 2020, 11:19 AM
Post#7


UtterAccess VIP
Posts: 13,717
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

More detail about what should happen:-
CODE
If [OriginalContractSum] > 0 And [TotalC] = 0 Then
    [ContractSumToDate] = [OriginalContractSum] And [RunningTotal] = 0
    End If


In the above [OriginalContractSum] is a Manual Entry.
[TotalC] is an Unbound Textbox formatted as currency which gets is value using a Form Reference to a Subform.

So if TotalC = Zero Then populate ContractSumToDate with OriginalContractSum and RunningTotal with Zero

CODE
  If [OriginalContractSum] > 0 And [TotalC] > 0 Then
    [ContractSumToDate] = ([OriginalContractSum] + [TotalC]) And [RunningTotal] = 0
    End If


If both ContractSumToDate and TotalC is Greater than Zero Then ContractSumToDate = OriginalContractSum Plus TotalC and RunningTotal = Zero

Hope that is as clear as mud David



--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 12 2020, 11:29 AM
Post#8


UtterAccess Moderator
Posts: 12,613
Joined: 6-December 03
From: Telegraph Hill


Try it this way, Mike. I think it should do all you require, but make sure you test all permutations.
CODE
  If [OriginalContractSum] > 0 Then
    [ContractSumToDate] = [OriginalContractSum] + Nz([TotalC], 0)
    [RunningTotal] = 0
  End If
  [ContractSumToDate] = [ContractSumToDate] - [txtT]

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Jan 12 2020, 11:58 AM
Post#9


UtterAccess VIP
Posts: 13,717
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

This is what I have tried:-

CODE
If [OriginalContractSum] > 0 Then
    [ContractSumToDate] = [OriginalContractSum] + Nz([TotalC], 0)
    [RunningTotal] = 0
  End If
  [ContractSumToDate] = [ContractSumToDate] - [txtT]
    
   If [txtT] > 0 Then
      [ContractSumToDate] = [ContractSumToDate] - [txtT]
    Else
      [ContractSumToDate] = 0
    End If


It is giving this error:-


And it is not Subtracting txtT from ContractSumToDate

The Controls are displayed as follows:-

Attached File  values.PNG ( 3.71K )Number of downloads: 2


The Control displaying $13,797.20 is txtT




--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 12 2020, 12:01 PM
Post#10


UtterAccess Moderator
Posts: 12,613
Joined: 6-December 03
From: Telegraph Hill


No error shows.

Also, I meant you should just use the code:
CODE
  If [OriginalContractSum] > 0 Then
    [ContractSumToDate] = [OriginalContractSum] + Nz([TotalC], 0)
    [RunningTotal] = 0
  End If
  [ContractSumToDate] = [ContractSumToDate] - [txtT]

Remove the stuff that follows.

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Jan 12 2020, 12:17 PM
Post#11


UtterAccess VIP
Posts: 13,717
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

I tried that and this is the error that appears

Attached File  error.PNG ( 2.68K )Number of downloads: 0

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 12 2020, 12:22 PM
Post#12


UtterAccess Moderator
Posts: 12,613
Joined: 6-December 03
From: Telegraph Hill


What is the ControlSource of [RunningTotal] ?

If it is a field in a query is it a calculated field?

--------------------


Regards,

David Marten
Go to the top of the page
 
FrankRuperto
post Jan 12 2020, 12:45 PM
Post#13



Posts: 787
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


David,

So in Access one can use unbound text fields as part of a math calculation without having to cast or declare as a variant?

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
cheekybuddha
post Jan 12 2020, 01:38 PM
Post#14


UtterAccess Moderator
Posts: 12,613
Joined: 6-December 03
From: Telegraph Hill


@Frank, a textbox control will contain a Variant value. So it can be Null, numeric, date, string.

VBA is pretty good at coercing types as required, but you should feel free to cast the values as a specific datatype if required.

The .Value (default) property of a textbox control will generally have coerced the value as it is returned, which means you can generally use it as an argument to a function/sub, which isn't necessarily possible with a Variant variable

For instance, say you have a function:
CODE
Function Hello(strParam As String) As String

  Hello = "Hello " & strParam &"!"

End Function

You can then do:
CODE
  Me.txtName = "Frank"
  MsgBox Hello(Me.txtName)

but the following will produce an error:
CODE
  Dim vName As Variant

  vName = "Frank"
  MsgBox Hello(vName)

and so would:
CODE
  Dim vName As Variant

  Me.txtName = "Frank"
  vName = Me.txtName
  MsgBox Hello(vName)


They won't even compile:
Compile error:
ByRef argument type mismatch

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Jan 12 2020, 01:48 PM
Post#15


UtterAccess VIP
Posts: 13,717
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

RunningTotal is an Unbound Textbox Formatted as Currency


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 12 2020, 01:50 PM
Post#16


UtterAccess Moderator
Posts: 12,613
Joined: 6-December 03
From: Telegraph Hill


Can you show the whole code of your procedure as you have it

--------------------


Regards,

David Marten
Go to the top of the page
 
FrankRuperto
post Jan 12 2020, 01:58 PM
Post#17



Posts: 787
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Interesting! So in the following comparison, "Frank" > 0 will not produce an error?

CODE
txtT = "Frank"

If [txtT] > 0 Then
      [ContractSumToDate] = [ContractSumToDate] - [txtT]
    Else
      [ContractSumToDate] = 0
    End If

This post has been edited by FrankRuperto: Jan 12 2020, 01:58 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
cheekybuddha
post Jan 12 2020, 02:09 PM
Post#18


UtterAccess Moderator
Posts: 12,613
Joined: 6-December 03
From: Telegraph Hill


Did you try it?

You will get strange results! But, yes that will work.

However, use a string variable in VBA will throw an error:
CODE
?"Frank" > 0

Error 13: Type mismatch

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Jan 12 2020, 02:33 PM
Post#19


UtterAccess VIP
Posts: 13,717
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

This is all the code on the AfterUpdate of the Combobox used to select the Record to Edit

CODE
Private Sub Combo71_AfterUpdate()
On Error GoTo Combo71_AfterUpdate_Err

    DoCmd.SearchForRecord , "", acFirst, "[CustomerPurchaseOrderID] = " & Str(Nz(Screen.ActiveControl, 0))
    
    If [OriginalContractSum] > 0 Then
    [ContractSumToDate] = [OriginalContractSum] + Nz([TotalC], 0)
    [RunningTotal] = 0
  End If
  [ContractSumToDate] = [ContractSumToDate] - [txtT]
    
Combo71_AfterUpdate_Exit:
    Exit Sub

Combo71_AfterUpdate_Err:
    MsgBox Error$
    Resume Combo71_AfterUpdate_Exit

End Sub

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 12 2020, 02:51 PM
Post#20


UtterAccess Moderator
Posts: 12,613
Joined: 6-December 03
From: Telegraph Hill


Why are you using Screen.ActiveControl?

Is it Combo71 that contains the CustomerPurchaseOrderID to be searched?

Maybe try:
CODE
Private Sub Combo71_AfterUpdate()
On Error GoTo Combo71_AfterUpdate_Err

    DoCmd.SearchForRecord , "", acFirst, "[CustomerPurchaseOrderID] = " & Nz(Me.Combo71, 0)
    
  If Nz([OriginalContractSum], 0) > 0 Then
    [ContractSumToDate] = [OriginalContractSum] + Nz([TotalC], 0)
    [RunningTotal] = 0
  End If
  [ContractSumToDate] = [ContractSumToDate] - [txtT]
    
Combo71_AfterUpdate_Exit:
    Exit Sub

Combo71_AfterUpdate_Err:
    MsgBox Error$
    Resume Combo71_AfterUpdate_Exit

End Sub

--------------------


Regards,

David Marten
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    29th March 2020 - 04:27 PM