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?
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?
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.
Uhhh...Drop down is not a FieldType. Is it just a control on a form or is it also a field in a table?
Apologies, it is a field in the table, where the values are limited to n/a and Yes. but it is defaulted to null.
Oh boy, are you using Lookup Fields in your tables?
http://www.mvps.org/access/lookupfields.htm
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.
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?
Yes - text!
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"?
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
Here's the basic structure of the UDF. You will need to complete the 'Disclosures = lines and post back with the completed procedure.
Code: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
i am plugging away at entering this in.. once I have this created..
How do I then show this in my report/form?
how do i specify what table the F1 etc fields are stored?
Do I type like Table1.F1 ?
sorry...
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.
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..
Code: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