How to concatenate multiple values in text box

I'm trying to code a control source for a text box to concatenate and
handle various possible values. There are three fields I need to
handle:

For example, if the values for the three fields are ...

Person_Relation = "Parent"
Person_Relation_Other = <NULL>
Person_Relation_Type = "Biological"

.... my text box should show:

    Parent :Biological;

(The ":" and ";" will be replaced by "(" and ")" but for now it's
easier to use something other than parentheses for testing.)

If instead the values for the three fields are:

Person_Relation = "Other"
Person_Relation_Other = "Neighbor"
Person_Relation_Type = <NULL>

.... my text box should show

   Neighbor

This is what I have so far:

=IIf(IsNull([Person_Relation]),"",[Person_Relation] & (" :"+
[Person_Relation_Type] & ";"))

If the person is a Biological Parent, my current code correctly shows:

Parent :Biological;

But of the person is an Other: Neighbor, my current code shows:

Other;

So, if Person_Relation = "Other" I need to figure out how to:

1. Omit Person_Relation & Person_Relation_Type in the string
2. Show the value for Person_Relation_Other
3. Omit the trailing ";"

Any ideas? Been tweaking and tweaking to no avail. Thank you.
0
HeislerKurt
11/15/2007 7:51:22 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
1707 Views

Similar Articles

[PageSpeed] 24

Shouldn't  you be checking whether Person_Relation_Type is Null, not 
Person_Relation?

Try:

=IIf(IsNull([Person_Relation_Type]),[Person_Relation_Other],[Person_Relation] 
& (" :"+[Person_Relation_Type] & ";"))

Alternatively, you could base it on what's in Person_Relation using:

=IIf(Nz([Person_Relation], "Other") = "Other", [Person_Relation_Other], 
[Person_Relation] & (" :"+[Person_Relation_Type] & ";"))

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


<HeislerKurt@gmail.com> wrote in message 
news:8927c6c1-5c8b-4429-b25a-1a13316f9d13@l1g2000hsa.googlegroups.com...
> I'm trying to code a control source for a text box to concatenate and
> handle various possible values. There are three fields I need to
> handle:
>
> For example, if the values for the three fields are ...
>
> Person_Relation = "Parent"
> Person_Relation_Other = <NULL>
> Person_Relation_Type = "Biological"
>
> ... my text box should show:
>
>    Parent :Biological;
>
> (The ":" and ";" will be replaced by "(" and ")" but for now it's
> easier to use something other than parentheses for testing.)
>
> If instead the values for the three fields are:
>
> Person_Relation = "Other"
> Person_Relation_Other = "Neighbor"
> Person_Relation_Type = <NULL>
>
> ... my text box should show
>
>   Neighbor
>
> This is what I have so far:
>
> =IIf(IsNull([Person_Relation]),"",[Person_Relation] & (" :"+
> [Person_Relation_Type] & ";"))
>
> If the person is a Biological Parent, my current code correctly shows:
>
> Parent :Biological;
>
> But of the person is an Other: Neighbor, my current code shows:
>
> Other;
>
> So, if Person_Relation = "Other" I need to figure out how to:
>
> 1. Omit Person_Relation & Person_Relation_Type in the string
> 2. Show the value for Person_Relation_Other
> 3. Omit the trailing ";"
>
> Any ideas? Been tweaking and tweaking to no avail. Thank you. 


0
Douglas
11/15/2007 8:07:14 PM
> Alternatively, you could base it on what's in Person_Relation using:
>
> =IIf(Nz([Person_Relation], "Other") = "Other", [Person_Relation_Other],
> [Person_Relation] & (" :"+[Person_Relation_Type] & ";"))

I went with this option and it works great. Thank you.
0
HeislerKurt
11/15/2007 9:23:48 PM
On Nov 15, 4:23 pm, HeislerK...@gmail.com wrote:
> > Alternatively, you could base it on what's in Person_Relation using:
>
> > =IIf(Nz([Person_Relation], "Other") = "Other", [Person_Relation_Other],
> > [Person_Relation] & (" :"+[Person_Relation_Type] & ";"))
>
> I went with this option and it works great. Thank you.

Actually ... just noticed that if Person_Relation has a value (e.g.,
Parent), but Person_Relation_Type is null, the last semicolon appears
as in:

   Parent;

