Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2004
    Posts
    178

    Unanswered: iif in query does not do what i want

    i have this code in a query

    TicketTotal: IIf(IsNull(DLookUp(" [qrySetShowsTempReport2]![SumOfTicketPrice] ","[qrySetShowsTempReport2]","[title]")),0,DLookUp("[qrySetShowsTempReport2]![SumOfTicketPrice]","qrySetShowsTempReport2","[title]"))


    it will display only 1 result for all it finds and not a different result for each line.

    if it helps i will be desplying the results in a subreport as they would apper in the query

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No surprise, with no valid criteria. Here's a handy reference:

    http://www.mvps.org/access/general/gen0018.htm
    Paul

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also the domain agg functions are pretty inefficient - avoid if you can (e.g. incorporate the logic into the query). And you've made ineficient use of them too. Paul is right but in addition you can also try something like:
    Code:
    Nz(DLookUp("[qrySetShowsTempReport2]![SumOfTicketPrice] ","[qrySetShowsTempReport2]","[title]"),0)
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Aug 2004
    Posts
    178
    i fixed this and i got all 0s

    heres the new code

    TicketTotal: IIf(IsNull(DLookUp(" [qrySetShowsTempReport2]![SumOfTicketPrice] ","[qrySetShowsTempReport2]","[tblfilms.Title]"="[qryReportsTemp2]![tblfilms.Title]")),0,DLookUp("[qrySetShowsTempReport2]![SumOfTicketPrice]","qrySetShowsTempReport2","[tblfilms.Title]"="[qryReportsTemp2]![tblfilms.Title]"))

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Still not correct. Try this guess, since I don't know the structure:

    DLookUp("[SumOfTicketPrice]","qrySetShowsTempReport2","[tblfilms.Title]='" & [tblfilms.Title] & "'")
    Paul

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Get rid of the Iif logic whilst debugging (and IMO altogether and repace with Nz) - it will just get in the way.

    Your code is in error. Try:
    Code:
    DLookUp("[qrySetShowsTempReport2]![SumOfTicketPrice] ","[qrySetShowsTempReport2]","[tblfilms.Title] = '" & [qryReportsTemp2]![tblfilms.Title] & "'")
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2004
    Posts
    178
    i have to 2 querys 1 has 3 fields

    titleid title Date
    356 Click 09/10/2006
    357 Hoodwinked 09/10/2006

    query2 has 4 fields

    titleid title date sunofticketprice
    356 Click 09/10/2006 150
    357 Hoodwinked 09/10/2006 7

    query1 will always have more records than query2. query2 will have the same or less than query1

    what i want is lookup query1 find the someoftickprice and put that value in. if there is norecord of it in query2 i want it to put a zero value

  8. #8
    Join Date
    Aug 2004
    Posts
    178
    i have tried a few codes with your help and i seem to get the same results. so maybe i am going about this the wrong way so if i put it in a form or a report in an unbound textbox but this does not work either. the problem is in the dlookup it wont move to the next record and redo the dlookup. is this possable

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A domain aggregate function is constructed like a SQL statement:

    Code:
    DLookup("[qrySetShowsTempReport2]![SumOfTicketPrice] "
    ,"[qrySetShowsTempReport2]"
    ,"[tblfilms.Title] = '" & [qryReportsTemp2]![tblfilms.Title] & "'")
    Code:
    SELECT [qrySetShowsTempReport2]![SumOfTicketPrice] 
    FROM [qrySetShowsTempReport2] 
    WHERE [tblfilms.Title] = '" & [qryReportsTemp2]![tblfilms.Title] & "'"
    Does qryReportsTemp2 have any context within qrySetShowsTempReport2? The answer is "no". If your FROM clause is qrySetShowsTempReport2 then you can't reference columns from qryReportsTemp2.

    Make sense?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Aug 2004
    Posts
    178
    ok i have created this little to try to explane what in the report
    Attached Files Attached Files

  11. #11
    Join Date
    Aug 2004
    Posts
    178
    i solved it by creating a combo box on a form based on the second query with a control = a field on the form. i should point out that the form was set to the first query i then put an unbound textbox on the same form and put this code in it.

    =IIf(IsNull(DLookUp("[qrySetShowsTempReport2]![SumOfTicketPrice]","qrySetShowsTempReport2","tblsetshowsTemp.Title= " & nz("[qrySetShowsTempReport2]![Id]",0))),0,(DLookUp("[qrySetShowsTempReport2]![SumOfTicketPrice]","qrySetShowsTempReport2","tblsetshowsTemp.Title= " & nz(" [qrySetShowsTempReport2]![Id]",0))))

    and it worked

Posting Permissions

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