Generating Reports

[Guest]

Hello

I am trying to generate a report where some of the information contained in
the report is physically data entered, with other fields to list a status
based on IIF statements or sum statements in a form.

Anything data entered on the form is appearing ok on the report, however the
other fields contining IFF or sum statements remain blank.

How can I fix this??

Thanking you in advance

Sam

 

[Steve Schapel]

Sam,

If you mean that you have calculated controls on a form, and you want
the same information on your report, I guess there are two basic
approaches you could take. One would be to replicate the same
functionality on the report, i.e. put unbound textboxes on the report,
and enter expressions in their Control Source properties to do the same
calculations as you have on the form. Or you could do the calculations
in the query that the report is based on. A third option, which is
probably not the best one, would be to refer to the values of the form
controls from the report. This assumes the form is open at the time
that the report is printed. You would use Control Source like this...
=[Forms]![NameOfForm]![NameOfCalculatedControl]

If you need more specific help with this, please post back with more
precise details, with examples.

 

[Guest]

Thanks for the help Steve Report is now showing follow up status as per advice

Just one more question....

I want the report to eliminate any follow up comments that show a status as
being "OK" (there are various follow up prompts, however an "OK" Status
requires no action) How do I eliminate any records on the report that show a
follow up status of "OK"???

Thanks again.

Sam,

If you mean that you have calculated controls on a form, and you want
the same information on your report, I guess there are two basic
approaches you could take. One would be to replicate the same
functionality on the report, i.e. put unbound textboxes on the report,
and enter expressions in their Control Source properties to do the same
calculations as you have on the form. Or you could do the calculations
in the query that the report is based on. A third option, which is
probably not the best one, would be to refer to the values of the form
controls from the report. This assumes the form is open at the time
that the report is printed. You would use Control Source like this...
=[Forms]![NameOfForm]![NameOfCalculatedControl]

If you need more specific help with this, please post back with more
precise details, with examples.

--
Steve Schapel, Microsoft Access MVP

Hello

I am trying to generate a report where some of the information contained in
the report is physically data entered, with other fields to list a status
based on IIF statements or sum statements in a form.

Anything data entered on the form is appearing ok on the report, however the
other fields contining IFF or sum statements remain blank.

How can I fix this??

Thanking you in advance

Sam

 

[Steve Schapel]

Sam,

Do you mean that you have a field named 'Status'? Probably the easiest
way is to put a criteria of <>"OK" in the Status field of the query that
the report is based on. Is that ok?

 

[Guest]

Steve

Unfortunately no. I had already tried that. When you execute the query, a
pop up box appears asking for a value for Exp1 (Which is counting days in
status) The Days In status field is the driver for the "Follow Up" Field
which contains the code to prompt a follow up.

Hope this makes sense?

 

[Steve Schapel]

Sam,

Sorry, that has gone over my head, I'm afraid. Maybe if you could
copy/paste the SQL view of the report's record source query into your
reply, it might help me to see what you are working with.

 

[Guest]

Steve

I may sound like a dummy, but I have tried to access the sql code for this
report, but nothing comes up???

I am not sure if I explained myself correctly the first time, so I will try
again...

I have a field called "follow up" This field in the forms section of the
data base. The follow up field will prompt ther user to follow up the staus
of a loan application . E.G. Follow up return of contracts.... or..... Send
application to underwriter for assessment. The ultimate answer that the user
wants to see is that follow up status of "OK" this means that no action is
required for that customer (and therefore should not appear on the report)

PLEASE NOTE THAT THESE FOLLOW UP STATUS'S ARE GENERATED USING THE IIF
STATEMENT IN THE FORM AND ARE BASED ON THE STATUS OF THE LOAN APPLICATION AND
HOW LONG THE LOAN APPLICATION HAS BEEN IN THIS STATUS.

So basically, what I need the report to do is show all follow up status
appart from the status of "OK"

Hopefuilly that makes sense

Thanks for your assistance.

 

[Steve Schapel]

Sam,

Ahhhh... ok! Sorry, I failed to grasp the bit about the Status being
calculated on the form.

Well, maybe it will help if I point this out... Forms don't have
fields. Forms don't have data. Fields are in tables. Data is in
fields, in tables. Forms provide an avenue for the display of the data,
in controls.