In this case, it should be hidden. I can't seem to figure out the fix.
0
HeislerKurt
11/19/2007 11:10:34 PM
Reply:

Similar Artilces:

Color box unavailable on Patterns tab in "format data series"
I have a user who is working with some charts and can not edit the color box (is grayed out) on the patterns tab in “format data series”. We feel it is a user setting or something because a different user can pull in the same spreadsheet and edit the color of a graph bar just fine… Has anyone encountered this? Any suggestions? ...

What is best way to determine whether a value exists in a column of values?
Excel 2002 Hi What is the best way to lookup a value (i.e. a number) to see whether or not it is in another column of values? I need exact matches only I cant get lookup() to work... With thanks Ship Shiperton Henethe Strangely enough I have now discovered that the function =countif() seems to work quite well! Ship Shiperton Henethe ...

re-post
Using Access 2007. If I have one instance of Access already opened to database#1, then attempt to open another Access db (database#2), I get "Permission Denied". This happens whether I try to Shell out to it, or try using Windows Explorer to open it. Now the twist! If I start db#1, then try to start db#3, it works! There's something about db#2 that makes the Shell command believe that opening it isn't allowed. Now the second twist!! After clicking through the "Permission Denied" error msg, db#2 STILL OPENS! It doesn't do what it's suppose...

how do I copy several lines of word text into one excel cell?
In a word doc I need to copy several lines of text and paste them into one excel cell. I can't seem to find any help telling me how to do this (each line pastes into a different cell) You can either double click the cell or press F2 key to get into edit mode for the cell, then the text pasted would all go into the cell. -Simon "jhh" wrote: > In a word doc I need to copy several lines of text and paste them into one > excel cell. I can't seem to find any help telling me how to do this (each > line pastes into a different cell) thank you SO much Simon CC - ...

domain with multiple subnet
Hi all, I've have 5 offices (let's say 5 sites) that are connected each other in a VPN, each site has its own subnet: 10.0.0.0/24 10.0.1.0/24 .... 10.0.5.0/24 today there is no active directory structure and I want to implement the first one. I want one only domain, handled by two domain controllers in the main office (the only with a NOC). Is there any issues with the different subnets? On 17/03/2010 15:55, Andrea Caldarone wrote: > I've have 5 offices (let's say 5 sites) that are connected each other in > a VPN, each site has its own subnet: ...

text box filter by form
For some reason that I just can't explain, a text box on a form associated with a single primary key field behaves unexpectedly when I do a filter-by-form. Instead of listing all of the records from the source table as choices in combo box mode, I only see "Is Null" and "Is Not Null" as choices. I have an older form from another database that behaves as I hoped this one would, yet there seems to be no property differences between the two text boxes. Can anybody explain what I'm missing please? Having "Is Null" and "Is Not Null" as filt...

Combo box to control two sub forms
I have a combo box on a form that I want to control the data viewed in two sub forms. The combo box list months in the mmm format. One of the sub forms is requests that have been approved with the date (mmm-yy) the other sub form is requests that have been denied with the date (mmm-yy). When I select Jan from the combo box, I want the sub forms to display only the records having to do with that month. Is this even possible? Your combobox provides only a month criteria while your data is both year and month. That's not a problem except when you select a month, your subform d...

Text to Columns 05-28-10
I have a column with the following data: A RUT 212874790014 Each cell has the word RUT, and the number changes, altough it always has 12 digits. I need to have the word in one cell and the number in another, so I use the text to column option. But when I finish doing this the result is: A B RUT 21,2875E+11 How can I avoid this, so as to keep displaying the whole original number in the cell? Thanks in advance. Regards, Emece.- After performing the text to columns, format the number column as 'number' zero decimal points. "Emece&quo...

text recognition difference b/w commercial and non-commercial?
I have OneNotes 2007 installed at work on Windows 7 and text recognition works. I have it installed at home (non-commercial) on Windows 7 and I do not get text recognition. Both are SP2. But they look different. Does text recognition not work in the non-commercial version? This is the only thing that make OneNote worthwhile as a tool for me. MarkC wrote: >I have OneNotes 2007 installed at work on Windows 7 and text > recognition works. I have it installed at home (non-commercial) on > Windows 7 and I do not get text recognition. Both are SP2. But they > look d...

