dlookup criteria

[Greg]

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.

 

[John Vinson]

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]

 

[Wayne Morgan]

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

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.

 

[Greg]

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?

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

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.

 

[Greg]

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.

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]

 

[Wayne Morgan]

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.

 

[Greg]

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.