Attach a subroutine to an unbound Text box? (1 Viewer)

foxxrunning

Member
Local time
Today, 01:36
Joined
Oct 6, 2019
Messages
109
Do I need to place that assignment in each of the If sections or only once? I placed it in each If statement and it looks wrong.
Option Compare Database
Option Explicit

Function SplitTotal(Code1 As String, Code2 As String)

If Code1 = "AL" Or Code1 = "AS" Then
TotalA = [SPLIT1] * [Fee]
SplitTotal = TotalA

If Code1 <> "AL" Or Code1 <> "AS" Then
TotalA = [Amount]
SplitTotal = TotalA

If Code2 = "AF" Then
TotalB = [Split2] * [Fee]
SpltiTotal = TotalB

If Code2 <> "AF" Then
TotalB = Null
SplitTotal = TotalB
End If
End Function
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:36
Joined
Oct 29, 2018
Messages
21,357
This is your example. I think the assignment is the Test statement.
Public Function Test() As String
Test = "It is now " & Now()
End Function

Why isn't my statement an "assignment statement". I don't understand.

TotalA = [SPLIT1] * [Fee]
Hi. Sorry if I wasn't clear on that. I tried to say it a few times, but I guess I could have said it better.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:36
Joined
Oct 29, 2018
Messages
21,357
Do I need to place that assignment in each of the If sections or only once? I placed it in each If statement and it looks wrong.
Option Compare Database
Option Explicit

Function SplitTotal(Code1 As String, Code2 As String)

If Code1 = "AL" Or Code1 = "AS" Then
TotalA = [SPLIT1] * [Fee]
SplitTotal = TotalA

If Code1 <> "AL" Or Code1 <> "AS" Then
TotalA = [Amount]
SplitTotal = TotalA

If Code2 = "AF" Then
TotalB = [Split2] * [Fee]
SpltiTotal = TotalB

If Code2 <> "AF" Then
TotalB = Null
SplitTotal = TotalB
End If
End Function
Hi. First of all, I recommend having Option Explicit at the top of your modules. This will force you to declare all your variables. The second recommendation is to also declare your data types. Third, this: If Code1<>"AL" OR Code1<>"AS" will always be True, because AL<>AS.

So, could you please describe, using plain words, what your function is supposed to do, so we can then help you translate it into code? Thanks.
 

Dreamweaver

Well-known member
Local time
Today, 08:36
Joined
Nov 28, 2005
Messages
2,466
You need to also assign a type to the return or I think it will always be a variant
Function SplitTotal(Code1 As String, Code2 As String) As String
 

foxxrunning

Member
Local time
Today, 01:36
Joined
Oct 6, 2019
Messages
109
I want the function to see what is in field Code1 which is a string, and if the letters in the Code1 field are either AL or AS then calculate an amount by multiplying the field Split1(numeric) by the field Fee(numeric) to produce a total for the unbound textbox which will have a currency format. Sorry if that wasn't clear before. Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:36
Joined
Oct 29, 2018
Messages
21,357
I want the function to see what is in field Code1 which is a string, and if the letters in the Code1 field are either AL or AS then calculate an amount by multiplying the field Split1(numeric) by the field Fee(numeric) to produce a total for the unbound textbox which will have a currency format. Sorry if that wasn't clear before. Thanks
Thanks. Sounds like you may not even need a function for it. What I was suggesting earlier would require you to create separate functions anyway, because a function, technically, can only return one value.

So, could you try this, for now?

=IIf([Code1]="AL" OR [Code1]="AS",[Split1]*[Fee],0)

PS. If [Code1] wasn't either AL or AS, what did you want the function to do? Return nothing or a maybe a zero?
 

foxxrunning

Member
Local time
Today, 01:36
Joined
Oct 6, 2019
Messages
109
If [Code1] wasn't either AL or AS I wanted a separate calculation of essentially the amount in the [Amount] field * 1 or itself
If Code1 <> "AL" Or Code1 <> "AS" Then
TotalA = [Amount]
SplitTotal = TotalA
That is why I needed the function or at least code to do the two different calculations. One of which (AL or AS) would need TWO lines in the report. One line for Split1 and another line for Split2. They are the only code which I need two lines for. Everything else only needs ONE line in the report and the textbox for it would have the value from the [Amount] field . Hope this is clear.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:36
Joined
Oct 29, 2018
Messages
21,357
If [Code1] wasn't either AL or AS I wanted a separate calculation of essentially the amount in the [Amount] field * 1 or itself
If Code1 <> "AL" Or Code1 <> "AS" Then
TotalA = [Amount]
SplitTotal = TotalA
That is why I needed the function or at least code to do the two different calculations. One of which (AL or AS) would need TWO lines in the report. One line for Split1 and another line for Split2. They are the only code which I need two lines for. Everything else only needs ONE line in the report and the textbox for it would have the value from the [Amount] field . Hope this is clear.
So, did you try the code/expression I posted earlier? Does it do anything? Okay, is Code1 is not AL or AS, then you want the result to show the [Amount], correct? If so, modify the code I posted to this one:

=IIf([Code1]="AL" Or [Code1]="AS",[Split1]*[Fee],[Amount])

