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 Wayne Morgan, Nov 12, 2005.

  1. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    All 3 arguments for the DLookup() function need to be strings. Also, I
    suspect that Department is misspelled. I'm also not sure that Between will
    work, you may have to enter this as <= And >=.

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

    Also, it appears that you're trying to pull the value from another query.
    Does this other query only return a single value? If not, then which of the
    rows do you want the value from? These statements themselves will probably
    need to be DLookup() statements. Are the values you want here available on
    the report? If so, just take them from the controls on the report. Also, to
    use values from the controls on the report, you'll need to concatenate in
    the value or give the full path to the control. The latter works for forms,
    I haven't tried it with a report.

    =DLookUp("[units]","qrysoldunits","[salesperson]=QueryDefs![qrycontactdata]![Salesperson]
    And [deptartment]=QueryDefs![qrycontactdata]![Department] And
    ([monthandyear] >= #" & [txtboxstartdate] & "# And [monthandyear] <= #" &
    [txtboxenddate] & "#)")

    The #'s are date delimiters, just as " are string delimiters. The field you
    have at the end is called MonthAndYear, is it an actual date or just part of
    one?

    DLookup() will only return ONE value, the first one it finds in the record
    set specified by the second argument that matches the criteria in the third
    argument. If you are wanting to return a list, a list box may be what you're
    needing with a query as the Row Source.

    Will you give a better explanation of what you're trying to do?

    --
    Wayne Morgan
    MS Access MVP


    "Greg" <Greg@discussions.microsoft.com> wrote in message
    news:9449A679-2291-4240-99F1-B48CB67FF991@microsoft.com...
    > 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:
    >
    > =DLookUp("[units]","qrysoldunits",[qrysoldunits]![salesperson]=
    > [qrycontactdata]![Salesperson] And [qrysoldunits]![deptartment]=
    > [qrycontactdata]![Department])
    >
    > it's not an error for the 1st salesperson, but it only pulls the units
    > value
    > for the first record in the query, that may or may not match that
    > salesperson
    > -- and for every other salesperson, it's an error.
    >
    > i'm pretty sure that this is the right direction for my solution but i
    > must
    > have my criteria wrong. please help.
    >
    >
     
  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

    depatment was only misspelled in my post. sorry. the salesperson and
    department fields are text and the monthandyear field is a date formatted
    mmm/yyyy. the query that i'm trying to pull from has only 4 fields:
    salesperson, department, monthandyear and units. it looks like this:

    jon doe 5 10-2005 new
    jon doe 4 10-2005 used
    jane doe 6.5 10-2005 new
    john smith 8.5 10-2005 new
    john smith 3 10-2005 used

    i have a report written based on a query that calculates the # of customers
    each salesperson sees for a user-prompted time period. that report pulls the
    information from a form that is set up to prompt for about 5 things, run both
    queries and then print the report. the report was constructed using the
    wizard off of only one query (qrycontactdata). i later found that i needed a
    2nd query to pull the # of sold cars from a different table and that needs to
    be part of the report, grouped by salesperson and then department (the way
    the report was done using the wizard) i thought to put my textbox with this
    dlookup value in the 'details' section of the report so that it will pull the
    appropriate value for each salesperson and department as it constructs the
    report. i had already tried joining the queries and tables in all kinds of
    combinations to get it to work, without success. dlookup 'felt' like the
    right direction but now you're making me think otherwise. a listbox? what
    is this listbox of which you speak?

    "Wayne Morgan" wrote:

    > All 3 arguments for the DLookup() function need to be strings. Also, I
    > suspect that Department is misspelled. I'm also not sure that Between will
    > work, you may have to enter this as <= And >=.
    >
    > =DLookUp("[units]","qrysoldunits","[salesperson]=QueryDefs![qrycontactdata]![Salesperson]
    > And [deptartment]=QueryDefs![qrycontactdata]![Department] And
    > ([monthandyear] Between [txtboxstartdate] And [txtboxenddate])")
    >
    > Also, it appears that you're trying to pull the value from another query.
    > Does this other query only return a single value? If not, then which of the
    > rows do you want the value from? These statements themselves will probably
    > need to be DLookup() statements. Are the values you want here available on
    > the report? If so, just take them from the controls on the report. Also, to
    > use values from the controls on the report, you'll need to concatenate in
    > the value or give the full path to the control. The latter works for forms,
    > I haven't tried it with a report.
    >
    > =DLookUp("[units]","qrysoldunits","[salesperson]=QueryDefs![qrycontactdata]![Salesperson]
    > And [deptartment]=QueryDefs![qrycontactdata]![Department] And
    > ([monthandyear] >= #" & [txtboxstartdate] & "# And [monthandyear] <= #" &
    > [txtboxenddate] & "#)")
    >
    > The #'s are date delimiters, just as " are string delimiters. The field you
    > have at the end is called MonthAndYear, is it an actual date or just part of
    > one?
    >
    > DLookup() will only return ONE value, the first one it finds in the record
    > set specified by the second argument that matches the criteria in the third
    > argument. If you are wanting to return a list, a list box may be what you're
    > needing with a query as the Row Source.
    >
    > Will you give a better explanation of what you're trying to do?
    >
    > --
    > Wayne Morgan
    > MS Access MVP
    >
    >
    > "Greg" <Greg@discussions.microsoft.com> wrote in message
    > news:9449A679-2291-4240-99F1-B48CB67FF991@microsoft.com...
    > > 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:
    > >
    > > =DLookUp("[units]","qrysoldunits",[qrysoldunits]![salesperson]=
    > > [qrycontactdata]![Salesperson] And [qrysoldunits]![deptartment]=
    > > [qrycontactdata]![Department])
    > >
    > > it's not an error for the 1st salesperson, but it only pulls the units
    > > value
    > > for the first record in the query, that may or may not match that
    > > salesperson
    > > -- and for every other salesperson, it's an error.
    > >
    > > i'm pretty sure that this is the right direction for my solution but i
    > > must
    > > have my criteria wrong. please help.
    > >
    > >

    >
    >
    >
     
  4. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    DLookup() will only return one value, the first one it finds that matches
    the criteria in the third argument. There are other aggregate functions that
    may do what you want. Specifically, take a look at DCount() and DSum() to
    see if one of them will get the value you're looking form. DCount will count
    the number of records that match the criteria and DSum will give you the sum
    of a specified field for the records that match the criteria you specify.

    --
    Wayne Morgan
    MS Access MVP


    "Greg" <Greg@discussions.microsoft.com> wrote in message
    news:081B6FD6-FC42-48E1-A589-A9F1641BAADA@microsoft.com...
    > depatment was only misspelled in my post. sorry. the salesperson and
    > department fields are text and the monthandyear field is a date formatted
    > mmm/yyyy. the query that i'm trying to pull from has only 4 fields:
    > salesperson, department, monthandyear and units. it looks like this:
    >
    > jon doe 5 10-2005 new
    > jon doe 4 10-2005 used
    > jane doe 6.5 10-2005 new
    > john smith 8.5 10-2005 new
    > john smith 3 10-2005 used
    >
    > i have a report written based on a query that calculates the # of
    > customers
    > each salesperson sees for a user-prompted time period. that report pulls
    > the
    > information from a form that is set up to prompt for about 5 things, run
    > both
    > queries and then print the report. the report was constructed using the
    > wizard off of only one query (qrycontactdata). i later found that i needed
    > a
    > 2nd query to pull the # of sold cars from a different table and that needs
    > to
    > be part of the report, grouped by salesperson and then department (the way
    > the report was done using the wizard) i thought to put my textbox with
    > this
    > dlookup value in the 'details' section of the report so that it will pull
    > the
    > appropriate value for each salesperson and department as it constructs the
    > report. i had already tried joining the queries and tables in all kinds
    > of
    > combinations to get it to work, without success. dlookup 'felt' like the
    > right direction but now you're making me think otherwise. a listbox?
    > what
    > is this listbox of which you speak?
     
  5. Greg

    Greg
    Expand Collapse
    Guest

    actually, the problem turned out to be a vba issue. i had it closing the
    form automatically when the report opened, so it would not calculate anything
    past the first page. thanks very much for your help though.

    the final expression that worked was:

    =DLookUp("[units]","qrysoldunits","[salesperson]=""" & [Salesperson] & """
    AND [Department]=""" & [Department] & """ AND ([monthandyear] Between #" &
    Format([txtboxstartdate],"mm/dd/yyyy") & "# And #" &
    Format([txtboxenddate],"mm/dd/yyyy") & "# )")

    and i got that from someone else on another site. i can tell you what it
    means and what it does, but the plethora of quotes and ampersands makes the
    format well out of my skill level.

    "Wayne Morgan" wrote:

    > DLookup() will only return one value, the first one it finds that matches
    > the criteria in the third argument. There are other aggregate functions that
    > may do what you want. Specifically, take a look at DCount() and DSum() to
    > see if one of them will get the value you're looking form. DCount will count
    > the number of records that match the criteria and DSum will give you the sum
    > of a specified field for the records that match the criteria you specify.
    >
    > --
    > Wayne Morgan
    > MS Access MVP
    >
    >
    > "Greg" <Greg@discussions.microsoft.com> wrote in message
    > news:081B6FD6-FC42-48E1-A589-A9F1641BAADA@microsoft.com...
    > > depatment was only misspelled in my post. sorry. the salesperson and
    > > department fields are text and the monthandyear field is a date formatted
    > > mmm/yyyy. the query that i'm trying to pull from has only 4 fields:
    > > salesperson, department, monthandyear and units. it looks like this:
    > >
    > > jon doe 5 10-2005 new
    > > jon doe 4 10-2005 used
    > > jane doe 6.5 10-2005 new
    > > john smith 8.5 10-2005 new
    > > john smith 3 10-2005 used
    > >
    > > i have a report written based on a query that calculates the # of
    > > customers
    > > each salesperson sees for a user-prompted time period. that report pulls
    > > the
    > > information from a form that is set up to prompt for about 5 things, run
    > > both
    > > queries and then print the report. the report was constructed using the
    > > wizard off of only one query (qrycontactdata). i later found that i needed
    > > a
    > > 2nd query to pull the # of sold cars from a different table and that needs
    > > to
    > > be part of the report, grouped by salesperson and then department (the way
    > > the report was done using the wizard) i thought to put my textbox with
    > > this
    > > dlookup value in the 'details' section of the report so that it will pull
    > > the
    > > appropriate value for each salesperson and department as it constructs the
    > > report. i had already tried joining the queries and tables in all kinds
    > > of
    > > combinations to get it to work, without success. dlookup 'felt' like the
    > > right direction but now you're making me think otherwise. a listbox?
    > > what
    > > is this listbox of which you speak?

    >
    >
    >
     

Share This Page