dlookup

[kaosyeti]

i'm trying to get dlookup to pull a value from a query in the details section
of my report. 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.

 

[Duane Hookom]

Your where clause must be a string. Assuming SalesPerson and Deptartment
(check your spelling) are string values and MonthAndYear are numeric...
=DLookUp("[units]","qrysoldunits",
"[salesperson]=""" & [qrycontactdata]![Salesperson] &
""" And [deptartment]=""" & [qrycontactdata]![Department] &
""" And [monthandyear] Between " &
[txtboxstartdate] & "And " & [txtboxenddate])

You should be able to figure out how to correct if monthandyear are text.

 

[kaosyeti]

i tried the expression you gave me and it still returns an error. i checked
for spelling and all seems fine. i copied and pasted my expression from
yours so i wouldn't make a mistake with the spacing or anything (and caught
the deptartment error) and still won't work. one thing, though.. i don't
know why you have so many ampersands as well as the word 'and' all over. is
this normal? thanks.

greg

Your where clause must be a string. Assuming SalesPerson and Deptartment
(check your spelling) are string values and MonthAndYear are numeric...
=DLookUp("[units]","qrysoldunits",
"[salesperson]=""" & [qrycontactdata]![Salesperson] &
""" And [deptartment]=""" & [qrycontactdata]![Department] &
""" And [monthandyear] Between " &
[txtboxstartdate] & "And " & [txtboxenddate])

You should be able to figure out how to correct if monthandyear are text.

i'm trying to get dlookup to pull a value from a query in the details
section

[quoted text clipped - 22 lines]

must
have my criteria wrong. please help.

 

[Duane Hookom]

I made some WAGs regarding your data types. You didn't respond to this. One
other possible issue is to make sure the name of the control is not also the
name of a field.

Since you are attempting to use this as a control source, I assume the field
expressions are from your report's record source. If that is the case then
you should probably remove the query name from the expression:
=DLookUp("[units]","qrysoldunits", "[salesperson]=""" & [Salesperson] & """
And [deptartment]=""" & [Department] & """ And [monthandyear] Between " &
[txtboxstartdate] & "And " & [txtboxenddate])

--
Duane Hookom
MS Access MVP
--

i tried the expression you gave me and it still returns an error. i
checked
for spelling and all seems fine. i copied and pasted my expression from
yours so i wouldn't make a mistake with the spacing or anything (and
caught
the deptartment error) and still won't work. one thing, though.. i don't
know why you have so many ampersands as well as the word 'and' all over.
is
this normal? thanks.

greg

Your where clause must be a string. Assuming SalesPerson and Deptartment
(check your spelling) are string values and MonthAndYear are numeric...
=DLookUp("[units]","qrysoldunits",
"[salesperson]=""" & [qrycontactdata]![Salesperson] &
""" And [deptartment]=""" & [qrycontactdata]![Department] &
""" And [monthandyear] Between " &
[txtboxstartdate] & "And " & [txtboxenddate])

You should be able to figure out how to correct if monthandyear are text.

i'm trying to get dlookup to pull a value from a query in the details
section

[quoted text clipped - 22 lines]

must
have my criteria wrong. please help.

 

[kaosyeti]

i kind of took your statement "Assuming SalesPerson and Deptartment
(check your spelling) are string values and MonthAndYear are numeric..." to
mean that if they are correctly formatted (which they are, i think) then
that's not the problem so i didn't mention it. the reason i'm not 100% sure
is that on the form that prompts the user for the parameters for the 2
queries that this report is based off of, there are comboboxes that have
their recordsource as small tables w/salesperson's names, department names,
etc.. they are definately text (except for the date, which is a date --
naturally). the comboboxes on the forms, however, have no format selected
and when i went to format the comboboxes as text, text wasn't an option. i
don't know if this is affecting anything or not.

now, the name of my controls on the form and in my report are things like
txtboxsalesperson and cboxsalesperson. however, the report has salesperson
and department as the field name AND control name (text box) in the
salesperson and department headers. should i change this? if i change the
name of these controls, will it screw anything else up? i used the wizard to
create this report so i didn't (knowingly) reference the name of the control
in anything that i changed/added to the report.

the field expressions ARE from my reports record source (qrycontactdata)
except where i want it to pull the units field for the corresponding
saleperson, department and date from qrysoldunits. which is why i'm here..
for dlookup criteria...8)

i must say that my frustration level with access is mounting but i know that
once i get this fixed, it will do SO much to help with these reports that
excel is just incapeable of that it'll be worth the agg.

if you want to see this db, i can send it to you (it's under 200kb zipped).
just email me at kaosyeti <(a.t.)> comcast <(d.o.t.)> net.

I made some WAGs regarding your data types. You didn't respond to this. One
other possible issue is to make sure the name of the control is not also the
name of a field.

Since you are attempting to use this as a control source, I assume the field
expressions are from your report's record source. If that is the case then
you should probably remove the query name from the expression:
=DLookUp("[units]","qrysoldunits", "[salesperson]=""" & [Salesperson] & """
And [deptartment]=""" & [Department] & """ And [monthandyear] Between " &
[txtboxstartdate] & "And " & [txtboxenddate])

i tried the expression you gave me and it still returns an error. i
checked

[quoted text clipped - 22 lines]

 

[Duane Hookom]

"except for the date, which is a date -- naturally" Which value is this? If
monthandyear is date and not numeric then try:
....""" And [monthandyear] Between #" &
[txtboxstartdate] & "# And #" & [txtboxenddate] "#")


