Text box with iif and is null statements

I need to have a text box that combines a couple of fields and text.  My 
problem is that if the field is blank I need it not to include some of the 
text.

The text box is 
Dear Alias or FirstName and SpouseName,

What I have is
="Dear " & (IIf(IsNull([Alias]),StrConv([FirstName],3),StrConv([Alias],3))) 
& (IIf(IsNull([SpouseName]),StrConv([SpouseName],3))) & ","

Problem is that if there is a spousename it isn't showing.  If I add in the 
"and" I still don't get the spouse name but I get the and.  
I have tried so many combinations, my head is spinning

Help!!
0
Utf
12/2/2009 8:21:01 PM
access.reports 4434 articles. 0 followers. Follow

2 Replies
1032 Views

Similar Articles

[PageSpeed] 36

srctr,

Your statement says, if there's nothing in SpouseName, show me that...

IIf(IsNull([SpouseName]),StrConv([SpouseName],3))

You need to say...

IIf(Not IsNull([SpouseName]),StrConv([SpouseName],3))

OR forget the IsNull altogether, actually the whole IIf statement as there 
is no IIf not this then that...

StrConv([SpouseName],3)

OR ust in case SpouseName is Null

="Dear " & IIf(IsNull([Alias]),StrConv([FirstName],3),StrConv([Alias],3))
& Trim(StrConv([SpouseName],3)) & ","

-- 
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"srctr" <srctr@discussions.microsoft.com> wrote in message 
news:37A2F4F0-8364-4002-BD7E-AB49859FDA10@microsoft.com...
>I need to have a text box that combines a couple of fields and text.  My
> problem is that if the field is blank I need it not to include some of the
> text.
>
> The text box is
> Dear Alias or FirstName and SpouseName,
>
> What I have is
> ="Dear " & 
> (IIf(IsNull([Alias]),StrConv([FirstName],3),StrConv([Alias],3)))
> & (IIf(IsNull([SpouseName]),StrConv([SpouseName],3))) & ","
>
> Problem is that if there is a spousename it isn't showing.  If I add in 
> the
> "and" I still don't get the spouse name but I get the and.
> I have tried so many combinations, my head is spinning
>
> Help!! 


0
Gina
12/2/2009 8:41:11 PM
Just realized I forgot the " and "...  After the first ampersand place... 
" and " &

-- 
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"srctr" <srctr@discussions.microsoft.com> wrote in message 
news:37A2F4F0-8364-4002-BD7E-AB49859FDA10@microsoft.com...
>I need to have a text box that combines a couple of fields and text.  My
> problem is that if the field is blank I need it not to include some of the
> text.
>
> The text box is
> Dear Alias or FirstName and SpouseName,
>
> What I have is
> ="Dear " & 
> (IIf(IsNull([Alias]),StrConv([FirstName],3),StrConv([Alias],3)))
> & (IIf(IsNull([SpouseName]),StrConv([SpouseName],3))) & ","
>
> Problem is that if there is a spousename it isn't showing.  If I add in 
> the
> "and" I still don't get the spouse name but I get the and.
> I have tried so many combinations, my head is spinning
>
> Help!! 


0
Gina
12/2/2009 8:44:29 PM
Reply:

Similar Artilces:

Filling drop down box
hi, I have a drop down box in a cell. Based on a value of anohter cell I have to fill my drop down box. what i mean is, say if GreeNPackage is "No" then I want the drop down box to fill a range of values. But if the GreeNPackage is "Yes" then I want the drop down box to fill another set of values. how can i do this? plenty of thanks See reply in .Functions Biff >-----Original Message----- >hi, > >I have a drop down box in a cell. Based on a value of >anohter cell I have to fill my drop down box. what i mean >is, say if >GreeNPackage is ...

data from a modeless dialog box to its parent
I need to post a text data from a modeless dialog box to its parent which is itself a dialog based application. Since it should be a large text data a simple Windows message won't be good. What is the right way to do that? Thanks Gil If you are sure the data will stay current you can post the address of the data (as a pointer) in a windows message to the parent. If you want to be really sure use SendMessage() which will wait until the parent gets the message before returning. You could call a function or copy the data to a variable in the parent dialog, but that could be dang...

