Results 1 to 8 of 8
  1. #1
    topazfae is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    4

    #Type! Error - when I tried to use IIF(IsNull)

    I am trying to string three fields together, I think I am missing something. I have three fields that I need to pull from. The first field and third field are required (First_Name and Surname), but we also have a field, called Maiden. So some of rows don't have Maiden filled in.

    So I came to this syntnax -

    =IIf(IsNull([Maiden]),[First_Name] & " " & [Surname],[First_Name] & " " & [Maiden] & " " & [Surname])

    Basically - If Maiden is null, then the result should be First_Name Surname, otherwise, First_Name Maiden Surname



    Why does it kept giving me an error - #Type!

    Any help would be greatly appreciated.

    Thanks

    Shannon

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Seems to work fine for me.
    Where are you placing this calculation?
    Are you sure that you have all the field names correct and haven't made any typos?

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Worked for me too.

    This also works

    =[First_Name] & " " & IIf(IsNull([Maiden]),"",[Maiden] & " ") & [surname]

  4. #4
    topazfae is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    4
    GRRR - I had it in report on Control source i think its called. I used the expresion window and I just used its field to make it to appear.

    Can I send you the file so you can check it out?

    Thank you.

    Shannon

  5. #5
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you click the "Go Advanced" reply button, you can see that you can attach files.
    I cannot download files from my current location, but would be able to look at it tonight (if no one else does before then).

  6. #6
    topazfae is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    4

    Attachment of the database with #Type!

    ForForum.mdb

    I had to remove some fields due to privacy, but I left firstname maiden and lastname as well as a report on sisters.

    You may get a dialog about email and comment - ignore them since I delete them from the table.

    Let me know what I did wrong.

    Thank you,

    Shannon

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Report in Design view - see the little green triangle in the upper left corner of the textbox? This means something wrong with the expression. Select the textbox. Now see dropdown box with ! character in a diamond? Click the dropdown box.

    The error is due to circular reference. The textbox and field used in expression have the same name. Change the textbox name to something like tbxName.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    topazfae is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    4
    Sigh - I knew it was something so simple. Thanks - yeah, I need to get it in a practice - renaming those textboxes. I felt so stupid Many thanks

    Thank you.

    Shannon

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

Similar Threads

  1. #type! error
    By Sonyatwillis in forum Reports
    Replies: 1
    Last Post: 10-18-2013, 12:47 PM
  2. Replies: 7
    Last Post: 07-24-2013, 02:01 PM
  3. Error: Run-time error '13' Type mismatch
    By uronmapu in forum Access
    Replies: 1
    Last Post: 09-07-2012, 05:38 AM
  4. Replies: 1
    Last Post: 05-11-2012, 10:59 AM
  5. Error 13 Type Mismatch error
    By GlennBurg in forum Programming
    Replies: 1
    Last Post: 06-21-2011, 03:05 AM

Tags for this Thread

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