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
1690 Views

Similar Articles

[PageSpeed] 7

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:

Rules Based On Numeric Field Values
Hi, I sometimes get emails which have special standardized notifications in them. Any "special attention" areas are in a consistent format which will also contain numbers that are normally 0. If they aren't 0, then there is some kind of problem. So, the number values can be from 0 to any value. Is there a way to "flag" these in a rule? Ideally, I'd like to change the message color of or priority based on the values seen. Below is partial output from one of the emails with example fields high-lighted: Successes : 10 Failures : 0 <==== Aborted : ...

How do I group text to graphic?
In my document I'm trying to embed a graphic into the text. How do I do this to where when I delete text before the graphic it moves with text instead of me having to redo embedding? BeanieBrain <BeanieBrain@discussions.microsoft.com> was very recently heard to utter: > In my document I'm trying to embed a graphic into the text. How do I > do this to where when I delete text before the graphic it moves with > text instead of me having to redo embedding? Which version of Publisher are you using? -- Ed Bennett - MVP Microsoft Publisher i'm using both 2000 and...

Tick boxs on a packing list
I am creating a report that shows products along with a subform showing optional extras for each product. On the subform i have drawn a box so it gives me the options when printed and packing the order to tick them off as boxed. This work fine when i have optional extra but when i have no optional extras in the subform i just end up with a box on the screen which i do not want. How do i set it up so it does not have box when i have no optinal extra So long as there is nothing else in that same horizontal space, you can turn on the CanShrink property and it will disappear when empty. HTH --...

Non3rd Party: Multiple Taxes (i.e. 2 Diff States)
I posted this a few days ago, and had some great response, however, I am looking for a non 3rd party solution to this issue. Does anyone have any ideas? RMS V1.3flat I am trying to establish two different Item tax structures for two different tax authorities, let's say one for MN and one for ND state. Preface: I am giving this as a real HL example. Assigning taxes on a per customer basis will not work as a solution to this issue. Please do not take it literally that I need to assign per customer based on Billing or Shipping State. Example: MN Tax has: MN State, MN City and MN C...

Percentage calculation on text columns
I have a sheet that represents exam candidates, their grades over 3 assignments with an average score, in the final COLUMN there is an indication of whether they have passed or failed in the format "Pass" "Fail". I need a formula that will count the overall group and give a percentage of the group that has failed. Can anyone help? -- Connor ------------------------------------------------------------------------ Connor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27970 View this thread: http://www.excelforum.com/showthread.php?threadid=4...

How can I type more than one line of text into a cell in Excel?
I am using Excel to organize contact information, names, addresses, phone numbers, etc. I would like all of this information in one cell and in the standard format of: name address phone etc. I do not know how to make Excel accept more than one line of text, unless I cut and paste it from a Word document. Could someone please tell me how to format the cells so that more than one line of text can be accepted in a cell? Hi use ALT+ENTER for inserting line breaks -- Regards Frank Kabel Frankfurt, Germany "watermark" <watermark@discussions.microsoft.com> schrieb im Newsb...

Carriage Return in a Concatenation
I have a serveral lines of VB that I need to Concatate, I have them in seperate columns b/c a few aspects will change. Currently my Concatenation looks like =H2&I2&J2&K2, I need a carriage return between &, I can get this result by putting each row colum on a seperate row, but that is not really feasiable, I know that there has to be a better way, any suggestions? =H2&CHAR(10)&I2&CHAR(10)&J2&CHAR(10)&K2 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) <eyeman6513_2000@yahoo.com> wrote in message news:11...

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...

Catching the value returned by ImportWizard
Is it possible to capture the values returned by the import wizard. In particular, the filename chosen and the talbe named given to the imported data? Thanks Marykay Hi, Marykay. > Is it possible to capture the values returned by the import wizard. Sorry. Neither the Import Text Wizard nor the Import Spreadsheet Wizard return any values for your procedure to capture. > In particular, the filename chosen and the talbe named given to the > imported data? If you want to get the new table name, create a VBA procedure that checks the current system time, then calls the Import T...

