Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Re: dlookup criteria

Discussion in 'Information Technology' started by John Vinson, Nov 12, 2005.

  1. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Fri, 11 Nov 2005 11:28:09 -0800, "Greg"
    <Greg@discussions.microsoft.com> wrote:

    >i'm trying to get dlookup to pull a value from a query in the details section
    >of my report (which is based off of a different query). this way, it will
    >reflect the value i need it to as the groupings change. right now, i have:
    >
    >=DLookUp("[units]","qrysoldunits",[qrysoldunits]![salesperson]=
    >[qrycontactdata]![Salesperson] And [qrysoldunits]![deptartment]=
    >[qrycontactdata]![Department] And ([qrysoldunits]![monthandyear] Between
    >[txtboxstartdate] And [txtboxenddate]))
    >
    >but all i get is an error message (#error). if i remove the bit about the
    >dates being in the correct range:


    The third argument of DLookUp needs to be a TEXT STRING which
    evaluates to a SQL WHERE clause. You're just putting in the WHERE
    clause.

    Try concatenating the fieldnames as text literals to the variable
    values returned from the form. Assuming that Salesperson and
    Department are Text fields; that this expression is in a Form based on
    qrycontactdata; and that monthandyear is a Date/Time field (which is
    purely a guess), try

    =DLookUp("[units]", "qrysoldunits", "[salesperson]=" & Chr(34) &
    [qrycontactdata]![Salesperson] & Chr(34) &
    " And [deptartment]=" & Chr(34) & [qrycontactdata]![Department] &
    Chr(34) & " And [monthandyear] Between #" & [txtboxstartdate] & "# And
    #" & [txtboxenddate] & "#")

    Thus if Salesperson is "Jones", Department is "Electronics", the third
    argument will evaluate to

    [Salesperson] = "Jones" AND [Deptartment] = "Electronics" AND
    [MonthAndYear] BETWEEN #10/1/05# AND #10/31/05#


    John W. Vinson[MVP]
     
  2. Loading...

    Similar Threads Forum Date
    Criteria: When She Deserves to be Raped Hard Talk Dec 21, 2012
    Sikh News Punjab mulling change in pass criteria in board exams (Outlook India) Breaking News Dec 3, 2007

  3. Greg

    Greg
    Expand Collapse
    Guest

    unfortunately, that expression didn't work. i replied to the post left by
    wayne morgan with a few more details of what i have going on.

    "John Vinson" wrote:

    > On Fri, 11 Nov 2005 11:28:09 -0800, "Greg"
    > <Greg@discussions.microsoft.com> wrote:
    >
    > >i'm trying to get dlookup to pull a value from a query in the details section
    > >of my report (which is based off of a different query). this way, it will
    > >reflect the value i need it to as the groupings change. right now, i have:
    > >
    > >=DLookUp("[units]","qrysoldunits",[qrysoldunits]![salesperson]=
    > >[qrycontactdata]![Salesperson] And [qrysoldunits]![deptartment]=
    > >[qrycontactdata]![Department] And ([qrysoldunits]![monthandyear] Between
    > >[txtboxstartdate] And [txtboxenddate]))
    > >
    > >but all i get is an error message (#error). if i remove the bit about the
    > >dates being in the correct range:

    >
    > The third argument of DLookUp needs to be a TEXT STRING which
    > evaluates to a SQL WHERE clause. You're just putting in the WHERE
    > clause.
    >
    > Try concatenating the fieldnames as text literals to the variable
    > values returned from the form. Assuming that Salesperson and
    > Department are Text fields; that this expression is in a Form based on
    > qrycontactdata; and that monthandyear is a Date/Time field (which is
    > purely a guess), try
    >
    > =DLookUp("[units]", "qrysoldunits", "[salesperson]=" & Chr(34) &
    > [qrycontactdata]![Salesperson] & Chr(34) &
    > " And [deptartment]=" & Chr(34) & [qrycontactdata]![Department] &
    > Chr(34) & " And [monthandyear] Between #" & [txtboxstartdate] & "# And
    > #" & [txtboxenddate] & "#")
    >
    > Thus if Salesperson is "Jones", Department is "Electronics", the third
    > argument will evaluate to
    >
    > [Salesperson] = "Jones" AND [Deptartment] = "Electronics" AND
    > [MonthAndYear] BETWEEN #10/1/05# AND #10/31/05#
    >
    >
    > John W. Vinson[MVP]
    >
    >
     

Share This Page