create a report based on conditions

  • Thread starter Thread starter SylvieB
  • Start date Start date
S

SylvieB

I have a form that has those following fields: OwnerName, Title,
ProposalDate, AwardDate, Amount, DueDate, EstimatedAwardDate and
EstimatedAmount.
i need to create a report that will display the dates in those controls
labeled ProposalDate, AwardDate, Amount) with the following conditions:
if ProposalDate is null then put the value of DueDate in the field
proposalDate on the report
if AwardDate is null then put the value of EstimatedAwardDate in the field
Award date on the report.
if Amount is null then put the value of EstimatedAmount in the field Amount
on the report.
it does not sound difficult but for some reasons i'm having a hard time to
do that.
Thank you in advance for any help.
Sylvie
 
Use a query as the recordsource for the report. Create columns in the query
like:

PropDate: Iff(IsNull([ProposalDate], [DueDate], [ProposalDate])

do similar for the other fields. Now in your report, connect the textbox to
PropDate instead of ProposalDate. And like manner for the other fields.
 
Arvin
when i entered the expresssion below, it displays "expresssion you entered
has a function containing the wrong number of arguments"; any idea?
Thank you for your help.

Arvin Meyer said:
Use a query as the recordsource for the report. Create columns in the query
like:

PropDate: Iff(IsNull([ProposalDate], [DueDate], [ProposalDate])

do similar for the other fields. Now in your report, connect the textbox to
PropDate instead of ProposalDate. And like manner for the other fields.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


SylvieB said:
I have a form that has those following fields: OwnerName, Title,
ProposalDate, AwardDate, Amount, DueDate, EstimatedAwardDate and
EstimatedAmount.
i need to create a report that will display the dates in those controls
labeled ProposalDate, AwardDate, Amount) with the following conditions:
if ProposalDate is null then put the value of DueDate in the field
proposalDate on the report
if AwardDate is null then put the value of EstimatedAwardDate in the field
Award date on the report.
if Amount is null then put the value of EstimatedAmount in the field
Amount
on the report.
it does not sound difficult but for some reasons i'm having a hard time to
do that.
Thank you in advance for any help.
Sylvie
Click to expand...
Click to expand...
 
Sylvie,

There's an OOPS... change Iff to IIf

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Sylvie said:
Arvin
when i entered the expresssion below, it displays "expresssion you entered
has a function containing the wrong number of arguments"; any idea?
Thank you for your help.

Arvin Meyer said:
Use a query as the recordsource for the report. Create columns in the
query
like:

PropDate: Iff(IsNull([ProposalDate], [DueDate], [ProposalDate])

do similar for the other fields. Now in your report, connect the textbox
to
PropDate instead of ProposalDate. And like manner for the other fields.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


SylvieB said:
I have a form that has those following fields: OwnerName, Title,
ProposalDate, AwardDate, Amount, DueDate, EstimatedAwardDate and
EstimatedAmount.
i need to create a report that will display the dates in those controls
labeled ProposalDate, AwardDate, Amount) with the following conditions:
if ProposalDate is null then put the value of DueDate in the field
proposalDate on the report
if AwardDate is null then put the value of EstimatedAwardDate in the
field
Award date on the report.
if Amount is null then put the value of EstimatedAmount in the field
Amount
on the report.
it does not sound difficult but for some reasons i'm having a hard time
to
do that.
Thank you in advance for any help.
Sylvie
Click to expand...
Click to expand...
Click to expand...
 
Thank you Gina but it still does not work. I get the same error message. Any
idea why? Sorry to be a pain. I never used "IIF" in a query before.

Gina Whipp said:
Sylvie,

There's an OOPS... change Iff to IIf

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Sylvie said:
Arvin
when i entered the expresssion below, it displays "expresssion you entered
has a function containing the wrong number of arguments"; any idea?
Thank you for your help.

Arvin Meyer said:
Use a query as the recordsource for the report. Create columns in the
query
like:

PropDate: Iff(IsNull([ProposalDate], [DueDate], [ProposalDate])

do similar for the other fields. Now in your report, connect the textbox
to
PropDate instead of ProposalDate. And like manner for the other fields.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I have a form that has those following fields: OwnerName, Title,
ProposalDate, AwardDate, Amount, DueDate, EstimatedAwardDate and
EstimatedAmount.
i need to create a report that will display the dates in those controls
labeled ProposalDate, AwardDate, Amount) with the following conditions:
if ProposalDate is null then put the value of DueDate in the field
proposalDate on the report
if AwardDate is null then put the value of EstimatedAwardDate in the
field
Award date on the report.
if Amount is null then put the value of EstimatedAmount in the field
Amount
on the report.
it does not sound difficult but for some reasons i'm having a hard time
to
do that.
Thank you in advance for any help.
Sylvie
Click to expand...
Click to expand...
Click to expand...
 
Oops, I missed something also, closing paranthesis... copy/paste the below

PropDate: IIf(IsNull([ProposalDate]), [DueDate], [ProposalDate])


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SylvieB said:
Thank you Gina but it still does not work. I get the same error message.
Any
idea why? Sorry to be a pain. I never used "IIF" in a query before.

Gina Whipp said:
Sylvie,

There's an OOPS... change Iff to IIf

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Sylvie said:
Arvin
when i entered the expresssion below, it displays "expresssion you
entered
has a function containing the wrong number of arguments"; any idea?
Thank you for your help.

:

Use a query as the recordsource for the report. Create columns in the
query
like:

PropDate: Iff(IsNull([ProposalDate], [DueDate], [ProposalDate])

do similar for the other fields. Now in your report, connect the
textbox
to
PropDate instead of ProposalDate. And like manner for the other
fields.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I have a form that has those following fields: OwnerName, Title,
ProposalDate, AwardDate, Amount, DueDate, EstimatedAwardDate and
EstimatedAmount.
i need to create a report that will display the dates in those
controls
labeled ProposalDate, AwardDate, Amount) with the following
conditions:
if ProposalDate is null then put the value of DueDate in the field
proposalDate on the report
if AwardDate is null then put the value of EstimatedAwardDate in the
field
Award date on the report.
if Amount is null then put the value of EstimatedAmount in the field
Amount
on the report.
it does not sound difficult but for some reasons i'm having a hard
time
to
do that.
Thank you in advance for any help.
Sylvie
Click to expand...
Click to expand...
Click to expand...
 
PropDate: IIF(IsNull([ProposalDate]), [DueDate], [ProposalDate])

Note the changes:
IIF not IFF
Closing parens after IsNull([ProposalDate]

In queries, I prefer to test for nulls this way
PropDate: IIF([ProposalDate] Is Null, [DueDate], [ProposalDate])


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
You guys are awesome. Thank you very much ALL of you for your help. It works
great now.

John Spencer said:
PropDate: IIF(IsNull([ProposalDate]), [DueDate], [ProposalDate])

Note the changes:
IIF not IFF
Closing parens after IsNull([ProposalDate]

In queries, I prefer to test for nulls this way
PropDate: IIF([ProposalDate] Is Null, [DueDate], [ProposalDate])


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Use a query as the recordsource for the report. Create columns in the query
like:

PropDate: Iff(IsNull([ProposalDate], [DueDate], [ProposalDate])

do similar for the other fields. Now in your report, connect the textbox to
PropDate instead of ProposalDate. And like manner for the other fields.
Click to expand...
Click to expand...
 
You're welcome!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SylvieB said:
You guys are awesome. Thank you very much ALL of you for your help. It
works
great now.

John Spencer said:
PropDate: IIF(IsNull([ProposalDate]), [DueDate], [ProposalDate])

Note the changes:
IIF not IFF
Closing parens after IsNull([ProposalDate]

In queries, I prefer to test for nulls this way
PropDate: IIF([ProposalDate] Is Null, [DueDate], [ProposalDate])


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Use a query as the recordsource for the report. Create columns in the
query
like:

PropDate: Iff(IsNull([ProposalDate], [DueDate], [ProposalDate])

do similar for the other fields. Now in your report, connect the
textbox to
PropDate instead of ProposalDate. And like manner for the other fields.
Click to expand...
Click to expand...
Click to expand...
 
Thanks to both John and Gina. Sometimes typing too fast into a newsreader
leads to errors. Glad you guy caught them.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

SylvieB said:
You guys are awesome. Thank you very much ALL of you for your help. It
works
great now.

John Spencer said:
PropDate: IIF(IsNull([ProposalDate]), [DueDate], [ProposalDate])

Note the changes:
IIF not IFF
Closing parens after IsNull([ProposalDate]

In queries, I prefer to test for nulls this way
PropDate: IIF([ProposalDate] Is Null, [DueDate], [ProposalDate])


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Use a query as the recordsource for the report. Create columns in the
query
like:

PropDate: Iff(IsNull([ProposalDate], [DueDate], [ProposalDate])

do similar for the other fields. Now in your report, connect the
textbox to
PropDate instead of ProposalDate. And like manner for the other fields.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top