Results 1 to 9 of 9
  1. #1
    DanielHofer is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    14

    =IIf([TAXABLE],"T","F") fails in Expression Builder

    Win7 64bit, Access 2010 32bit



    Expression Builder problem with setting Control Source in a report field to
    =IIf([TAXABLE],"T","F") => all values are "T"
    or with
    =IIf([TAXABLE]=-1,"T","F") => all values are #Type!

    Both formulas work ok in creating a field in an access query
    TAX: IIf([TAXABLE],"T","F")
    TAX: IIf([TAXABLE]=-1,"T","F")

    The table is a linked MySQL table with the MySQL TAXABLE field type as TinyInt(1) [-1 = True, 0 = False].

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    I think you have to be specific with this type of one.

    =IIf([TAXABLE]]<> 0, "T", "F")

    I use <> 0 because I've had it not work before on -1 even though it should.

  3. #3
    DanielHofer is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    14

    =IIf([TAXABLE]<>0,"T","F")

    I tried =IIf([TAXABLE]<>0,"T","F") but the result is #Type! on each record.

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Do you have any nulls in that field?

  5. #5
    DanielHofer is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    14

    Same problem in local table with Number or Text field

    I created a local table with 4 fields:
    ID AutoNumber
    TorF Yes/No with Format True/False
    Num Number with Field Size Integer Fixed 0 Decimals
    StringNum Text Field Size 2

    Put 2 records in:
    1 True -1 "-1"
    2 False 0 "0"

    I used =IIf([Num]<>0,"T","F") on the Num field and
    =IIf([StringNum]<>"0","T","F") on the Text field and
    =IIf([TorF],"T","F") on the Yes/No field
    as the Control Source in the report field.

    The Num and StringNum field give #Type! as a result in the report.
    The Control Source expression on the Yes/No field works ok.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Is it possible that the MySQL TinyInt is not being considered a Boolean by Access?
    Perhaps it is being seen as a Number data type????

  7. #7
    DanielHofer is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    14

    MySQL not the problem

    The local table of the previous Post was created in Access 2010.
    In all type cases, MySQL or Access, the query version of the expression works.

  8. #8
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    The control source version would need to be referring to the control name that is bound to the field and, when using a formula, it has to be named something other than the field name.

  9. #9
    DanielHofer is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    14

    Smile Success, Thankyou!

    I changed the textbox names to something other than the field names and the above formulas all work.

    Thankyou.

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

Similar Threads

  1. Replies: 16
    Last Post: 07-22-2011, 09:23 AM
  2. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM
  3. Replies: 7
    Last Post: 01-23-2011, 12:32 PM
  4. Replies: 3
    Last Post: 04-10-2010, 10:22 AM
  5. aSTR = Dir("C:\*.*") >> "Type Mismatch"
    By JGrant in forum Programming
    Replies: 1
    Last Post: 03-28-2009, 05:17 AM

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