open multiple workbooks at startup
Ever since i ran a macro that opened several workbooks, my Excel always opens the same workbooks. I have searched and deleted all the files that are being opened. Search shows they are in a sub-folder "Application Data", but this folder is not listed in explorer and the files that are opened are not found with a file search. another thing, the open files in Excel options is cleared to blank. How do I stop opening these files when I open another file? Dear Ron, you can find the folder application data if you switch on the view hidden files option in windows it will be in C:...

building an external link based on a cell value
I would like to build an external link in a spreadsheet that automatically changes based on a cell value. For example, I have the following link: =SUMIF('[Location Income Statements 2004.xls]Mar04'! $14:$14,P5,'[Location Income Statements 2004.xls]Mar04'! $18:$18) Instead of Mar04 in the formula above, I would like to use a cell's contents.... cell A8 for example. Is this possible? Thanks, Jason Jason You can use the INDIRECT function. =SUMIF(INDIRECT("'[Location Income Statements 2004.xls]" & A8 & "'!$14:$14"),P5... Indirect ...

Excel starting multiple instances of IE
Hi, How do I prevent excel starting a new instance of internet explorer whenever I click on a hyperlink in a spreadsheet. I have a spreadsheet with 3000+ rows. Each row has a cell with a hyperlink, so the instances of IE can build up very quickly. The IE/Advanced option "Reuse windows for launching shortcuts" is ticked, but does not appear to have any effect. Paul that feature would only work on a shortcut already displayed in IE Only workaround is to convert the spreadsheet into Word then Save As html then open that file in IE. That would work... "Paul Hodges" ...

Assign number value to a field with text in it?
Is this possible to do? I want a cell to have a number assigned to it, but display text in the cell, so I can use the cell in a mathmatical formula. I'm using Excel 2003 to track how many seats I have left in each conference room. I want the name of a room to equal a number. For example: I want the text "Executive Room A" to equal 105 Does anyone know a way to do this with out putting them in different fields. -Sharon --- Message posted from http://www.ExcelForum.com/ You may want to use Help to learn about VLOOKUP. You can make a table on say Sheet2 with the room names a...

Duplicate value
I have a form with a subform on it. The subform has 5 fields, set up as datasheet view: ID (autonumber) Start End Qty (sum of end-start) Line Type (combo box) I've been asked to see if the 'End' can automatically be entered at the 'Start' on the next line; however, there is upon occassion the need to change the start number. For example: ID Start End Qty Line 1 0 215 215 (whatever) 1 215 300 85 (whatever) 1 325 ...

DLOOKUP FOR CALCULATED VALUES
Need help with the following code: ID = numerical pdate = is the date field in my query. =dlookup("[Earned Hr]"/("[tpaid]"-("[project]"+"[Station]"+"[Break]"), "productionqueryrpt","[dd]=#" & [pdate]& "# And [id]=" & [id])) Please help!!!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 "DOMIRICAN via AccessMonster.com" <u38512@uwe> wrote in news:7a97fc0c97606@uwe: > Need help with the following code: > ID = numeric...

Combo Box, list order
I am using a Combo Box to select and display names from a table. The names in the table are in alphabetic order and to begin with so was the Combo Box. I have since added new names to the list, the Table list remains in alphabetic order but the Combo Box is all over the place. Can anyone suggest a cure for this. Many Thanks Fritz Open the form in design view. Right-click the combo, and choose Properties. On the Data tab, examine the Row Source property. Make it a query that sorts the records the way you want. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access u...

Schema to validate a Value Element of Type 'DateTime'
Hi, I've been trying to write some schema to validate the Value element of an Eq element but to no avail. If the Value Element is off type DateTime then the node should be able to contain text (the actual date string) or the <Today /> element. I can write schema to validate either one off the options seperately but cannot write it in a way which allows either one off the options. Hope that makes since, any help would be greatly appreciated. I've included some sample XML of what I'm trying to validate below. Cheers, Paul <And> <Eq> <FieldRef Nam...