Does that give you the correct results? Please let us know. Again, I think this would go a lot quicker if we have a sample copy of your db.
 

foxxrunning

Member
Local time
Today, 01:36
Joined
Oct 6, 2019
Messages
109
That worked. I really don't know how to give you a sample copy of the workspace. All I know how to do is send the complete workspace and as this is for a doctor and his patients, I am really hesitant to do it. I can try to explain the complete workspace if you want.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:36
Joined
Oct 29, 2018
Messages
21,357
That worked. I really don't know how to give you a sample copy of the workspace. All I know how to do is send the complete workspace and as this is for a doctor and his patients, I am really hesitant to do it. I can try to explain the complete workspace if you want.
That's okay. One thing you could do is replace the sensitive information (patient info, etc.) with test/dummy data. Anyway, if the above code worked, can you apply the same principle to your other textboxes? Maybe we don't need a sample file if you could get everything to work by using the above example.
 

foxxrunning

Member
Local time
Today, 01:36
Joined
Oct 6, 2019
Messages
109
Sorry we went out for a walk. Cooped up for weeks and the daily walk is now the law. I think I might be able to get this to work. Will try a similar statement in the second textbox control source. I will let you know if it doesn't work. I am really appreciative for all your help and patience. Thank you once again.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:36
Joined
Oct 29, 2018
Messages
21,357
Sorry we went out for a walk. Cooped up for weeks and the daily walk is now the law. I think I might be able to get this to work. Will try a similar statement in the second textbox control source. I will let you know if it doesn't work. I am really appreciative for all your help and patience. Thank you once again.
No worries. Good luck. Let us know how it goes.
 

foxxrunning

Member
Local time
Today, 01:36
Joined
Oct 6, 2019
Messages
109
As of tonight it is working fine. Noticed that when I tried to Sum the txtbox contents that the Sum Icon in the Detail Tab is grayed out. I think I have to change the contents of the textbox to Numeric? before the Sum icon will again light. Is there a way to change the contents of the formula you gave me to numeric? Something like
num(=IIf([Code1]="AL" Or [Code1]="AS",[Split1]*[Fee],[Amount])) I know this is wrong because I tried it. Must be some way to light the Sum Icon.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:36
Joined
Oct 29, 2018
Messages
21,357
As of tonight it is working fine. Noticed that when I tried to Sum the txtbox contents that the Sum Icon in the Detail Tab is grayed out. I think I have to change the contents of the textbox to Numeric? before the Sum icon will again light. Is there a way to change the contents of the formula you gave me to numeric? Something like
num(=IIf([Code1]="AL" Or [Code1]="AS",[Split1]*[Fee],[Amount])) I know this is wrong because I tried it. Must be some way to light the Sum Icon.
Okay, try:

=CCur(IIf(...))
 

foxxrunning

Member
Local time
Today, 01:36
Joined
Oct 6, 2019
Messages
109
Didn't work. When I open the Sigma Icon in the Design Tab, all the is available to me is the "Count Records" action, all the other actions including the "Sum" action is unavailable.
 

foxxrunning

Member
Local time
Today, 01:36
Joined
Oct 6, 2019
Messages
109
It seems as though the expression is what Access doesn't like. If I click on any of the other fields (fee, amount, split1) the Sigma Icon in the Design tab displays all of the possible Summation values including Sum, Average, Count records, Max, Min, SD, variance. But when I highlight the expression you gave me everything but Count Records is grayed out. Do you have an idea why Access if offended by the expression? I might have to find another way to do what you devised if I cannot get it to total the values produced by the expression. The CCrr did not work. I looked to see if all of the involved fields are numeric and noticed that the amount field was created as Currency and not numeric. The others are numeric. Could that be the reason? Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:36
Joined
Oct 29, 2018
Messages
21,357
It seems as though the expression is what Access doesn't like. If I click on any of the other fields (fee, amount, split1) the Sigma Icon in the Design tab displays all of the possible Summation values including Sum, Average, Count records, Max, Min, SD, variance. But when I highlight the expression you gave me everything but Count Records is grayed out. Do you have an idea why Access if offended by the expression? I might have to find another way to do what you devised if I cannot get it to total the values produced by the expression. The CCrr did not work. I looked to see if all of the involved fields are numeric and noticed that the amount field was created as Currency and not numeric. The others are numeric. Could that be the reason? Thanks
Hi. It might be time to post a sample db.
 

foxxrunning

Member
Local time
Today, 01:36
Joined
Oct 6, 2019
Messages
109
I would like to do so if you could explain what you mean. I have three tables, patients, transactions, and services. They are patients and transactions are linked one to many by a patient number. Transactions and services are also linked one to many by a service code. Would it be adequate to send you screen shots of the three tables design view of the fields in each table? What else might you need?
 

foxxrunning

Member
Local time
Today, 01:36
Joined
Oct 6, 2019
Messages
109
I think these are the three tables I use in txt format. Hope they get through properly. Is this enough.?
 

Attachments

  • Tables.txt
    61 bytes · Views: 108

foxxrunning

Member
Local time
Today, 01:36
Joined
Oct 6, 2019
Messages
109
I see the tables didn't survive changing from docx to txt so I'll try again.
 

Users who are viewing this thread

Top Bottom