So, as I understand it, you are trying to generate a report. Well, if
you have a calculated control on a form, you can't really moderate the
data printed to the report based on the data shown in the calculated
control on the form. You need to construct the dataset that the report
will use as its record source, by reference back to the data... and the
data is in the table, not the form.

You haven't provided enough information for anyone to give a specific
response, but this is how I would imagine it would go... Your report
is based on a query (or a table, but most typically on a query). You
can see what this is by looking at the Record Source property in design
view of the report. Probably, it will be possible to use a calculated
field in the query to return the same 'Status' value in the query as you
have done in the calculated control on the form. Assuming this is
correct, then you can use the criteria in the query to exclude the "OK"
records from the report.

If you still need more help with this, go to the design view of the
query (which the report is based on), select SQL from teh View menu, and
copy/paste that to your reply. And also give the expression you used on
the form to get the Status.

 

[Guest]

Thanks for your assistance Steve. I finally know how to pull the code!!!

Here it is:-

SELECT [Data Entry].Lender, [Data Entry].[Application ID], [Data
Entry].[Customer Name], [Data Entry].Status, Date()-[Data Entry]![Date Of
Status] AS Expr1, IIf([Status]="W" And [Expr1]>30,"Customer Has Not Returned
Contracts After 30 Days. Please Cancel Application & Send F70
Letter",IIf([status]="W" And [Expr1]>7,"No Contracts Returned After 1 Week.
Please Send F70 Letter",IIf([Status]="W" And [Expr1]>4,"Contact Customer To
Chase Up Return Of Contracts",IIf([Status]="X" And [Expr1]>3,"Chase Up
Employment Vero With Admin Team",IIf([Status]="M" And [Expr1]>0,"Make
Recommendation & Forward File To Underwriter",IIf([Status]="AD" And
[Expr1]>0,"Chase Up Approval/Declinal From Underwriting
Team",IIf([Status]="N" And [Expr1]>7,"No Response From Customer in Last 7
Days. Cancel Application & Send F70 Letter",IIf([Status]="N" And
[Expr1]>0,"Follow Up On Sale With Customer",IIf([Status]="P" And
[Expr1]>0,"Follow Up On Production Of Contracts With Admin
Team",IIf([Status]="CA","Cancelled
Application",IIf([Status]="RJ","Application Declined",IIf([Status]="HO","Hand
Off","OK")))))))))))) AS Expr2, [Data Entry].[Notes/ Comments], [Data
Entry].[Date Of Status]
FROM [Data Entry]
WHERE ((([Data Entry].Status)="X" Or ([Data Entry].Status)="M" Or ([Data
Entry].Status)="AD" Or ([Data Entry].Status)="N" Or ([Data Entry].Status)="P"
Or ([Data Entry].Status)="W"))
ORDER BY [Data Entry].Lender DESC , [Data Entry].Status DESC , [Data
Entry].[Date Of Status] DESC;


Thanks Again!

 

[Steve Schapel]

Sam,

No I am *really* confused! I thought you said that Status was a value
that you had calculated on a form? And now, from your SQL, it appears
that you have a Table or Query named [Data Entry] which contains a field
called Status, which you are trying to use for the report. Am I right?

Well, for one thing, as far as I know there is a maximum of 7 "levels"
for nested IIf() functions in an expression, and you have about 10, so I
would imagine that this will not work. Does it? If you run the query
(click the button with the red ! icon on the toolbar), does it return
the data you expect? If not, what do you get?

 

[Guest]

Steve

This code does run from a form, however the report would not recognise the
output from the form. When I placed the field named "Follow Up" into the
query table, it returned no value, so you advised to do the following in a
previous post as follows:-

If you mean that you have calculated controls on a form, and you want
the same information on your report, I guess there are two basic
approaches you could take. One would be to replicate the same
functionality on the report, i.e. put unbound textboxes on the report,
and enter expressions in their Control Source properties to do the same
calculations as you have on the form. Or you could do the calculations
in the query that the report is based on. A third option, which is
probably not the best one, would be to refer to the values of the form
controls from the report. This assumes the form is open at the time
that the report is printed. You would use Control Source like this...
=[Forms]![NameOfForm]![NameOfCalculatedControl]

Yes it actually works just fine!!!

Sam,

No I am *really* confused! I thought you said that Status was a value
that you had calculated on a form? And now, from your SQL, it appears
that you have a Table or Query named [Data Entry] which contains a field
called Status, which you are trying to use for the report. Am I right?

Well, for one thing, as far as I know there is a maximum of 7 "levels"
for nested IIf() functions in an expression, and you have about 10, so I
would imagine that this will not work. Does it? If you run the query
(click the button with the red ! icon on the toolbar), does it return
the data you expect? If not, what do you get?

--
Steve Schapel, Microsoft Access MVP

Thanks for your assistance Steve. I finally know how to pull the code!!!

Here it is:-

SELECT [Data Entry].Lender, [Data Entry].[Application ID], [Data
Entry].[Customer Name], [Data Entry].Status, Date()-[Data Entry]![Date Of
Status] AS Expr1, IIf([Status]="W" And [Expr1]>30,"Customer Has Not Returned
Contracts After 30 Days. Please Cancel Application & Send F70
Letter",IIf([status]="W" And [Expr1]>7,"No Contracts Returned After 1 Week.
Please Send F70 Letter",IIf([Status]="W" And [Expr1]>4,"Contact Customer To
Chase Up Return Of Contracts",IIf([Status]="X" And [Expr1]>3,"Chase Up
Employment Vero With Admin Team",IIf([Status]="M" And [Expr1]>0,"Make
Recommendation & Forward File To Underwriter",IIf([Status]="AD" And
[Expr1]>0,"Chase Up Approval/Declinal From Underwriting
Team",IIf([Status]="N" And [Expr1]>7,"No Response From Customer in Last 7
Days. Cancel Application & Send F70 Letter",IIf([Status]="N" And
[Expr1]>0,"Follow Up On Sale With Customer",IIf([Status]="P" And
[Expr1]>0,"Follow Up On Production Of Contracts With Admin
Team",IIf([Status]="CA","Cancelled
Application",IIf([Status]="RJ","Application Declined",IIf([Status]="HO","Hand
Off","OK")))))))))))) AS Expr2, [Data Entry].[Notes/ Comments], [Data
Entry].[Date Of Status]
FROM [Data Entry]
WHERE ((([Data Entry].Status)="X" Or ([Data Entry].Status)="M" Or ([Data
Entry].Status)="AD" Or ([Data Entry].Status)="N" Or ([Data Entry].Status)="P"
Or ([Data Entry].Status)="W"))
ORDER BY [Data Entry].Lender DESC , [Data Entry].Status DESC , [Data
Entry].[Date Of Status] DESC;


Thanks Again!

 

[Steve Schapel]

Sam,

I still can't really follow what you are doing. As far as I can see,
you have not previously mentioned anything about "Follow Up". And I
can't see how you are selecting the reords for your report where Status
<> "OK". But anyway, I am happy to know that you are getting it to work
for you.

 

[Guest]

Steve

The Code is working ok, but the <>"OK" command does not work on the report.
The report still shows the status of "OK" when It should be eliminated.

Sorry for all of the confusion.

Sam,

I still can't really follow what you are doing. As far as I can see,
you have not previously mentioned anything about "Follow Up". And I
can't see how you are selecting the reords for your report where Status
<> "OK". But anyway, I am happy to know that you are getting it to work
for you.

--
Steve Schapel, Microsoft Access MVP

Steve

This code does run from a form, however the report would not recognise the
output from the form. When I placed the field named "Follow Up" into the
query table, it returned no value, so you advised to do the following in a
previous post as follows:-

If you mean that you have calculated controls on a form, and you want
the same information on your report, I guess there are two basic
approaches you could take. One would be to replicate the same
functionality on the report, i.e. put unbound textboxes on the report,
and enter expressions in their Control Source properties to do the same
calculations as you have on the form. Or you could do the calculations
in the query that the report is based on. A third option, which is
probably not the best one, would be to refer to the values of the form
controls from the report. This assumes the form is open at the time
that the report is printed. You would use Control Source like this...
=[Forms]![NameOfForm]![NameOfCalculatedControl]

Yes it actually works just fine!!!