text box as label to appear when subreport does
I have a text box used as a label with the control source: ="Attributes" +IIf(IsNull([FillTemp_Label]),Null,"") I'd like to add a subreport to this: srptFGProcessingPacketPouchAtts. Can this be done? Thanks! -- www.Marzetti.com What do you want to do? -- Duane Hookom Microsoft Access MVP "JohnLute" wrote: > I have a text box used as a label with the control source: > ="Attributes" +IIf(IsNull([FillTemp_Label]),Null,"") > > I'd like to add a subreport to this: srptFGProcessingPacketPouchAtts. > > Can this be...

How to insert superscripted value in Y-axis title of excel sheet
I tried to write suprscripted variable in y-title of the excel chart (located in work sheet). It gave the superscript at the extreme top, text direction 90 degrees, (not with the text desired). Where am I going wrong in putting the superscript through format axis? Moreover I could not re-size the text boxes. Please advice and help me out. To superscript a character in a run of text, click so that the cursor is blinking in the text, then select the character and press Ctrl+1 (numeral one) to open the Format dialog. Format the character as superscripted. Axis titles, chart titles, and da...

missing all texts from mail messages
For some reason all text from all of my mail messages are missing. The only way I can read my mail messages is by saving them as *.txt files. I have relatively new XP version (got a new computer few weeks ago) with Outlook=20 Express 6. The mail messages were readable at first,=20 but the problem occurred after automatic windows updates=20 last week and I believe this has caused the problem. How=20 could I fix this? Automatic updates on my computer around the time the mail=20 message problem appeared:=20 Windows Media Player 9 Series (KB837272)=20 Windows XP -security update(KB840315)=20...

How do i set a max value for a Edit control
Hi I would likie to know how to set a max value for an Edit Control...sey Age = 100 Create the textbox with ES_NUMBER style, which will allow only numbers to be entered. Handle the EN_CHANGE message, and check for the limit. You may have to trap WM_PASTE to avoid pasting of non-neumeric data. Cheers Jagadeesh "CodeMan" <anonymous@discussions.microsoft.com> wrote in message news:B9611ACA-9A94-4B79-A2BE-7EA92B387EFC@microsoft.com... > Hi > I would likie to know how to set a max value for an Edit Control...sey Age = 100 ; > > Hello you set the variable min a...

Series including text possible?
Is there a better way to create a "series" that is based on characters as well as digits than what I'm doing below? I created 3 columns. I put the text in one, the digits in the second that I could apply the series to, with the 3rd column holding a ":". But when I paste into the text file, I have tabs separating the 3 columsn where I was hoping for no space. So this here - NAQAHDAH 01 : NAQAHDAH 02 : NAQAHDAH 03 : should paste like this - NAQAHD6H01: NAQAHDAH02: NAQAHDAH03: NAQAHDAH04: NAQAHDAH05: NAQAHDAH06: NAQAHDAH07: NAQAHDAH08: NAQAHDAH09 Is there perhaps a w...

ADO recordset command text vs command
Hi all, I've noticed a slight difference between the way recordset command text works vs commands themselves where SP calls are concerned. We have many calls to SP which also include input and output parameters. When examining a SQL Server trace, for example, the difference is, that we can specify command text to an ADO recordset, which includes input parameter names. However, the ADO command object does not appear to specify any parameter names, although it does support return of output parameters. Is there a way for the ADO command object to also specify the parameter names? This would ...

Combo Boxes #4
Can someone please tell me how I lock a combo box to a cell? Right-click on the control and click Format control, then select tab Properties. HTH, Nikos "RF" <anonymous@discussions.microsoft.com> wrote in message news:9ca301c3eac7$6e665ea0$a401280a@phx.gbl... > Can someone please tell me how I lock a combo box to a > cell? ...

Customize Combo box Colum Heads?
Is there a way to customize the column headings in a combo/list box? The current column headings are short and confusing and I'd like to give them better names. For example, current Column Name is ADD1. I'd like to use "Address" as the column heading. Thanks. Use a query as the RowSource, and use alias names for the fields: SELECT ADD1 AS Address, NameX AS EmpName, ID AS EmpID FROM YourTableName; The alias names will appear in the combo box's header row. -- Ken Snell <MS ACCESS MVP> "Mcrawford" <Mcrawford@discussions.microsoft.com&g...