create a report based on conditions

[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

 

[Arvin Meyer [MVP]]

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.

 

[Sylvie]

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

 

[Gina Whipp]

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

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

 

[SylvieB]

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.

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

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

 

[Gina Whipp]

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

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.

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

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

 

[John Spencer]

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

 

[SylvieB]

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

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.

 

[Gina Whipp]

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

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

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.

 

[Arvin Meyer [MVP]]

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

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

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.