Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2004
    Posts
    29

    Unanswered: Easy IIF questions - Two conditions

    Hi everyone,

    I have this IIF statement, that returns yes or no for a calculated field in a select query:

    IIf((tmpProbleme.DateHeureFerme<tmpProbleme.varTem psPromisInitial) or (tmpactivite.typeactivite = "7 - Solution finale") and (tmpactivite.DateActivite<tmpProbleme.varTempsProm isInitial),"Oui","Non") AS Respect_delais

    It is driving me bonkers because the second condition:

    tmpactivite.DateActivite<tmpProbleme.varTempsPromi sInitial

    always generate an #Error when it's evaluated...

    If I run both conditions on their own, they both work individually which is not what I want.

    I've tried nested IIFs and I get the same result, even tried to run a subquery for the second condition and get the same error.

    Can anyone throw their two cents in please.

    Thanks

    R.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you've got three conditions, not two, combined with OR and AND -- let's say, for the sake of shorthand, they are x OR y AND z

    how did you want them evaluated?

    as (x OR y) AND z ?

    or as x OR ( y AND z ) ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    29
    x OR ( y AND z )

  4. #4
    Join Date
    Sep 2004
    Posts
    29
    Quote Originally Posted by duaner View Post
    x OR ( y AND z )
    even with x OR ( y AND z )...Still get the Error

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by duaner View Post
    even with x OR ( y AND z )...Still get the Error
    and what error is that
    and what SQL did you try?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    IIf(tmpProbleme.DateHeureFerme < tmpProbleme.varTempsPromisInitial , "Oui" ,
    IIf(tmpactivite.typeactivite = "7 - Solution finale"
    and tmpactivite.DateActivite < tmpProbleme.varTempsPromisInitial, "Oui" , "Non" ) ) AS Respect_delais
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2004
    Posts
    29
    Hi Rudy.

    Yes I had tried and tried it again, nested Iif, get the same result when the last condition gets evaluated (#Error). This is a weird one.

    When I run them seperately no error...

    R

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so can we see the actual SQL you are using?
    if you are doing this in the query designed switch to SQL view and copy and paste the result here

    where are you using his code?
    do...
    tmpProbleme.DateHeureFerme
    tmpProbleme.varTempsPromisInitial
    tmpactivite.typeactivite
    tmpactivite.DateActivite
    tmpProbleme.varTempsPromisInitial exist

    seeing varTempsPromisInitial makes me wonder if perhgaps these are variables rahter than columns

    is it possible you have a inside the text being compared (it may be that the sql parser its getting confused. one way to try this is toi check each part of the IIF

    is the join correctly formed
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Sep 2004
    Posts
    29
    Quote Originally Posted by healdem View Post
    so can we see the actual SQL you are using?
    if you are doing this in the query designed switch to SQL view and copy and paste the result here

    where are you using his code?
    do...
    tmpProbleme.DateHeureFerme
    tmpProbleme.varTempsPromisInitial
    tmpactivite.typeactivite
    tmpactivite.DateActivite
    tmpProbleme.varTempsPromisInitial exist

    seeing varTempsPromisInitial makes me wonder if perhgaps these are variables rahter than columns

    is it possible you have a inside the text being compared (it may be that the sql parser its getting confused. one way to try this is toi check each part of the IIF

    is the join correctly formed
    tmpProbleme.DateHeureFerme
    tmpProbleme.varTempsPromisInitial
    tmpactivite.typeactivite
    tmpactivite.DateActivite
    tmpProbleme.varTempsPromisInitial

    these are all fields in a temp table and yes varTempsPromisInitial is also a time stamp field

    This is run in a simple SQL select statement where all fields are available for the query.

    is it possible you have a inside the text being compared (it may be that the sql parser its getting confused. one way to try this is toi check each part of the IIF

    i'll run them seperately again today and keep you posted.

  10. #10
    Join Date
    Sep 2004
    Posts
    29
    OK i've narrowed it down...in the:

    IIf((tmpProbleme.DateHeureFerme<tmpProbleme.varTem psPromisInitial) or ((tmpactivite.typeactivite = "7 - Solution finale") and (tmpactivite.DateActivite<tmpProbleme.varTempsProm isInitial)),"Oui","Non") AS Respect_delais

    it's the last condition

    tmpactivite.DateActivite<tmpProbleme.varTempsPromi sInitial

    that throws out the Error.

    These two fields are both date fields. I've even built a query and ran the query getting the error off that new one and still got the error.

    Is it possible that I am getting the error because the two fields are from different tables? Even though there is a right join created in the query?

    I've included the sql code...this is just stumping me lol.

    Thanks

    R.
    Attached Files Attached Files

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im just wondering if its to do with the join, and what you are experiencing is effectively a data error rather than a query error

    your right join will return all rows in tmpactivitie and any rows in tmpprobleme that match

    for now it might be worth investigating what happens if you make it 'just' a simple join and see if that clears the issue.

    TBH its a while since I've tinkered with Access queries using IIF so I'd perhaps want to check it was happy with the date comparison
    eg
    Code:
    iif((tmpactivite.DateActivite<tmpProbleme.varTempsPromisInitial)),"Oui","Non") AS Respect_delais
    sometimes these sort of problems can be worked around using sub queries
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Sep 2004
    Posts
    29
    I've tried different joins, access will only let you RIGHT, INNER, LEFT joins. What did you mean by simple join? Just have JOIN in the from clause generates a query error.

    Regardless of the join I used I still get the same evaluation Error, it's like the two fields have different datatypes, but they are both date...strange.

    I've tried running the las part in a sub-query, I end up getting the same error.

    Thanks

    R.

  13. #13
    Join Date
    Sep 2004
    Posts
    29
    Found the source of the problem...the field tmpactivite.DateActivite is text

    It is stored like this:

    "2011-10-03 13:35:42"

    How can I convert this to date/time at query run time? I can't modify the source table. I am pulling this information from a lotus Notes database into temp tables via ODBC connection. I've used the FORMAT() function for hh:mm:ss format before, but the way the text is stored is a bigger challenge.

    Thanks

    R.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using CDATE to convert it to a date value
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Sep 2004
    Posts
    29
    Yay problem solved...damned Notes developper put in a text time stamp in it's database.

    Thanks all for your responses

    Cheers

    R.

Posting Permissions

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