Single Exchange server with multiple domains
Hello, My company was just acquired and the new owners are asking that I merge our two Exchange servers into a single server. We are running two domains. Is it possible for a user in one Windows domain to access an Exchange server in another domain? Jeff <Jeff@discussions.microsoft.com> wrote: > Hello, > > My company was just acquired and the new owners are asking that I > merge our two Exchange servers into a single server. We are running > two domains. Is it possible for a user in one Windows domain to > access an Exchange server in another domain? Sure, if you...

Combine multiple workbooks into one workbook
I would like to combine a number of workbooks inside a folder into one workbook and maybe have the worksheet name be the name of the file or something. The core issue is combining them. I have looked and I see how to reverse it but not this exact thing, please help. ...

Display Color Hex Values in a DropDownList
How can I populate a DropDownList with the Available Color Hex Values while displaying the Color? I'd like to allow my users a way to set a color profile. Thanks Hello, The problem being exactly ? Is this to get the hex values or to display a color for each item or to enumerate available colors ? You also have ColorDialog (http://msdn.microsoft.com/en-us/library/system.windows.forms.colordialog.aspx)... -- Patrice "Dave" <Dave.Burkett@Jacobs.com> a �crit dans le message de news:f8e3e0b5-1b54-49b4-85db-079e8ea7d2bf@19g2000yqu.googlegroups.com... ...

converting to UNICODE, _TCHAR and TCHAR, writing text files
Hi, i'm converting a MFC application to support unicode. I have some (probably noob) questions: - What is the difference between the types _TCHAR and TCHAR ? I see some UNICODE applications use _TCHAR and others TCHAR. - I have to convert the way textfiles are written. These text-files are send to machines using parallel port, so they have to stay the same as before (when my application had no _UNICODE preprocessor definition). The commands that are used to write these files are fputs, fopen, etc. When changing it to support wide characters are the textfiles still the same? example (_U...

Setting Value Of One Cell Equal To Value Of Selected Cell
Without using VBA - If cell A1=red, A2=white and A3=blue, can I set C1 to be the value of the selected cell so that if A1 is selected, C1 will equal red, if A2 is selected, C1 will equal white, etc.? Thanks, Sheldon Potolsky Sheldon, In a word, no. No worksheet thing comes to mind that works as a function of the active cell. You might want to use some radio buttons or a list box (Forms Toobar or Control Toolbox) instead of cell selection. They'll let you click to make such choices, and you can use formulas that will change a cell value as you wish. -- Regards from Virginia Beach, ...

Promt before changing a value
I was asked the below question by someone and was wondering if you guys have any advise I can relay to him. "I am looking to create something, most likely with VB code, in excel that will pop up a dialog box to warn the user that they are changing the quantity of a part by a more than normal amount. For example, if the current inventory quantity for a specific insert is 10,000, then if that cell is changed by more than 3000, a dialog box will appear to prompt the user to double check their change. So, if that cell is changed to 13,000 or higher, or, 7,000 or lower, the dialog box will ...

In excel can you select certain cells which contain the same text
If I have several cells with the same text in them can I filter these out and select them. I know you can do this for formula etc but can it be done for text? try data>filter>autofilter -- Don Guillett SalesAid Software donaldb@281.com "ade" <ade@discussions.microsoft.com> wrote in message news:937AD9E1-668B-4E32-B194-29146DF0A60A@microsoft.com... > If I have several cells with the same text in them can I filter these out and > select them. I know you can do this for formula etc but can it be done for > text? ...

Excel Cell Format for Numberic Values
When I export data having 20 numeric characters, Excel will put in place a scientifc equation. The numbers are rounded off after 15 characters. Cannot get Excel to display all 20 numeric numbers. Any ideas on how to get Excel to read all 20 numbers in a cell? Hi not possible. Excel only supports 15 significant digits -- Regards Frank Kabel Frankfurt, Germany "Al" <Al@discussions.microsoft.com> schrieb im Newsbeitrag news:4324528D-BBAB-493C-A1B0-EC108BCB6571@microsoft.com... > When I export data having 20 numeric characters, Excel will put in place a > scientifc e...

