IIF Query?

Hi - 

I need to run a query for a mail merge and need to have a "addressee" field.
My table has two name fields, "first" name and "second" name.

If there is a "second" name listed, I want to use that name, 
but if there is not a "second" name listed, I want to use the "first" name 
listed.

This is what I wrote , but it doesn't return anything.  I think I am doing 
something wrong. 

addressee: IIf([second_name]=Null,[first_name],IIf([second_name]=Not 
Null,[second_name]," ")) 


Thanks, 

BS
0
Utf
10/10/2007 2:47:00 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
309 Views

Similar Articles

[PageSpeed] 37

On Oct 9, 8:47 pm, bluesky <blue...@discussions.microsoft.com> wrote:
> Hi -
>
> I need to run a query for a mail merge and need to have a "addressee" field.
> My table has two name fields, "first" name and "second" name.
>
> If there is a "second" name listed, I want to use that name,
> but if there is not a "second" name listed, I want to use the "first" name
> listed.
>
> This is what I wrote , but it doesn't return anything.  I think I am doing
> something wrong.
>
> addressee: IIf([second_name]=Null,[first_name],IIf([second_name]=Not
> Null,[second_name]," "))
>
> Thanks,
>
> BS

=iif(Not IsNull([SecondName],[SecondName],[FirstName])

0
pietlinden
10/10/2007 2:55:15 AM
Try:

IIf(Len(Trim([second_name] & "")) = 0, [first_name], [second_name])

-- 
HTH
Van T. Dinh
MVP (Access)



"bluesky" <bluesky@discussions.microsoft.com> wrote in message 
news:DDFEF44B-374A-4599-9C2D-9B52B104DE87@microsoft.com...
> Hi -
>
> I need to run a query for a mail merge and need to have a "addressee" 
> field.
> My table has two name fields, "first" name and "second" name.
>
> If there is a "second" name listed, I want to use that name,
> but if there is not a "second" name listed, I want to use the "first" name
> listed.
>
> This is what I wrote , but it doesn't return anything.  I think I am doing
> something wrong.
>
> addressee: IIf([second_name]=Null,[first_name],IIf([second_name]=Not
> Null,[second_name]," "))
>
>
> Thanks,
>
> BS 


0
Van
10/10/2007 3:08:35 AM
On Tue, 9 Oct 2007 19:47:00 -0700, bluesky <bluesky@discussions.microsoft.com>
wrote:

>If there is a "second" name listed, I want to use that name, 
>but if there is not a "second" name listed, I want to use the "first" name 
>listed.

NZ([SecondName], [FirstName])


             John W. Vinson [MVP]
0
John
10/10/2007 6:20:27 AM
Reply:

Similar Artilces:

Query Joins
When capturing data from another database owned by another group, I need all of the data for 120 days from tableA and all of the data that matches by keyID from tableB. However, when I try to pull in the data from a single make Table query, I only get data from TableA that has data in TableB. Is this because I do not have the proper join? Yes. You probably have an inner join which requires data in both tables before it will return a record. In query design view, double click on the join line between the two tables until you see a dialog box. Try the second option which is a le...

IIF Statement syntax?
I have a timesheet template that allows a user to enter their Time In, Time out for Lunch, Time back In, Time Out for the day, then calculates the time differences to give total number of hours worked. I am trying to make it so that if the total time for the day is Negative, it assigns zero as the default. Here is the statement that I tried IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, ROUND((((E7-E6)+(E4-E3))*24),2),0 Can anyone give me some help on what I've done wrong? I just get #NAME in the box Thanks for the help in advance Mac Could it be that IFF should be IF? Bernard "m...

Cross tab query solution
I have table Named OutStationTab having the data as below field EName - Text Field OsDate - Date Field OsAmt - Numeric field I want to make a cross tab query in which RowHeading- EName Column Heading- Expr1: Format([OsDate],"dd-mm-yyyy") Value- OsAmt Note the data is entered from 15th Date of a month to 14th date of next month. One Employee take 1 outstation charges only in a day. I am able to make the crosstab query and its working perfect. Like I filter the query (From 15-02-2010 to 14-03-2010), then made the CrossTab query. It works perfect. It does n...

iIF clause
Can you help me build the right IIf field in the query with the following conditions : The field DDU consissts of : DDU :[exworks]*2+0,4+0,01 To the above expression i must also add 0,001 if size = 205, etc according to the following table : 205 0.001 60 0.001 20 0.009 1 1.32 4 0,32 0,5 1,67 However Acces does not accept my query, obvioulsy i have errors : DDU : [exworks]*2+0,4+0,01 + IIf([size = 205],0,001,[size = 60],001) Will you help me ? ...

Querying Recipient Policies from script
Any idea how to construct an ldap query to extrapolate all the email addresses policies out of the recipient policies. So i I'm not totally sure what you're trying to accomplish, but we have a VBS script here that dumps the recipient policies to a text file for us. If you email me directly (remove the online from my address) I'll be happy to send it to you. Thanks, Richard Roddy Microsoft Exchange Support This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- >Thread-Topic: Querying Recipient Policies from script >...

is this query really too complex?
hello I am getting the error message that this is too complex! Can this be right? A limit on the number of IIFs? Is there another way of doing this? RenewalCost: IIF([1118] and [type]="a",100, IIF([1118] and [type]="c",60, IIF([1118] and [type]="d",130, IIF([1116] and [type]="a",85, IIF([1116] and [type]="c",50, IIF([1116] and [type]="d",100, IIF([1618] and [type]="a",85, IIF([1618] and [type]="c",50, IIF([1618] and [type]="d",100, IIF([1118plus] and [type]="a",100, IIF([1118plus] and [type]...

Update Query Question 06-19-07
This is what I want to do. In the PO_TBL I have a field PO_ITEM_NBR which combines the data of the PO_ITEM and PO_NBR fields. I want to parse into the actual fields of PO_ITEM and PO_NBR which will exist in the same table. Current Table Result PO_ITEM_NBR PO_ITEM PO_NBR 123344 001 123344 001 123344 002 123344 002 123455 001 123455 001 etc. I have created a updte query with the following SQL statememnt: UPDATE PO_TBL SET PO_ITEM = Left(PO_ITEM_NBR,InStr(PO_ITEM_NBR,&...

query builder Error 05-16-07
we are very new to CRM and I have our fields customized but when I go to preview the page it says ERROR QUERY BUILDER ERROR NO ATTRIBUTEs. Does anyone know what this means or how to fix it. Hi Sarah, Have you published the customizations? HTH, Niths "Sarah" wrote: > we are very new to CRM and I have our fields customized but when I go to > preview the page it says ERROR QUERY BUILDER ERROR NO ATTRIBUTEs. Does > anyone know what this means or how to fix it. We have gone to Actions Publish and we aren't sure if they are actually published because we can't ...

querying dhcp
Question 1 Is there a way to manage DHCP server with powershell or .net? Or is NETSH the only way? Question 2 if netsh is the only way then how would I pass it an IP address and then find out if a reservation with that IP exists. Question 3 if there is a native .net or powershell way could you point me in the right direction? Thanks I don't have time to look into all of your questions, but first off... Yes, the only way to manage DHCP from any command line today is via NETSH. Marco "Jacob Sampson" <JacobSampson@discussions.microsoft.com> wrote in ...

query probelms
I am a new access user and haveing problems joining tables into a query. table A is a list of engineering spec numbers and ES_id table B a list of details pertaining to the ES (namem rev, etc) table C is a list of procedures numbers pertaining to a ES. This table can have from 0 to 10 precedures per ES. Table D is a list of details pertaining to the procedures (name, rev etc) Table E is a list of drawing numbers pertaining to a ES. This table can have from 0 to 10 drawings per ES. Table F is a list os details pertainging to each drawing. I have no problems linking table A and B becasue it is...

Best Way To Query The Following Information
I have a form and a database in MS Access 2003 that was developed by a programmer over 4 years ago. The database was created through unconventional methods so one table holds a majority of the fields. The form is used to input help desk tickets now, but management wants to start querying different types of information from it. I was tasked to create a section on the form that indicates if a technician group has either "passed", "failed" or is in a "failed but resubmitted" for a certain ticket. I have a checkbox to indicate each state. There are a series of for...

Stumped with LIKE query
I have a MS Access database (from Office XP) to catalog my movies in. Here's my dilemma... I have Shrek 1 an 2. From within Access, how can I query the names so Shrek and Shrek 2 are both returned? I either get one or nothing. Here's what I have tried (among other things): SELECT Table1.MovieName, Table1.MovieYear, Table1.Category, Table1.Rating, Table1.Plot FROM Table1 WHERE Table1.MovieName Like [Enter Movie Name]; That returns only "Shrek" and not "Shrek 2". ------------------------------------------------------------------------------ ----- SELECT Table1.M...

Passing a value from subform or main form to subform query
I am using MS ACCESS 2003 I have a main form and a subform that pulls up an existing case for the user to update the information already entered. The subform is designed and opens up in form view not in datasheet view. The subform knows which case to pull in based on the case number on the main form The textboxes on the main form are bound fields from a query. The user enters an ID which is how the main form query knows which record to pull and it works. The textboxes on the subform are bound with data from a different query (so I have a query for each form) The que...

XML query
Hi I am new to using XML, and have a bit of an issue. How do I define how the XML file looks when I export a query from Access? On Sep 12, 4:50 pm, rmorri...@davislangdon.com.au wrote: > Hi I am new to using XML, and have a bit of an issue. How do I define > how the XML file looks when I export a query from Access? XML is a markup language: http://www.w3.org/TR/2006/REC-xml11-20060816/#sec-origin-goals That means, it is a plain text file, it uses tags. When you export a query from Access 2003 to XML, several files with different purpose can be generated. 1. A file with .xml exten...

IIF(AND) statement
Hi I have a number of things I need to test in the query. I need to use IIF statement with AND (for example in Excel you can do it),. Example I need this IIf (and(cat="HS",cat2="JTK",cat3="NT"),"1","2") how to do it in access? -- Greatly appreciated Eva On 7 apr, 21:09, Eva <E...@discussions.microsoft.com> wrote: > Hi > I have a number of things I need to test in the query. I need to use IIF > statement with AND (for example in Excel you can do it),. > Example > I need this > IIf (and(cat="H...

Hidden files in Ms-Query cause ODBC connect errors or Query is wac
I have a Query fetch_from_bob that moves data (collapses records,unique key search, etc...) from excel spreadsheet "bob" to excel spreadsheet "fred". Fred and bob live in the same directory" c:\123directory" on the same computer. I allow this query to execute/refresh data automatically when one of the query paramaters is changed. This works great. I also have a macro that invokes the query to refresh the data whenever the user desires. This works great as well. The macro literally has all the VB code to execute the query. But I'm just a part time progra...

What Does +IIF Do as Opposed to IIF?
What Does +IIF Do as Opposed to IIF?James Igoehttp://code.comparative-advantage.com/ AFAIK, there's no such function as +IIf in VBA. If I had to guess, I'd say that the + is simply arithmetic: add the results of the IIf function to whatever preceeded it.How are you seeing it used?-- Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)<james.igoe@gmail.com> wrote in message news:1173285991.823665.303480@p10g2000cwp.googlegroups.com...>> What Does +IIF Do as Opposed to IIF?>> James Igoe> http://code.comparative-advantage.com/> On 7 Mar 200...

Excel printing query
How do you print an excel worksheet with row numbers? <File> <PageSetUp> <Sheet> tab, And *check* "Row & Column Headings". -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Linda" <Linda@discussions.microsoft.com> wrote in message news:808D8884-3E22-4018-90EC-932C90EC8570@microsoft.com... > How do you print an excel worksheet with row numbers? File | ...

Select/Append Query Malfunction
I have an append query that takes a parameter and two combo box selections from frmEmpSetHours. One of the combo box selections is Shift (1, 2, or 3). The resultant dynaset is appended to tblEmployeeProduction. Shift 1 and 2 have Shift.ShiftHours of "8". Shift 3 has Shift.ShiftHours of "7.5". The problem is that the dynaset returned/appended after inputting selections for 3rd shift return a Shift.ShiftHours value of 8 instead of 7.5. I have tried building the sql from scratch one line at a time and have been successful returning the appropriate Shift.ShiftHours until I ...

Complex Query?
I'm looking for an easier way of setting up a query in Access. I know how to do the following in Excel, but I don't know how to do it in Access and I am trying to imrpove my Access skills. Basically, if I have a list of data as such: Job001 C034 Job002 D010 Job002 D011 Job002 D010 Job003 C039 Job004 B009 Job005 P045 Job006 C047 Job006 C046 Job007 P033 Job008 C034 Job008 C008 Job009 C015 Job009 D016 Job009 C071 Job010 JW0027 Job011 BP1320 Job011 BP1320 Job011 BP1320 Job012 CB2260 Job013 EH0720 Job014 BP1350 Job014 DI0470 Job015 CF1430 Job016 BF1220 Job017 EI0110 Job018 PZ0810 Job01...

IIF question
Hello. Doing a report that uses an IIF in a textbox that is text. If the IIF is false I would like the text (mainly 1 or 2 words) not to be underlined. How do I end the IIF so the text is not underlined? Thanks. John I am not sure how you mean, but if you want this underlined based on the true or false then use the properties box and choose the text decoration item and write an expression in there. "JohnE" <JohnE@discussions.microsoft.com> wrote in message news:5C6EEFBC-0DC0-41C0-A35D-3A01209E568A@microsoft.com... > Hello. Doing a report that uses an IIF...

Select value in query
I have the following sample field & data in one table MyValue Value1 Value2 Value3 100 20 30 90 200 null 210 150 My goals here is to divide myvalue with either value1 or value2 or value3 whichever is higher. e.g in line one 100/90 and 200/210 in line two. how to create the expression in my query thanks On Thu, 4 Feb 2010 05:26:01 -0800, zyus <zyus@discussions.microsoft.com> wrote: select MyValue/ iif(v1 > v2, iif(v1 > v3, v1, v3),iif(v2>v3,v2,v3)) Because you have nulls, you may ...

append query and duplicates
I have a two part question. Access 97. I want to know first if I can append from a Excel file that is a .csv, or do I have to convert it first to a .xls? I know how to append from a .xls file. The .csv file (before conversion to .xls) is downloaded from another machine. each time the the file is downloaded (every month), it contain the total data, which will consist of hourly readings. Each download has the previous month's data, with the present month's data added to end of file. There is no set limit to number of months, that is, at present, my download for December 07...

query results
I have a query that pulls data from 2 tables all works fine if there is data in both tables. Unfortunatly the possibility exsits that one table might not have any data therefor not exist for that customer and those results do not show in my form where the query displays. I do not know sql very good so am having a problem getting results to show when the table is missing -- Message posted via http://www.accessmonster.com In the query design window double click on the line between the two tables until a dialog box pops up. Try the second option. If that doesn't work, try the third opt...

help with nested iif statement
I have a table and I want to create a query that has a new field " Type of Shipment" base on a field "Item Category" If the item category field equals ZDIR, ZTRN ...etc up to 23 different item categories the "Type of Shipment field will equal DIRECT else I would like it to return "Stock" Any help will be greatly appreciated. Thanks Hi, for the iif statement. What you can do is: IIf(category = 'ZDIR' OR category = 'ZTRN' OR .............,'DIRECT','') Having a long nested if is a headache. What i can suggest ...