Concatenation of Text and Number

[RuralGuy]

Looking good. You need Option Explicit at the top of this module and then Debug>Compile and you will find some errors. I have to go plow about a foot of snow off of the property so we can get out. I will be gone a few hours, sorry. You are getting close.

[diane802]

I am still getting the error when I put this into the report, it is asking me to define the parameters for F1, F2, etc etc...

If i enter the parameters, then it does accurate run through the If statements.

[RuralGuy]

Did you successfully compile it without any errors? Post how you are using it in your report, please.

[diane802]

Hi - thanks for looking at this .. this is what I have.. and then I added the control source and it shows up as:

=Disclosures («Valued_Accounts», «Balance_Credited», «Balance_Owed», «Valued_Accounts_Length», «Balance_Credited_Length», «Balance_Owed_Length»)

-

 
Option Explicit
Public Function Disclosures(Valued_Accounts, Balance_Credited, Balance_Owed, Valued_Accounts_Length, Balance_Credited_Length, Balance_Owed_Length) As String
If Valued_Accounts = "Yes" Then
    If Balance_Credited = "Yes" Then
        If Balance_Owed = "Yes" Then
            ' -- Yes, Yes, Yes
            Disclosures = Valued_Accounts_Length & "; " & Balance_Credited_Length & " Credited; " & Balance_Owed_Length & " Owed"
        Else
            '-- Yes, Yes, n/a
            Disclosures = Valued_Accounts_Length & "; " & Balance_Credited_Length & " Credited"
        End If
    Else
        If Balance_Owed = "Yes" Then
            '-- Yes, n/a, Yes
            Disclosures = Valued_Accounts_Length & "; " & Balance_Owed_Length & " Owed"
        Else
            '-- Yes, n/a, n/a
            Disclosures = Valued_Accounts_Length
        End If
    End If
Else
    If Balance_Credited = "Yes" Then
        If Balance_Owed = "Yes" Then
            '-- n/a, Yes, Yes
            Disclosures = Balance_Credited_Length & " Credited; " & Balance_Owed_Length & " Owed"
        Else
            '-- n/a, Yes, n/a
            Disclosures = Balance_Credited_Length & " Credited"
        End If
    Else
        If Balance_Owed = "Yes" Then
            '-- n/a, n/a, Yes
            Disclosures = Balance_Owed_Length & " Owed"
        Else
            '-- n/a, n/a, n/a
            Disclosures = "n/a"
        End If
    End If
End If
End Function

[RuralGuy]

Are these the actual names of the fields in your RecordSource of the Report? («Valued_Accounts», «Balance_Credited», «Balance_Owed», «Valued_Accounts_Length», «Balance_Credited_Length», «Balance_Owed_Length»)
What are the «...» characters and why are you using them? It is not necessary to carry the same names through to the UDF. Aliasing works just fine and may be easier to read. I assume the RecordSource of your Report is a query, right?

[diane802]

Those are the same names as the fields - but whats interesting is I don't have the «...» piece... it came through when I went to the control source and chose Disclosures from the Built-In Functions section... thats how it came through..

Yes - the record source of my report is a query.

[RuralGuy]

I prefer to use the UDF in the query rather than on a form. Could you post the SQL view of your query?

[diane802]

so sorry took me so long to respond!

here is the sql view:

 
SELECT [Accounts Main Table].*, Office.[Office Location] AS [Office Location_Office], Office.[Business Unit Name], Office.[Business Area], [Customer Area].*, [Late Fee Flat Amount USD].*, [Late Fee USD].[Late Fee USD], [Late Fee Local].[Late Fee Amount], [Late Fee Local].[Late Fee Currency]
FROM ((([Customer Area] INNER JOIN (Office INNER JOIN [Accounts Main Table] ON Office.[CCOffice Location] = [Accounts Main Table].[CC/Office]) ON [Customer Area].[CC/Customer Function] = [Accounts Main Table].[Customer Function/CC]) INNER JOIN [Late Fee Local] ON [Accounts Main Table].[Customer ID Number] = [Late Fee Local].[Customer ID Number]) INNER JOIN [Late Fee Flat Amount USD] ON [Accounts Main Table].[Customer ID Number] = [Late Fee Flat Amount USD].[Customer ID Number]) INNER JOIN [Late Fee USD] ON [Accounts Main Table].[Customer ID Number] = [Late Fee USD].[Customer ID Number];

[RuralGuy]

OK, we'll put it in the form. I'm sorry but I have to leave for several hours now. We can work on it when I get back.

[diane802]

thanks - that is the query for my report

[RuralGuy]

It is a dilly! All of the fields passed to the UDF will need to be bound to controls on your report. Those controls do not need to be visible but Access will not pull the fields unless they are bound to a control. (Don't ask!) You can give the controls more simple names that begin with txt... and then we will be able to differentiate between the control and the field. Post back with the ControlNames you pick please.

[diane802]

i'm sorry - what do you mean by control names? do you mean the table where the original field resides, or the name of the field in the table? if you mean the name of the field - they are all exactly as I told you (Valued_Accounts, Balance_Credited, Balance_Owed, Valued_Accounts_Length, Balance_Credited_Length, Balance_Owed_Length).

The table they come from is Accounts Main Table.

[RuralGuy]

Fields are in tables (and queries) and controls are how we display fields on Forms and Reports. All of the controls (TextBoxes, Labels, ComboBoxes) have their own name that defaults to the field to which they are bound or something else really informative like TextBox49, Label50 etc. I've been gone most of the day trying to find places to put snow. We have another storm due in Tuesday evening and we haven't quite completed cleaning up after the last one. My truck went to the shop again today as well. Same problem its had for the last three months for the 4th time. It is getting pretty tiring when it lets me down.

[diane802]

ahhh ok! makes sense! well the controls are basically the exact same name as I provided above - when i created the fields, i had them already named as i wanted them to be.

that does sound like a lot of snow if you have find places to put it... but better to have snow than accelerated globing warming!

[RuralGuy]

Can you prepend txt to the control names so we are sure the expression is referencing the controls?

=Disclosures(txtValued_Accounts, txtBalance_Credited, txtBalance_Owed, txtValued_Accounts_Length, txtBalance_Credited_Length, txtBalance_Owed_Length)

This should invoke the UDF in your report.