locked
DLookup not returning result when source is a query RRS feed

  • Question

  • Hi all ye access gurus,

    My query had a criteria  for PtsFemale = CInt([Forms]![frmPtsDB].[txtVar3] and it worked fine. I needed to create 3 different queries with 3 different criteria so I removed the criteria & tried using this DLookup - Me.txtVar2 = Nz(DLookup("PtsMale", "qryLatestPartner", "PtsFemale = " & CInt([Forms]![frmPtsDB].[txtVar3])), 0) in order to have one query but with varying criteria.

    To my dismay I get 0 as the result and I can't fathom out why. Can anyone explain the reason that I may choose the best path forward. This query returns the TOP 1 result in DESC sort and I have the identical returning in ASC sort and each x 3. So I'm trying to reduce 6 queries to 2

    Friday, July 22, 2011 8:27 PM

Answers

  • Of course I could have seperate "WHERE" string clauses based on which value I'm looking for but then it's probably more efficient just having several queries.

     

    What I need to also understand is why it works if I supply the criteria within the query but not if I try pass the criteria via a method such as DLookup.

    Hi Hugh,

    In my previous answer I explained why you get a 0 result, but probably I was not too explicit.

    So,

    without criteria

     

    SELECT TOP 1 tblCompetitions.Comp_Date, tblPtsPerCompHistory.PtsMale, tblPtsPerCompHistory.PtsFemale, tblEvtStructure.StylID
    FROM (tblPtsPerCompHistory INNER JOIN tblEvtStructure ON tblPtsPerCompHistory.PtsStructID = tblEvtStructure.EvtStruct_Idx)

    INNER JOIN tblCompetitions ON tblPtsPerCompHistory.PtsCompID = tblCompetitions.Competition_Idx
    WHERE tblEvtStructure.StylID = [Forms]![frmPtsDB].[txtVar1]
    ORDER BY tblCompetitions.Comp_Date DESC;

    With the above query you retrieve the TOP 1 record using tblEvtStructure.StylID = [Forms]![frmPtsDB].[txtVar1] ORDER BY ...
    This one record (!) is most likely from a different PtsFemale then you are looking for. So, if you "filter" this one record with the condition in your DLookup, the result is no record, and DLookup returns a 0.

     

    By wrapping your query in a VBA function, using txtVar1 and/or txtVar3 and/or ... as parameters, you can in fact run all your queries by supplying the appropriate values to the parameters.

     

    Imb.

    Saturday, July 23, 2011 2:15 PM

All replies

  • Hi Hugh,

    It is not yet clear to me what you exactly want.

    At least  DLookup - Me.txtVar2 = ... does not look good to me.

     

    Imb.

    Friday, July 22, 2011 8:54 PM
  • Hi Imb,

    Maybe I made it confusing in my layout. The VBA code is

     Me.txtVar2 = Nz(DLookup("PtsMale", "qryLatestPartner", "PtsFemale = " & CInt([Forms]![frmPtsDB].[txtVar3])), 0)

    In the above I want to lookup the value of "PtsMale" and apply it to my forms textbox "txtVar2" where "Ptsfemale" in the query is the value of my forms textbox "txtVar3"

    Hope that makes better sense

    Friday, July 22, 2011 9:01 PM
  • Hi Imb,

    Maybe I made it confusing in my layout. The VBA code is

     Me.txtVar2 = Nz(DLookup("PtsMale", "qryLatestPartner", "PtsFemale = " & CInt([Forms]![frmPtsDB].[txtVar3])), 0)

    In the above I want to lookup the value of "PtsMale" and apply it to my forms textbox "txtVar2" where "Ptsfemale" in the query is the value of my forms textbox "txtVar3"

    Hope that makes better sense


    Hi Hugh,

    This makes more sense.
    The syntax looks ok to me. A 0 value is returned when there are no records found in your DLookup function.
    How looks qryLatestPartner. Has it the fields PtsMale and PtsFemale. Are PtsMale and PtsFemale integers?

     

    Imb.

    Friday, July 22, 2011 9:24 PM
  • Hi Imb,

    It's the same query I was using with criteria "CInt([Forms]![frmPtsDB].[txtVar3])" which worked fine. I removed the criteria so I could pass the criteria via VBA code using DLookup. Now I get 0 result ie: no records & I need to understand why so I can plan my way forward.

    Friday, July 22, 2011 9:34 PM
  • Hi Hugh,

    Can you then post the qryLatestPartner? Eventually before and after removing the criteria?

     

    Imb.

    Friday, July 22, 2011 9:57 PM
  • With criteria

    SELECT TOP 1 tblCompetitions.Comp_Date, tblPtsPerCompHistory.PtsMale, tblPtsPerCompHistory.PtsFemale, tblEvtStructure.StylID
    FROM (tblPtsPerCompHistory INNER JOIN tblEvtStructure ON tblPtsPerCompHistory.PtsStructID = tblEvtStructure.EvtStruct_Idx)

    INNER JOIN tblCompetitions ON tblPtsPerCompHistory.PtsCompID = tblCompetitions.Competition_Idx
    WHERE (((tblPtsPerCompHistory.PtsFemale) = [Forms]![frmPtsDB].[txtVar3]) AND ((tblEvtStructure.StylID) = [Forms]![frmPtsDB].[txtVar1]))
    ORDER BY tblCompetitions.Comp_Date DESC;

     

    without criteria

    SELECT TOP 1 tblCompetitions.Comp_Date, tblPtsPerCompHistory.PtsMale, tblPtsPerCompHistory.PtsFemale, tblEvtStructure.StylID
    FROM (tblPtsPerCompHistory INNER JOIN tblEvtStructure ON tblPtsPerCompHistory.PtsStructID = tblEvtStructure.EvtStruct_Idx)

    INNER JOIN tblCompetitions ON tblPtsPerCompHistory.PtsCompID = tblCompetitions.Competition_Idx
    WHERE tblEvtStructure.StylID = [Forms]![frmPtsDB].[txtVar1]
    ORDER BY tblCompetitions.Comp_Date DESC;

    Friday, July 22, 2011 10:12 PM
  • without criteria

     

    SELECT TOP 1 tblCompetitions.Comp_Date, tblPtsPerCompHistory.PtsMale, tblPtsPerCompHistory.PtsFemale, tblEvtStructure.StylID
    FROM (tblPtsPerCompHistory INNER JOIN tblEvtStructure ON tblPtsPerCompHistory.PtsStructID = tblEvtStructure.EvtStruct_Idx)

    INNER JOIN tblCompetitions ON tblPtsPerCompHistory.PtsCompID = tblCompetitions.Competition_Idx
    WHERE tblEvtStructure.StylID = [Forms]![frmPtsDB].[txtVar1]
    ORDER BY tblCompetitions.Comp_Date DESC;

     

    PtsFemale

    Hi Hugh,

    After a night's rest I think it is not the syntax of the query, but the logic that gives the problem.

    In fact. with your TOP 1 you select the most recent StylID in your database, and that is mostly not your "desired PtsFemale".

    But you can make a function what can do the job. Something like:

     

    Function Last_person(MyStylID as Long,MyPtsFemale as Long) as Long

      Dim rs AS Recordset
      Dim MyQuery as String

      MyQuery =

    "SELECT TOP 1 tblCompetitions.Comp_Date, tblPtsPerCompHistory.PtsMale, tblPtsPerCompHistory.PtsFemale, tblEvtStructure.StylID
    FROM (tblPtsPerCompHistory INNER JOIN tblEvtStructure ON tblPtsPerCompHistory.PtsStructID = tblEvtStructure.EvtStruct_Idx)

    INNER JOIN tblCompetitions ON tblPtsPerCompHistory.PtsCompID = tblCompetitions.Competition_Idx
    WHERE (((tblPtsPerCompHistory.PtsFemale) = " & MyPtsFemale & " AND ((tblEvtStructure.StylID) = " & MyStylId & " ORDER BY tblCompetitions.Comp_Date DESC"

      Set rs = CurrentDB.OpenRecordset (MyQuery)

      if (Not rs.EOF) Then Last_person = rs!PtsFemale

      rs.close

    End Function

     

    You still have to edit the MyQuery string in order to have the right syntax, but I hope the idea is clear

    .

    Imb.

    Saturday, July 23, 2011 8:17 AM
  • Hi Imb,

    Actually StylID is a criteria variable since it gets its values from a textbox on the form. I want to look up the last (TOP 1) PtsFemale if I supply the PtsMale criteria or the PtsMale if I supply the PtsFemale criteria. The PtsMale value coming from textbox txtVar2 or the PtsFemale value coming from textbox txtVar3. So this part of your sample code [ (((tblPtsPerCompHistory.PtsFemale) = " & MyPtsFemale ] cannot be hardcoded in the query

    Of course I could have seperate "WHERE" string clauses based on which value I'm looking for but then it's probably more efficient just having several queries.

     

    What I need to also understand is why it works if I supply the criteria within the query but not if I try pass the criteria via a method such as DLookup.

    Saturday, July 23, 2011 1:52 PM
  • Of course I could have seperate "WHERE" string clauses based on which value I'm looking for but then it's probably more efficient just having several queries.

     

    What I need to also understand is why it works if I supply the criteria within the query but not if I try pass the criteria via a method such as DLookup.

    Hi Hugh,

    In my previous answer I explained why you get a 0 result, but probably I was not too explicit.

    So,

    without criteria

     

    SELECT TOP 1 tblCompetitions.Comp_Date, tblPtsPerCompHistory.PtsMale, tblPtsPerCompHistory.PtsFemale, tblEvtStructure.StylID
    FROM (tblPtsPerCompHistory INNER JOIN tblEvtStructure ON tblPtsPerCompHistory.PtsStructID = tblEvtStructure.EvtStruct_Idx)

    INNER JOIN tblCompetitions ON tblPtsPerCompHistory.PtsCompID = tblCompetitions.Competition_Idx
    WHERE tblEvtStructure.StylID = [Forms]![frmPtsDB].[txtVar1]
    ORDER BY tblCompetitions.Comp_Date DESC;

    With the above query you retrieve the TOP 1 record using tblEvtStructure.StylID = [Forms]![frmPtsDB].[txtVar1] ORDER BY ...
    This one record (!) is most likely from a different PtsFemale then you are looking for. So, if you "filter" this one record with the condition in your DLookup, the result is no record, and DLookup returns a 0.

     

    By wrapping your query in a VBA function, using txtVar1 and/or txtVar3 and/or ... as parameters, you can in fact run all your queries by supplying the appropriate values to the parameters.

     

    Imb.

    Saturday, July 23, 2011 2:15 PM
  • Hi Imb,

    I keep forgetting the "TOP 1" in the query so that of course makes sense.

    I'll experiment with your function method as I need to pass StylID and either txtVar2 or txtVar3 as parameters where txtVar2 & txtVar3 correlate to different fields of the query.

    Thanks for the help

    Saturday, July 23, 2011 2:44 PM
  • Hi Hugh Self Taught,

     

    Thank you for posting in our forum.

     

    What's the status on your side?

    Imb provided us with a good workaround! Have you solved the problem?

    I think this problem is valuable.

    At first, I also feel strange and can't figure it out. Imb's idea is right!

     

    If you can share your solutions and experience here, it will be very beneficial for other community members who have similar questions. There are more than 200 members read the post before!

     

    I look forward from you.

     

    Best Regards,


    Macy Dong [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, July 28, 2011 10:51 AM
  • Hi All,

    What I had failed to realize is that by removing the criteria from the query,  the query would return the TOP 1 of all the unfiltered data, since the query executes first then the DLookup is basically a filter of that result. So no matter how I filtered it I could never achieve the result I was trying to get.

    I will be implementing Imb's solution as I can see how to use it & will post back the code & variations that I implement. 

    Thursday, July 28, 2011 9:36 PM