Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2014
    Posts
    2

    Unanswered: The expression you entered has a function containing the wrong number of arguments.

    Hello~
    Our agency still uses Access 2000 for our database. And I am trying to create a field with the following information but it keeps saying the expression you entered has a function containing the wrong number of arguments. Can anyone help me figure out where it is wrong?

    HUD: IIf(Int([SAIntakeDate]-[DOB])/365)<18 and
    IIf([TraumaCodes] Like D
    Or [TraumaCodes] Like E
    Or [TraumaCodes] Like F
    Or [TraumaCodes] Like P
    Or [TraumaCodes] Like S
    Or [TraumaCodes] Like W
    Or [TraumaCodes] Like X),
    C,Null) +
    IIf(Int([SAIntakeDate]-[DOB])/365)>=62 ,E,Null) +
    IIf([Disabilities] is not U and [Disabilities] is not N and [Disabilities] is not Null, D,Null) +
    IIf([Homeless] Like Y,H,Null) +
    IIf([maritalcode] Like "*married*",IIf([TraumaCodes] Like "*d*","B",Null)) +
    IIf([Impeded] Like "Yes","I",Null)

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    at first glance

    im not certain you can use IS NOT when comparing to string literals, I'd expect <>

    Code:
    IIf(Int([SAIntakeDate]-[DOB])/365)<18 and
    IIf([TraumaCodes] Like “D”
    Or [TraumaCodes] Like “E”
    Or [TraumaCodes] Like “F”
    Or [TraumaCodes] Like “P”
    Or [TraumaCodes] Like “S”
    Or [TraumaCodes] Like “W”
    Or [TraumaCodes] Like “X”),
    “C”,Null) +
    IIf(Int([SAIntakeDate]-[DOB])/365)>=62 ,“E”,Null) +
    IIf([Disabilities] <> “U” and [Disabilities] <> “N” and [Disabilities] is not Null, “D”,Null) +
    IIf([Homeless] Like “Y”,”H”,Null) +
    IIf([maritalcode] Like "*married*",IIf([TraumaCodes] Like "*d*","B",Null)) +
    IIf([Impeded] Like "Yes","I",Null)

    howeevr thats fiendishly complex statement in a query or control assignment
    if its a control assignement then push it into a function that returns the required value and call the function as part of the assignment
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2014
    Posts
    2
    Thank you for your reply...I'm not sure how to push it into a function that returns the required value and call the function as part of the assignment. I have been working on databases for a while but I still have a lot to learn. Can you maybe help with how I would go about doing this?

    Quote Originally Posted by healdem View Post
    at first glance

    im not certain you can use IS NOT when comparing to string literals, I'd expect <>

    Code:
    IIf(Int([SAIntakeDate]-[DOB])/365)<18 and
    IIf([TraumaCodes] Like D
    Or [TraumaCodes] Like E
    Or [TraumaCodes] Like F
    Or [TraumaCodes] Like P
    Or [TraumaCodes] Like S
    Or [TraumaCodes] Like W
    Or [TraumaCodes] Like X),
    C,Null) +
    IIf(Int([SAIntakeDate]-[DOB])/365)>=62 ,E,Null) +
    IIf([Disabilities] <> U and [Disabilities] <> N and [Disabilities] is not Null, D,Null) +
    IIf([Homeless] Like Y,H,Null) +
    IIf([maritalcode] Like "*married*",IIf([TraumaCodes] Like "*d*","B",Null)) +
    IIf([Impeded] Like "Yes","I",Null)

    howeevr thats fiendishly complex statement in a query or control assignment
    if its a control assignement then push it into a function that returns the required value and call the function as part of the assignment

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    soemthing like (in a code module)
    Code:
    public function ReplaceMyFiendishlyComplexIIFStatement(intakedat as date, DoB as date, TraumaCode as string, Disabilities as String, Homeless as string, maritalcode as string,impeded  as string) as string 
    dim ReturnValue as string
    ReturnValue = ""
    Dim ageatIntake as integer
    AgeAtIntake = (intakedate-dob)\365
    if AgeAtIntake <18
      Select Case  traumacode
        case is "D", "E", "F", "P", "S", "W", "X"
          ReturnValue = "C"
      case else
         ReturnValue = ""
      end select
    elseif AgeAtIntake >=62
      ReturnValue = "E"  
    'else for completeness of logic
    '  returnvalue = ""
    endif
    If(IS null(DISABILITIES) or NOT([Disabilities] = “U” or [Disabilities] = “N” and [Disabilities]) then
      ReturnValue =  ReturnValue & "E"   '& is the string concatnation symbol in VB/VBA not +
    'else  'else for completeness of logic
    '  returnvalue = ReturnValue & ""
    endif
    If([Homeless] = “Y”
      ReturnValue =  ReturnValue & ”H”
    'else  'else for completeness of logic
    '  returnvalue = ReturnValue & ""
    If([maritalcode] Like "*married*" then
      If([TraumaCodes] Like "*d*" then
        ReturnValue =  ReturnValue & ”B”
      endif
    IIf([Impeded] = "Yes","I",Null)
        ReturnValue =  ReturnValue & ”I"
    endif
    return returnvalue
    end function
    then you can call that function where required, in a query, a form a report a whatever
    supply the appropriate parametres
    you will need to 'harden' the function to handle null and or impropoer values
    what I think your code is trying to do is generate a multi digit composite code but to be honest its confusing! or perhaps more accurately its late in the day and Im strugglign to concentrate, and or be bothered to fully understand what you are trying to do

    however key points arising
    use <> for not equal in an IF statement
    use like when there is a partial match (ie you are using one of the wild cards otherwise use =

    by pushing this into a function its possible to break each element into its constituent parts AND makes it easy to debug. so instead of "it doesn't work" you can put a watch / breakpoint ont he code and step through it to work out what is actually going on. if you don't know how to make use of the integrated debugger (and you are trying to create complex statements like this then do yourself a favour, take a timeout and learn up on debugging
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    Iif statements have 2 - 3 arguments (ex: iif(statement, IfTrue, IfFalse)) and from I am seeing in your code, you are closing your parentheses early.

    Let's take your trauma code piece and collapse it a bit to help illustrate.

    IIf([TraumaCodes] Like “D” Or [TraumaCodes] Like “X”) ,“C”,Null)

    Here, you are basically closing your Iif without defining the IfTrue, or IfFalse. To correct it, you would have to do something like this:

    IIf([TraumaCodes] Like "D", "C", Iif([TraumaCodes] Like "X" ,"C",Null))

    Unfortunately, I do not believe ORs/ANDs are acceptable syntax in this usage of Iif which is why I have it nested as I did.

    Editted:

    Apparently, you can, so I apologize. The underlying problem still applies. It would look like:

    IIf([TraumaCodes] Like "D" OR TraumaCodes] Like "X", "C", Null)
    Last edited by Brian Cermak; 11-06-14 at 17:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •