conditional calculations (iif)

i have a report based off of a query that is created from user-input from a
form. i'm trying to add a text box to show the value of a field in a table
that's not part of the original query's design. so:

form "frmparameters" lets the user fill in 5 parameters to construct the
query.

query "qrycontactdata" is a listing of customer contacts, with fields for
salesperson, date, department, result and several yes/no checkboxes that
aren't important right now.

report "rptcontactdata" makes the whole thing nice and presentable but is
only (at this point) using data from qrycontactdata.

how can i create a text box in the report to show the value in field "units"
in table "tblsoldunits" when the salesperson field in tblsoldunits is the
same as the salesperson entry from frmparameters AND the department field in
tblsoldunits is the same as the department entry from frmparameters?

i'm happy to email/upload this db if anyone needs to see it to answer my
question. please keep in mind that i am intelligent but know next to nothing
about access (just heard about it 2 months ago for the first time) and have a
strong tendency to do things in access that make sense to me from my excel
experience (where i'm above-average proficiency). to make things worse, i
know absolutely nothing about vba so if your answer has vba code, please be
very specific. thank you.

wrote:

i have a report based off of a query that is created from user-input from a
form. i'm trying to add a text box to show the value of a field in a table
that's not part of the original query's design. so:

form "frmparameters" lets the user fill in 5 parameters to construct the
query.

query "qrycontactdata" is a listing of customer contacts, with fields for
salesperson, date, department, result and several yes/no checkboxes that
aren't important right now.

report "rptcontactdata" makes the whole thing nice and presentable but is
only (at this point) using data from qrycontactdata.

how can i create a text box in the report to show the value in field "units"
in table "tblsoldunits" when the salesperson field in tblsoldunits is the
same as the salesperson entry from frmparameters AND the department field in
tblsoldunits is the same as the department entry from frmparameters?


The first thing to try is see if there is a way to Join the
data from both tables. If you don't know what it means to
Join two tables in a query, create a new query and add both
tables. Then drag the related field(s) from one table to
its counterpart in the other table.

If your situation is too complicated for that approach, try
using the DLookup (or DSum, DCount, etc) function to
retrieve the desired data from the other table.

--
Marsh
MVP [MS Access]

i can't get the join to work out but i tried a number of dlook options and
the closest i got was this:

=DLookUp("[units]","qrysoldunits",[qrysoldunits]![salesperson]=
[qrycontactdata]![salesperson] And [qrysoldunits]![deptartment]=
[qrycontactdata]![department])

which returned the value in the first record of the units field of
qrysoldunits. the problem is that it didn't match the salesperson and it
showed the same value for both the new and used departments. the report is
grouped by salesperson 1st and department 2nd and the control is in the
department footer, btw.

any thoughts? thanks.

Marshall Barton wrote:
i have a report based off of a query that is created from user-input from a
form. i'm trying to add a text box to show the value of a field in a table
[quoted text clipped - 14 lines]
same as the salesperson entry from frmparameters AND the department field in
tblsoldunits is the same as the department entry from frmparameters?

The first thing to try is see if there is a way to Join the
data from both tables. If you don't know what it means to
Join two tables in a query, create a new query and add both
tables. Then drag the related field(s) from one table to
its counterpart in the other table.

If your situation is too complicated for that approach, try
using the DLookup (or DSum, DCount, etc) function to
retrieve the desired data from the other table.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200511/1

via AccessMonster.com wrote:

i can't get the join to work out but i tried a number of dlook options and
the closest i got was this:

=DLookUp("[units]","qrysoldunits",[qrysoldunits]![salesperson]=
[qrycontactdata]![salesperson] And [qrysoldunits]![deptartment]=
[qrycontactdata]![department])

which returned the value in the first record of the units field of
qrysoldunits. the problem is that it didn't match the salesperson and it
showed the same value for both the new and used departments. the report is
grouped by salesperson 1st and department 2nd and the control is in the
department footer, btw.


The syntax is not correct. Try this:

=DLookUp("units","qrysoldunits","salesperson=" &
Me!salesperson & " And department=" & Me!department)

That will only work if the salesperson and department fields
are a numeric type. If they are Text type fields:
=DLookUp("units","qrysoldunits","salesperson=""" &
Me!salesperson & """ And department=""" & Me!department &
"""")

--
Marsh
MVP [MS Access]

i tried your expression but it's still coming up as an error. it's also
prompting me to enter a value in a box labelled 'Me'.


Marshall Barton wrote:
i can't get the join to work out but i tried a number of dlook options and
the closest i got was this:
[quoted text clipped - 8 lines]
grouped by salesperson 1st and department 2nd and the control is in the
department footer, btw.

The syntax is not correct. Try this:

=DLookUp("units","qrysoldunits","salesperson=" &
Me!salesperson & " And department=" & Me!department)

That will only work if the salesperson and department fields
are a numeric type. If they are Text type fields:
=DLookUp("units","qrysoldunits","salesperson="" " &
Me!salesperson & """ And department=""" & Me!department &
"""")


--
Message posted via http://www.accessmonster.com

via AccessMonster.com wrote:

i tried your expression but it's still coming up as an error. it's also
prompting me to enter a value in a box labelled 'Me'.


Marshall Barton wrote:
i can't get the join to work out but i tried a number of dlook options and
the closest i got was this:
[quoted text clipped - 8 lines]
grouped by salesperson 1st and department 2nd and the control is in the
department footer, btw.

The syntax is not correct. Try this:

=DLookUp("units","qrysoldunits","salesperson=" &
Me!salesperson & " And department=" & Me!department)

That will only work if the salesperson and department fields
are a numeric type. If they are Text type fields:
=DLookUp("units","qrysoldunits","salesperson=" "" &
Me!salesperson & """ And department=""" & Me!department &
"""")


Somehow I was thinking the DLookup was in a VBA procedure.
For a text box expression get rid of the Me.

=DLookUp("units","qrysoldunits","salesperson=" &
salesperson & " And department=" & department)

You still haven't said if the fields are a numeric type or a
Text type??

--
Marsh
MVP [MS Access]

sorry... they're text. and =DLookUp("units","qrysoldunits","salesperson="
&
salesperson & " And department=" & department) didn't work out either. if a
vba solution is possible, i can go that route but please be very specific in
your answer (i know zero, about vba. i've only done as much as i've been
shown by others). thanks.


Marshall Barton wrote:
i tried your expression but it's still coming up as an error. it's also
prompting me to enter a value in a box labelled 'Me'.
[quoted text clipped - 15 lines]
Me!salesperson & """ And department=""" & Me!department &
"""")

Somehow I was thinking the DLookup was in a VBA procedure.
For a text box expression get rid of the Me.

=DLookUp("units","qrysoldunits","salesperson=" &
salesperson & " And department=" & department)

You still haven't said if the fields are a numeric type or a
Text type??


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200511/1

via AccessMonster.com wrote:

sorry... they're text. and =DLookUp("units","qrysoldunits","salesperson="
&
salesperson & " And department=" & department) didn't work out either. if a
vba solution is possible, i can go that route but please be very specific in
your answer (i know zero, about vba. i've only done as much as i've been
shown by others). thanks.


At this point, I don't see a need to use VBA for this.

As I said before, if they are Text type fields, then use
(dropping the Me.)

=DLookUp("units","qrysoldunits","salesperson=""" &
salesperson & """ And department=""" & department & """")

--
Marsh
MVP [MS Access]

still didn't work. i think the problem lies in a different direction. i've
got posts on 3 different web sites and i've gotten about 30 different dlookup
statements that haven't worked. i suspect that the problem is with the
format of one of my controls or something but i can't figure out what it is.
here is a clip from another post about this:

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.

i just can't see that 30 dlookup expressions are all wrong and have all
produced a simple error message w/o getting at all closer to an answer.

Marshall Barton wrote:
sorry... they're text. and =DLookUp("units","qrysoldunits","salesperson="
&
salesperson & " And department=" & department) didn't work out either. if a
vba solution is possible, i can go that route but please be very specific in
your answer (i know zero, about vba. i've only done as much as i've been
shown by others). thanks.

At this point, I don't see a need to use VBA for this.

As I said before, if they are Text type fields, then use
(dropping the Me.)

=DLookUp("units","qrysoldunits","salesperson="" " &
salesperson & """ And department=""" & department & """")


--
Message posted via http://www.accessmonster.com

I'm starting to have a problem with this entire thread.

I think you are being extremely presumptious when you say
"still didn't work" with, I presume, some expectation that I
can use that lack of specific information to solve your
problem. You must be getting an error message or some kind
of result and I need to know not only what values the
function is operating on, but also the result produced.

In reviewing ehis entire thread, I don't think we have
established exactly what the salesperson and department
items really are. Are they fields in the report's record
source query? You said before that they are Text type
fields, but I am wondering if maybe they are actually a
Lookup field that actually has a numeric ID.

How about doing a little debugging here? If you don't
already have them, add two text boxes, one bound to
salesperson and the other to department. Maybe seeing these
values will provide a clue.

It might also be helpful if you posted the report's record
source query and a short same of the data it returns.

The copy of someone else's reply in a different forum does
not seem relevant to me. I am not at all sure that it's
trying to address the same question as in this thread, but
if it is, I can not see what the format of a form combo box
has to do with anything in the report. You don't care how
the data is formatted, you just need to be aware of the data
type and value.
--
Marsh
MVP [MS Access]


via AccessMonster.com wrote:
still didn't work. i think the problem lies in a different direction. i've
got posts on 3 different web sites and i've gotten about 30 different dlookup
statements that haven't worked. i suspect that the problem is with the
format of one of my controls or something but i can't figure out what it is.
here is a clip from another post about this:

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.

i just can't see that 30 dlookup expressions are all wrong and have all
produced a simple error message w/o getting at all closer to an answer.

sorry... they're text. and =DLookUp("units","qrysoldunits","salesperson="
&
salesperson & " And department=" & department) didn't work out either. if a
vba solution is possible, i can go that route but please be very specific in
your answer (i know zero, about vba. i've only done as much as i've been
shown by others). thanks.

Marshall Barton wrote:
At this point, I don't see a need to use VBA for this.

As I said before, if they are Text type fields, then use
(dropping the Me.)

=DLookUp("units","qrysoldunits","salesperson=" "" &
salesperson & """ And department=""" & department & """")