conditional calculations (iif)

I’m not trying to be difficult and I certainly don’t think I’m being
presumptuous by thinking that you can understand what’s happening when I say
that it ‘still didn’t work’. Believe it or not, I’m trying to be as specific
as I can be. In fact, I thought I was going on and on with details that you
wouldn’t need, in a struggling effort to get my point across.

As for the error, there is no specific message that pops up, but just a
#error? statement on the report where the control is located. The results
vary based on who’s dlookup statement that I’m currently trying but if I have
said it didn’t work, then nothing changed. I thought I was communicating the
events that were occurring when the report runs when I said that It’s pulling
the first record of the query but that it wasn’t matching the salespeople up
properly. Also, when I mentioned the pop up prompt for the ‘me’ field that
occurred earlier.

The salesperson and department fields are controls in the report that was
created with the wizard. The recordsource for the whole report is
qrycontactdata. The only information that is not in that query is the units
that I’m trying to use dlookup to get. The query is based on tblcontactdata
which is formatted as text for those fields. The only place that I can see a
potential format problem is the controls in formparameters which prompts the
user for which salesperson to run the report on (it will also accept a null
value for this, in which case the report runs on all salespeople). If any of
these controls are lookups, then the wizard did that and I can’t see the
expression in the control souce.

I created two new textboxes in the department line of the report, one for
salesperson and one for department. Both correctly displayed the name of the
person and department for each page of the report (each page being a
different salesperson, with new, used and total broken out into thirds on
each page).

The report’s recordsource property is qrycontactdata. That’s it. The query
will turn the results that look like this (the field names have been
abbreviated here because they wouldn't fit neatly across this post):

qrycontactdata
Slsperson UpSource Dept Demo WriteUp Mgr
Result Date
John Doe Walk-In Used Yes Yes
Yes Working 10/1/2005
Jane Doe Walk-In New No No
No Working 10/1/2005
John Smith Walk-In New Yes No
Yes Working 10/1/2005
Jane Smith Walk-In Used Yes Yes
Yes Working 10/1/2005

For good measure, qrysoldunits returns this result:

qrysoldunits
salesperson units monthandyear
deptartment
John Doe 2 10-2005
new
John Doe 4 10-2005
used
Jane Doe 5 10-2005
new
John Smith 2 10-2005
new

Qrysoldunits will have 2 records for each salesperson per month, max. it may
possibly have only 1. when looking at the raw data, both queries are correct.
They pull the correct data for the parameters entered in the form that
creates them.

As for the copy I inserted on my last post. It wasn’t someone else’s opinion
or question. It was my own. I just wanted you to be aware of the fact that
I’m asking this same question elsewhere and have not met with success. When
I figured that it’s about 5 people per website and 3 websites later, that 15
people who are minimally advanced users of ms access and many of whom are
probably considered experts, probably can’t all be getting this wrong in the
same way without the fault lying elsewhere. I was just trying to suggest and
alternative direction to see if that would spark an ‘ah-ha’ from you to let
me know what I haven’t got right in my form/query/table/report that is
probably as simple as a property on a control somewhere or something.

Truthfully, I’ve been working on this db for a month and prior to that have
never heard of access, let alone used it for anything. I’ve been trying on
my own for over a week to get this one thing and have met with zero success.
I can say that I’m putting about 1/5-2 hours per day into it at work. After
a week I decided to solicit help from the community because I was stuck. Now,
if after all that you can say that you’ve got a problem with this entire
thread, I am comfortable with you not responding to it again. The last I
checked, this forum was for the express purpose of helping people like me
with problems like this. If that’s not correct, let me know and I’ll seek
help elsewhere. If my ability to communicate my problem is ineffective, I’m
sorry, but it’s not do to any lack of effort on my part to try to get my
point across. If I’m not grasping these concepts as quickly or easily as you
feel that I should, then perhaps your time would be better spent taking a
learning annex course in teaching rather than blithely spouting off about how
you’re having a problem with my problem. Honestly, I still would like your
help and anyone else who’s reading this but I can do with a little less
arrogance, please.

Greg


Marshall Barton wrote:
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.
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
[quoted text clipped - 27 lines]
=DLookUp("units","qrysoldunits","salesperson="" " &
salesperson & """ And department=""" & department & """")

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

Based on the sample data you posted, I don't see how the
DLookup I posted:

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

can generate #Error. Nor can I see how it will return the
first record in qrysoldunits regardless of the salesperson
or department. It will return Null (nothing) if there is no
matching record or it should work as desired.

As far as I can tell, as long as the queries are working by
themselves, the form has nothing to do with the DLookup.

When I asked about Lookup **fields** before (not a control
on a form or report), I was concerned that the query field
has a Lookup DisplayControl property set to ComboBox. If it
were, it might explain all the trouble we're having.
--
Marsh
MVP [MS Access]


via AccessMonster.com wrote:
[]
As for the error, there is no specific message that pops up, but just a
#error? statement on the report where the control is located.
[]
The salesperson and department fields are controls in the report that was
created with the wizard. The recordsource for the whole report is
qrycontactdata. The only information that is not in that query is the units
that I’m trying to use dlookup to get. The query is based on tblcontactdata
which is formatted as text for those fields. The only place that I can see a
potential format problem is the controls in formparameters which prompts the
user for which salesperson to run the report on (it will also accept a null
value for this, in which case the report runs on all salespeople). If any of
these controls are lookups, then the wizard did that and I can’t see the
expression in the control souce.

I created two new textboxes in the department line of the report, one for
salesperson and one for department. Both correctly displayed the name of the
person and department for each page of the report (each page being a
different salesperson, with new, used and total broken out into thirds on
each page).

The report’s recordsource property is qrycontactdata. That’s it. The query
will turn the results that look like this (the field names have been
abbreviated here because they wouldn't fit neatly across this post):

qrycontactdata
Slsperson UpSource Dept Demo WriteUp Mgr
Result Date
John Doe Walk-In Used Yes Yes
Yes Working 10/1/2005
Jane Doe Walk-In New No No
No Working 10/1/2005
John Smith Walk-In New Yes No
Yes Working 10/1/2005
Jane Smith Walk-In Used Yes Yes
Yes Working 10/1/2005

For good measure, qrysoldunits returns this result:

qrysoldunits
salesperson units monthandyear
deptartment
John Doe 2 10-2005
new
John Doe 4 10-2005
used
Jane Doe 5 10-2005
new
John Smith 2 10-2005
new

Qrysoldunits will have 2 records for each salesperson per month, max. it may
possibly have only 1. when looking at the raw data, both queries are correct.
They pull the correct data for the parameters entered in the form that
creates them.

i really hope that you don't have a blood pressure problem (or for that
matter, have a passion for letter bombs and my home address). the problem
apparently is (and has been) that the form that lets the user select the
parameters has a wee bit of code on it. when you click the ok command button,
it opens both queries, the report, then closes the queries, then itself.
this was supposed to allow a user to use the form to set parameters, then
view the report on the screen, printing what pages they wanted. i deleted
the code that called for the queries to close so that i could see them (to
see if they had the wrong data) and i mistakenly deleted the line that closes
the form as well. when the form was still opened, the report worked fine.
i'm getting all the numbers in all the right places.

i'm truly sorry to put you (and everyone else) through all of this. my
frustration level has mounted over this last week with this thing and i think
i started to take it all a little personally.

if you're so inclined, what would be the correct code to put on the command
'ok' button on my parameters form to have it run both queries and then the
report (on the screen) and then close everything except the report. or is
this even possible? thanks.

Marshall Barton wrote:
Based on the sample data you posted, I don't see how the
DLookup I posted:

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

can generate #Error. Nor can I see how it will return the
first record in qrysoldunits regardless of the salesperson
or department. It will return Null (nothing) if there is no
matching record or it should work as desired.

As far as I can tell, as long as the queries are working by
themselves, the form has nothing to do with the DLookup.

When I asked about Lookup **fields** before (not a control
on a form or report), I was concerned that the query field
has a Lookup DisplayControl property set to ComboBox. If it
were, it might explain all the trouble we're having.
[]
As for the error, there is no specific message that pops up, but just a
#error? statement on the report where the control is located.
[]
The salesperson and department fields are controls in the report that was
created with the wizard. The recordsource for the whole report is
[quoted text clipped - 47 lines]
They pull the correct data for the parameters entered in the form that
creates them.

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

via AccessMonster.com wrote:

i really hope that you don't have a blood pressure problem (or for that
matter, have a passion for letter bombs and my home address). the problem
apparently is (and has been) that the form that lets the user select the
parameters has a wee bit of code on it. when you click the ok command button,
it opens both queries, the report, then closes the queries, then itself.
this was supposed to allow a user to use the form to set parameters, then
view the report on the screen, printing what pages they wanted. i deleted
the code that called for the queries to close so that i could see them (to
see if they had the wrong data) and i mistakenly deleted the line that closes
the form as well. when the form was still opened, the report worked fine.
i'm getting all the numbers in all the right places.
[]
if you're so inclined, what would be the correct code to put on the command
'ok' button on my parameters form to have it run both queries and then the
report (on the screen) and then close everything except the report. or is
this even possible? thanks.


Great news! I'm sure glad you figured that out because I
didn't have a clue how the form integrated into the picture.

Note that when the report's (and those DLookups) queries are
dependent on the form for their filtering criteria, the form
must stay open for the duration. The earliest the form can
be closed is in the report's Close event. The form may make
itself invisible if you do not want to see it on the screen:
Me.Visible = False

Except for debugging purposes, I see no reason why you
should open the queries. I suspect that you should remove
that code in the production version.

I think the only code you need in the button's click event
is to open the report and optionally make the form
invisible.

--
Marsh
MVP [MS Access]

you were right about the code to open the queries. i removed that code
entirely and all i have is to hide the form and run the report. it works
great now. thanks again.

Marshall Barton wrote:
i really hope that you don't have a blood pressure problem (or for that
matter, have a passion for letter bombs and my home address). the problem
[quoted text clipped - 7 lines]
the form as well. when the form was still opened, the report worked fine.
i'm getting all the numbers in all the right places.
[]
if you're so inclined, what would be the correct code to put on the command
'ok' button on my parameters form to have it run both queries and then the
report (on the screen) and then close everything except the report. or is
this even possible? thanks.

Great news! I'm sure glad you figured that out because I
didn't have a clue how the form integrated into the picture.

Note that when the report's (and those DLookups) queries are
dependent on the form for their filtering criteria, the form
must stay open for the duration. The earliest the form can
be closed is in the report's Close event. The form may make
itself invisible if you do not want to see it on the screen:
Me.Visible = False

Except for debugging purposes, I see no reason why you
should open the queries. I suspect that you should remove
that code in the production version.

I think the only code you need in the button's click event
is to open the report and optionally make the form
invisible.


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

That's good to hear and thank you for letting me know the
issue is resolved.
--
Marsh
MVP [MS Access]


via AccessMonster.com wrote:

you were right about the code to open the queries. i removed that code
entirely and all i have is to hide the form and run the report. it works
great now. thanks again.

Marshall Barton wrote:
i really hope that you don't have a blood pressure problem (or for that
matter, have a passion for letter bombs and my home address). the problem
[quoted text clipped - 7 lines]
the form as well. when the form was still opened, the report worked fine.
i'm getting all the numbers in all the right places.
[]
if you're so inclined, what would be the correct code to put on the command
'ok' button on my parameters form to have it run both queries and then the
report (on the screen) and then close everything except the report. or is
this even possible? thanks.

Great news! I'm sure glad you figured that out because I
didn't have a clue how the form integrated into the picture.

Note that when the report's (and those DLookups) queries are
dependent on the form for their filtering criteria, the form
must stay open for the duration. The earliest the form can
be closed is in the report's Close event. The form may make
itself invisible if you do not want to see it on the screen:
Me.Visible = False

Except for debugging purposes, I see no reason why you
should open the queries. I suspect that you should remove
that code in the production version.

I think the only code you need in the button's click event
is to open the report and optionally make the form
invisible.