Results 1 to 9 of 9
  1. #1
    focosi is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    97

    iif statement with duplicate name fields

    I have a textbox in a report using as control source :

    =IIf("[Recipients]![Sex]=F";", born";", born on")

    (actually the difference is in Italian declinations of born according to sex).
    Despite Access doesn't return any error, the output of the report is invariably ", born" (i.e. all recipients seems female)....
    I suspect there is something wrong in my syntax (commas, quotes, apostrophes, ..)...but I can't figure out what... I have to include table name since I have duplicate field names in 2 tables ([Recipients] and [Donor]) : I understand this is suboptimal but I can't correct at this stage....


    Any hint?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Try:
    =IIf("[Recipients]![Sex]=F", "born", "born on")
    or try:
    =IIf("[Recipients]![Sex]='F'", "born", "born on")
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    The syntax of your IIF is wrong - you have the quotes in the wrong place. Try this:

    =IIf([Recipients]![Sex]= "F"; ", born";", born on")

    I am assuming you have set the semicolon ( ; ) as a delimiter - I have never seen that - over here I would use

    =IIf([Recipients]![Sex]= "F", ", born" , ", born on")

    HTH

    John
    Last edited by John_G; 08-13-2012 at 07:49 AM. Reason: remove smiley!

  4. #4
    focosi is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    97
    Quote Originally Posted by Bob Fitz View Post
    Try:
    =IIf("[Recipients]![Sex]=F", "born", "born on")
    understands as always like "F", hence always returns "born"

    Quote Originally Posted by Bob Fitz View Post
    or try:
    =IIf("[Recipients]![Sex]='F'", "born", "born on")
    same as above.... any hint?

  5. #5
    focosi is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    97
    Quote Originally Posted by John_G View Post
    Hi -

    The syntax of your IIF is wrong - you have the quotes in the wrong place. Try this:

    =IIf([Recipients]![Sex]= "F"; ", born";", born on")
    You are right according to Microsoft (http://office.microsoft.com/en-us/ac...001228853.aspx), but this is a special case since, due to duplicate fields, I have to refer to the mother table.... If I remove quotes from the [mother table]![field] part of the function the field value returns #ERROR..... Any hint?

    Quote Originally Posted by John_G View Post
    I am assuming you have set the semicolon ( ; ) as a delimiter - I have never seen that - over here I would use

    =IIf([Recipients]![Sex]= "F", ", born" , ", born on")

    HTH

    John
    I'm running Access 2010 with Italian language pack : that's why I need ( ; ) instead of ( , ) as a delimiter... It's a well-known issue...

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Try:
    =IIf(Forms![Recipients]![Sex]= "F", "; born" , "; born on")
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    focosi is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    97
    Quote Originally Posted by Bob Fitz View Post
    Try:
    =IIf(Forms![Recipients]![Sex]= "F", "; born" , "; born on")
    Actually [Recipients] is a table.
    If I write

    =IIf(Tables![Recipients]![Sex]= "F", "; born" , "; born on")

    ...it is automatically corrected to ... :

    =IIf([Tables]![Recipients]![Sex]= "F", "; born" , "; born on")

    ... and then after running the report it asks for value of parameter "Tables". Any hint ?

    Daniele

  8. #8
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    You can't refer to tables in an IIF expression like that - Access has no way of knowing which record you want to look at. For you purposes, you need to use the DLookup function, something like this:

    =iif(Dlookup("Sex","Recipients",Condition) = "F"; ", born"; ", born on")

    The important part is condition, where you have to supply information to tell the DLookup function which record to look at in the Recipients table, and to do that you need to have a field (control) on your report which identifies a recipient.

    Suppose the control on the report is called Person_ID, and the ID field in the Recipients table is called Recipient_ID. The condition would then be: "Recipient_ID = " & me!Person_ID

    and the complete iif would then be:

    =iif(Dlookup("Sex","Recipients","Recipient_ID = " & me!Person_ID) = "F"; ", born"; ", born on")

    John

  9. #9
    focosi is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    97
    Quote Originally Posted by John_G View Post
    Hi -

    You can't refer to tables in an IIF expression like that - Access has no way of knowing which record you want to look at. For you purposes, you need to use the DLookup function, something like this:

    =iif(Dlookup("Sex","Recipients",Condition) = "F"; ", born"; ", born on")

    The important part is condition, where you have to supply information to tell the DLookup function which record to look at in the Recipients table, and to do that you need to have a field (control) on your report which identifies a recipient.

    Suppose the control on the report is called Person_ID, and the ID field in the Recipients table is called Recipient_ID. The condition would then be: "Recipient_ID = " & me!Person_ID

    and the complete iif would then be:

    =iif(Dlookup("Sex","Recipients","Recipient_ID = " & me!Person_ID) = "F"; ", born"; ", born on")

    John
    You're the man!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-23-2012, 05:07 PM
  2. Replies: 1
    Last Post: 11-27-2011, 11:37 PM
  3. Replies: 1
    Last Post: 04-15-2011, 04:04 AM
  4. Duplicate data fields
    By watergal82 in forum Access
    Replies: 1
    Last Post: 09-07-2010, 02:48 PM
  5. Prevent Duplicate Values on Combination of Two Fields
    By LornaM in forum Database Design
    Replies: 8
    Last Post: 05-05-2009, 11:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums