Concatenation of Text and Number

[RuralGuy]

What are the FieldTypes of the F1, F2, F3, F1A, F2A, F3A fields? Are they all Text fields (strings)? Can they be empty or Null?

[diane802]

F1, F2, and F3 is a drop-down where it says "n/a" or "Yes". It can be null.

F1A, F2A, and F3A are free form text fields that can be empty. The only time they should be filled out if is the above says Yes. I actually have it so that it is locked if the above does not say Yes. So it would then be null.

[RuralGuy]

Uhhh...Drop down is not a FieldType. Is it just a control on a form or is it also a field in a table?

[diane802]

Apologies, it is a field in the table, where the values are limited to n/a and Yes. but it is defaulted to null.

[RuralGuy]

Oh boy, are you using Lookup Fields in your tables?
http://www.mvps.org/access/lookupfields.htm

[diane802]

no i am not

i just marked it in the conditions to have a combo list in the text box, and i designated those two values as what can be typed in.

[RuralGuy]

So your form has a ComboBox control with a Value list bound to your field in the table. When you open the table in design mode what is the FieldType for that field? Text?

[diane802]

Yes - text!

[RuralGuy]

I'm going to have to think about this some more. I thought a Select Case structure would work but it is awkward. It is possible that all three (F1, F2, F3) can be "n/a", "Yes" or Null right and Null is the same as "n/a"?

[diane802]

yes but you know what - i can have it just default to n/a - so that way Null is not an option.

and yes, all three can be n/a, or Yes, or some Yes and some n/a

[RuralGuy]

Here's the basic structure of the UDF. You will need to complete the 'Disclosures = lines and post back with the completed procedure.

Public Function Disclosures(F1, F2, F3, F1A, F2A, F3A) As String

    If F1 = "Yes" Then
        If F2 = "Yes" Then
            If F3 = "Yes" Then
                '-- Yes, Yes, Yes
                'Disclosures =
            Else
                '-- Yes, Yes, n/a
                'Disclosures =
            End If
        Else
            If F3 = "Yes" Then
                '-- Yes, n/a, Yes
                'Disclosures =
            Else
                '-- Yes, n/a, n/a
                'Disclosures =
            End If
        End If
    Else
        If F2 = "Yes" Then
            If F3 = "Yes" Then
                '-- n/a, Yes, Yes
                'Disclosures =
            Else
                '-- n/a, Yes, n/a
                'Disclosures =
            End If
        Else
            If F3 = "Yes" Then
                '-- n/a, n/a, Yes
                'Disclosures =
            Else
                '-- n/a, n/a, n/a
                'Disclosures =
            End If
        End If
    End If
End Function

[diane802]

i am plugging away at entering this in.. once I have this created..

How do I then show this in my report/form?

[diane802]

how do i specify what table the F1 etc fields are stored?

Do I type like Table1.F1 ?

sorry...

[RuralGuy]

You should just copy and paste the code I supplied in your new module. You do not specify the table here. It will be taken care of by the routine that invokes the function. Just complete all of the RED lines and remove the ' at the beginning of the line. Then post back with what you have so we can see it. Use the code tags (the # on the advanced toolbar) just as I did to preserve the indenting.

[diane802]

How does it look?

I entered this all in, then I created a text field in my report, placing the control to be this field. but when I try to run, it asks me for the parameter for all the fields..

 
Option Compare Database
Public Function Disclosures(F1, F2, F3, FA1, FA2, FA3) As String
If F1 = "Yes" Then
    If F2 = "Yes" Then
        If F3 = "Yes" Then
            ' -- Yes, Yes, Yes
            Disclosures = FA1 & "; " & FA2 & " Credited; " & FA3 & " Owed"
        Else
            '-- Yes, Yes, n/a
            Disclosures = FA1 & "; " & FA2 & " Credited"
        End If
    Else
        If F3 = "Yes" Then
            '-- Yes, n/a, Yes
            Disclosures = FA1 & "; " & FA3 & " Owed"
        Else
            '-- Yes, n/a, n/a
            Disclosures = FA1
        End If
    End If
Else
    If F2 = "Yes" Then
        If F3 = "Yes" Then
            '-- n/a, Yes, Yes
            Disclosures = FA2 & " Credited; " & FA3 & " Owed"
        Else
            '-- n/a, Yes, n/a
            Disclosures = FA2 & " Credited"
        End If
    Else
        If F3 = "Yes" Then
            '-- n/a, n/a, Yes
            Disclosures = FA3 & " Owed"
        Else
            '-- n/a, n/a, n/a
            Disclosures = n / a
        End If
    End If
End If
End Function