create a report based on conditions

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
 
A

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.
 
S

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.

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...
 
G

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

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...
 
S

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.

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...
 
G

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

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...
 
J

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
 
S

SylvieB

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...
 
G

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

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...
 
A

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

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

check box with required fields 3
Sum 4 Fields with conditions 4
Cascade queries 7
Expression in a report 3
Where to put code? 2
Use VBA Code to Assign Macro Buttons to use Different Queries for Source in a SubForm 0
conditions on a report 4
Pulling data to a report based on double filter 1

Top