Restore pick list values
We edited the invoice 'status reason' picklist ( actualy deleted some values ) and modified others. This we have done on other picklists with no problems. However we have noticed that when this attribute is displayed on selection lists the text displayed is not the same as that on the picklist. We now presume there is code behind particular list values and want to restore the old values with the same list value. We have a backup of the DB available through enterprise manager and can see the missing records there in STRINGMAP and STRINGMAPBIT . We are missing values 4,5, 6 and a...

reset the value of a spin button
Q: How can I reset the value of a spin button with out setting off the CHANGE event? I am using Office Pro 2003 and have tried the following with out success: Application.EnableEvents = False spnButton1.Value = VarX Application.EnableEvents = True Option Explicit Private fNoChange As Boolean 'other code Private Sub spnButton1_Change() If Not fNoChange Then 'change code End If End Sub 'more code fNoChange = True spnButton1.Value = VarX fNoChange = False -- HTH Bob "cubbybear3" <n4213l@gmail.com> wrote ...

Count values either vba or formula
Sample of what I am trying to achieve: Worksheet1: (column A, B, C, D, E) - source data id,primary region, secondary region, tertiary region, status 111,americas,new york, NA, increase 111,americas,canada,NA, increase 111,americas,mexico,LATAM,increase 111,americas,peru,LATAM,decrease 112,europe,france,WEST,increase 112,europe,spain,WEST,decrease 112,europe,uk,WEST,decrease 112,europe,portugal,WEST,decrease etc.... Worksheet2: (contains summary sheet) - summarize data 111,americas,NA, increase, 2 <----- count instances found from worksheet1 111,americas,LATAM,increase, 1 111,americas,LA...

Dynamic Range in a List Box with a blank choice?
Ok... so far so good !! 8-) ... I'm happy! I have a named dynamic range similar to this. =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) .... and (by golly) it shows up in my list when I do Data>Validation>allow list on another sheet! But how do I provide a blank? What I want to do is if a referenced cell is blank (no input from the user) then the list shows a blank? A little bit about what I'm headed for: I am trying to develop a time sheet that will track: time spent running total time what task I spent the time on (this is where my list box comes in) then on another ...

automatically change text case on entry
I use the following code to automatically change the case of a range of cells to uppercase in Excel, which works fine. However I need some other cells in the same sheet to automatically change to Proper Case. Is there a way of adapting the following code to enable upper and proper case? Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Range("H1,M5,O11,F7")) Is Nothing Then With Target .Value = UCase(.Value) End With End If ws_exit: Application.En...

select items in a list box with a macro?
Instead of a mouse, can you select items in a list box with a macro? You can use a line like this: Me.ListBox1.ListIndex = 0 The first item in the listbox is item 0. If you allow multiple selections, then this worked ok for me: Option Explicit Private Sub CommandButton1_Click() Dim iCtr As Long With Me.ListBox1 For iCtr = 0 To .ListCount - 1 If iCtr = 0 _ Or iCtr = 3 _ Or iCtr = 5 Then .Selected(iCtr) = True Else .Selected(iCtr) = False End If Next i...

Converting text to uppercase
This is Excel 2000. Cell C2 in a worksheet called 'NOTES' contains '34 West Street, somewhere-in-England' =UPPER ( NOTES!C2 ) is supposed to produce ' 34 WEST STREET, SOMEWHERE-IN-ENGLAND' In fact it gives: #NAME? What is going wrong? Have you spelt UPPER( correctly? There is no space before the open bracket. Hope this helps. Pete On Apr 24, 3:53=A0pm, Alan Secker <a...@asandco.co.uk> wrote: > This is Excel 2000. Cell C2 in a worksheet called 'NOTES' > > contains '34 West Street, somewhere-in-England' > > =3DUPPER ( NOTES!...

Track Changes in 2007 deleting large portions of text
We have a document where 3 team members are making updates in revision marks. We are not updating at the same time. For some reason, huge chunks of text are getting deleted that were never actually deleted by any of us. It has happened to all 3 of us after we worked in the document. It is now the third or fourth time that I have had to go back and reject the changes for the sections deleted, and yes it does show my name or one of the other uses in the name of the person who made the changes. I can go in, reject all of the deleted sections, save the document, get out and get b...