Mail merge recipient dialog box
Why are the "edit" and "refresh" buttons at the bottom of the mail merge recipient dialog box grayed out? They were available in the last Word version but I cannot figure how to access them in 7 to add to or correct my mailing list. In Word 2007, the first thing you have to do is select the name of the data source in the box at the bottom left of the dialog box. Then, depending on the type of data source, the Edit and Refresh buttons may be ungreyed. [FWIW the same dialog is used by MS Publisher, where they allow multiple data sources, requiring you to ...

STATEMENT DOWNLOAD PROBLEMS #2
Hi, I am trying to download statements and keep coming up with an error which says that "A communication error has occurred. Please try or call again." I have tried deleting and recreating the OFX file extension but this hasn't helped. Does anyone have any ideas? Thanks Caz In microsoft.public.money, Cazzy123 wrote: >I am trying to download statements and keep coming up with an error which >says that "A communication error has occurred. Please try or call again." I >have tried deleting and recreating the OFX file extension but this hasn't >he...

Save a Copy/Overwrite changes dialog box
All of a sudden, this dialog box pops up every time I want to save something in Excel 2002. The box says, "The file '<file name>' may have been changed by another user since you last saved it. In that case, what do you want to do? __ Save a Copy __ Overwrite changes even though I'm the only user that's changed anything in the past 3 years. The shared workbook command has not been changed at all, so that can't be the problem. The settings are the same as the ones I have at home for personal spreadsheets that don't have this dialog box. Help! Than...

SOP Statements
Hello, We are using GP v 9.0. I have just recently taken on the task of sending out customer statements. I find that the User Definable Form has a great layout that I would like to send to our customers. However, our company name and address does not print on the form even though the option is marked when selecting the statement. Any suggestions on how to get the company information to print on the SOP Statement - User Definable Form? Thank you, Kristy If you're talking about the statements printed using Tools>Routines>Sales>Statements, then you'll need to modify t...

combo box list updating
Hi - I would like my combo box list to change base on the value of A1. That is, I have its input range being B1:F1 - "Year 1", " Year 2" etc. Example: -If cell A1 is the value "10" then the combo box default list item would be "Year 1" (which is cell "B1") -If cell A1 is the value "20" then the combo box default list item would be "Year 2" (which is cell "C1") ETC... Thanks for your help - Jim A ...

"Find a contact box" problem
Hi. I�m having problems with the �Find a contact� box on the Standard toolbar. Previously, I would type in a name and the search would pull up name/s from the Outlook Contacts folder first then, if the name is not in the Contacts folder, it would pull up name/s from the Global Address List if any. Now, when I type in a name, the search only pulls up name/s from the Global Address List. The search does not include the Contacts folder although there are entries in the Contacts folder. The problem started after I transferred a couple of Contacts subfolders to another pst file. How do I fix the p...

Counting Texts
If cell A1 contains a paragraph of texts and if I want to count just letter "W"s (Upper or lower case), How can I achieve this? Example: A1 contains "How now brown cow" the formula should return "4". Thank you. Write a macro and use the VBA functions Instr(strName, "w") together with Split(strName, "w") and count the number of times that it finds "w" or "W" Chris "Keith" <Keith@discussions.microsoft.com> wrote in message news:CC60AA13-B569-4C55-BDC7-1B6295CDAFB9@microsoft.com... > If cell A1 co...

need help with list box?
Hi I have a huge table of about 20000 records. how can I restrict entry for a few columns. I am new to excel programming. For example. I have a list of colors (red, green, blue) that I want to go under the Color column. how can I allow the user to click on a pulldown or list box similar to ms access for each cell. thanks in advance. Hi you could use 'Data - Validation'. See: http://www.contextures.com/xlDataVal01.html >-----Original Message----- >Hi > >I have a huge table of about 20000 records. how can I restrict entry for a >few columns. >I am new to exc...

Check box question 04-23-07
I have a form with a check box used to indicate if a receipt is voided or not. What I'm trying to do is go to a new record after the checkbox is marked. I have the following code in place and it works. Kinda. The problem is that if I then go back and UNCHECK the check box, it sends me to a new record again. Here's the code: Private Sub Check43_AfterUpdate() If Ckeck43 = Yes Then DoCmd.GoToRecord , , acNewRec Thanks for any help! End Sub The following works fine for me. If Me.Check3 = True Then DoCmd.GoToRecord , , acNewRec so try If Ckeck43 = True Then DoCmd.GoToRecor...

Emails missing text
We have about 80 users on our Exchange server. We have 2 users that have an issue with reading their email. It appears that when they open the email the right side of the email/text is gone. When I shadow their session I can see the entire email but they cannot. It is as if they right section of the text of the email was deleted. I'm sorry I'm not explaining this well. Any feedback is appreciated. Thanks ...

how do i change or remove a signiture that is under outo text hea.
Someone please help i am using outlook 2003 and i am triyng to change a signiture that is under auto text. to get into it i open a message push insert then outo text then signiture. I want to change it. I have closed the untilted message gone to tools then options then mail format then signitures then remove all the signitures but the signiture under insert and outo text is still there See if this helps: http://office.microsoft.com/en-us/word/HP051860291033.aspx -- Bill R "nearly bald with frustration" <nearly bald with frustration@discussions.microsoft.com> wrote i...

Export excel file to semicolon delimited text file
Hello all. We have the need to export a file from excel to a semicolon delimited text file. Is there a way to do this from Excel directly? If not, does anyone have any other suggestions? TIA for your help. If you change your regional settings to use the semicolon as your list separator, you can save as .csv and it should work. But this may affect other programs, too--since it's a windows setting. windows start button|settings|control panel|regional and language options| regional options tab|customize button near the bottom. On the other hand, if you don't want to fiddle with...

Some contacts don't show on drop down box when addressing email
I was using Outlook Express, my computer motherboard died, got new computer with Windows 7, trying to learn Outlook 2003. The computer tech from my husband's office put Outlook 2003 on the new computer and somehow transferred the addresses that I had in Outlook Express. I do not know what method he used to get the addresses into Outlook 2003. Some things carried over just fine, some things didn't, i.e. a group list of my subdivision property owners, so I had to re-make that distribution list. When I want to email my daughter, her name or email address is not s...

text size in reading pane
Where is the font selection / text size control for the Outlook 2003 reading pane (incoming emails)? I just bought a brand new dell with windows xp and all and I can't even increase the text size of incoming emails as I could with older versions of Outlook Express. I have been trying to figure this out for around 15 hours now and have come up empty. What kind of progress is this anyway???? A program rendered unable to accomplish such a simple practical thing like that!! Im very upset. My vision is poor. Unless I get an answer soon, I am considering a return of this unit to dell...

can't see text in any office files anymore
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel on my macbookpro: can't see text in any office files anymore (word, powerpoint, excel). only when using preview function from OSX. <br> what's wrong??? <br> do I have to re-install the all suite? any quick fix? ...

Entire Col Null?
Is it possible to detect if every cell in an entire column is null? -- syuhas ------------------------------------------------------------------------ syuhas's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27486 View this thread: http://www.excelforum.com/showthread.php?threadid=470009 If, by NULL, you mean blank (no data, no text, no formulas, no nothing) try this: =COUNTIF(A:A,"") will return 65536 if it is truly all blank. O course, use the letter of the column you want to verify. Is this what you are looking for -- swatsp0 --------------...

Complicated IF statement
Afternoon everyone. Have been struggling to get a formula together for the following. I am being told due to its complexity that I will probably need to break it down into 2 sections but hoping someone can help me simplify things. I need to do the following A1 = 1 (although this can be 1,2,3,4,5) (This is my variable I am struggling with) B1 = €10000 (TARGET) C1 - €6500 (ACTUAL) Then I have a few boxes showing the following <70% between 70-99% >100% A5 = 1 B5 = 10% C5 = 15% D5 = 18% A6 = 2 ...

Conditional Statements and Time Format
Hello, I have a spreadsheet where I need to calculate a range of time in a cell and display a value in another cell. Example is in cell F2 I have a time displayed of 15:34, and in the calculation window it displays as 3:34:00 PM. In Cell F3 I want to display one of three things, "1st shift", "2nd shift" or "3rd shift". Is it possible to use the conditional statements to give me the value of "1st shift" when cell F2 is between >= 07:00 and < 15:00? I can't find anything that speaks to getting thee range information from time. Thanks, Brian ...

Help Creating A Formula To Copy and Paste Text
Hi everyone. Thanks in advance for any help you may have. I need to create a formula that searches for a specific word in a column then cut the word and paste it to another column. Of course this would be simple if it was the only word in the column, but there is other text that I would like to remain in the orginal column.. Thanks again, Mary You have a reply at your other thread. Mary wrote: > > Hi everyone. Thanks in advance for any help you may have. > > I need to create a formula that searches for a specific word in a > column then cut the word and paste it to ...

Rich Text formatting in Access 2007
In Access 2007, is it possible to add rich text formatting to text in a memo field (or mixed formatting within any text field). I have just upgraded to the new version of Access, and it appears that this feature still has not been added. Is this correct? (If so, why?) If it is not possible to format individual words within a field in Access, , is there a simple, free add-in that will enable Access to do this kind of basic formatting, such as adding italic, bold, and underlining? Thanks very much for any information you may have. Bob Rich Text format for Memos in: Tables - Look in t...

Footnote text cannot be entered without mouse click
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Normally adding a footnote number automatically adds that number to the bottom of the page. A blinking text entry indicator normally allows me to enter the footnote text without any additional step. <br><br>However, today I wanted to add a footnote number after a word, and although the corresponding number correctly appears at the bottom of the page, and the blinking text entry indicator also appears ready to allow entering footnote text, yet when I try to type, nothing is entered. I must resort to ...

Add numbers accross columns after stripping away text
I have the following data in a spreadsheet: A1 B1 C1 D1 E1 F1 4.5f 6f 3.5f 3f 7.25f I need to be able to add the numbers together to give me 24.25, i.e. strip the fs away. The numbers will always be less than 10 and the there will only ever be .25 or.5 or .75 after the number (I don't know whether that is significant?). If anyone can show me how to do this I would be very grateful. I have been messing around with MID and FIND to no avail and then started thinking that SUMPRODUCT might have to get involved but it all got a bit m...

Shortcut into Name box
Hi there. Using E02 on XP. I'm using the Name box (above A1) and wonder if there isn't a keystroke to 'jump' into it quickly? Thanks in advance for any help! Depends on what you use it for. If you quickly want to jump to a named range, you can also use F5. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Bonnie" <Hambrick@discussions.microsoft.com> wrote in message news:454a01c520c2$f4137fb0$a401280a@phx.gbl... > Hi there. Using E02 on XP. I'm using the Name box (above > A1) and wonder if there isn't a keystroke to 'jump' into ...