--
Duane Hookom
MS Access MVP
--

i kind of took your statement "Assuming SalesPerson and Deptartment
(check your spelling) are string values and MonthAndYear are numeric..."
to
mean that if they are correctly formatted (which they are, i think) then
that's not the problem so i didn't mention it. the reason i'm not 100%
sure
is that on the form that prompts the user for the parameters for the 2
queries that this report is based off of, there are comboboxes that have
their recordsource as small tables w/salesperson's names, department
names,
etc.. they are definately text (except for the date, which is a date --
naturally). the comboboxes on the forms, however, have no format selected
and when i went to format the comboboxes as text, text wasn't an option.
i
don't know if this is affecting anything or not.

now, the name of my controls on the form and in my report are things like
txtboxsalesperson and cboxsalesperson. however, the report has
salesperson
and department as the field name AND control name (text box) in the
salesperson and department headers. should i change this? if i change
the
name of these controls, will it screw anything else up? i used the wizard
to
create this report so i didn't (knowingly) reference the name of the
control
in anything that i changed/added to the report.

the field expressions ARE from my reports record source (qrycontactdata)
except where i want it to pull the units field for the corresponding
saleperson, department and date from qrysoldunits. which is why i'm
here..
for dlookup criteria...8)

i must say that my frustration level with access is mounting but i know
that
once i get this fixed, it will do SO much to help with these reports that
excel is just incapeable of that it'll be worth the agg.

if you want to see this db, i can send it to you (it's under 200kb
zipped).
just email me at kaosyeti <(a.t.)> comcast <(d.o.t.)> net.

I made some WAGs regarding your data types. You didn't respond to this.
One
other possible issue is to make sure the name of the control is not also
the
name of a field.

Since you are attempting to use this as a control source, I assume the
field
expressions are from your report's record source. If that is the case then
you should probably remove the query name from the expression:
=DLookUp("[units]","qrysoldunits", "[salesperson]=""" & [Salesperson] &
"""
And [deptartment]=""" & [Department] & """ And [monthandyear] Between " &
[txtboxstartdate] & "And " & [txtboxenddate])

i tried the expression you gave me and it still returns an error. i
checked

[quoted text clipped - 22 lines]

must
have my criteria wrong. please help.