3 column report

[JEPE]

Hi,

This is hard to explain....

I have a table containing all bills made.
There are 3 important fields:
invoice number
way_of_payment
value

the way_of_payment can have 3 values:
"C"
"O"
or empty

I must have a report based upon a querry so the final report should look
like:

invoice number in first column
and then (and this is tricky for me)
in the second column the field "value" if the way_of_payment is "C"
(third en 4th column remains empty)
if way_of_payment is "O" then the third column should contain the value
but the secodn and the 4th column must be empty
if way_of_payment is empty
second, 3th, 4th column are empty

How to create this querry and
finaly I want the sum of the second, 3th and 4th column in the report
footer...

Any help is welcome
Thanks

 

[John Spencer]

By your definition, the fourth column is ALWAYS going to be null (empty).

SELECT [Invoice Number]
, IIF(Way_of_Payment = "C",[Value],Null) as CPay
, IIF(Way_of_Payment = "O",[Value],Null) as OPay
, Null
FROM [YourTable]

Getting a sum in the report footer should be simple.

To do this in query design view, use a calculated field and type in the
following for Cpay. Do something similar for OPay.
Field: CPay: IIF(Way_of_Payment = "C",[Value],Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[JEPE]

Great John...
1 small extra:
You wrote:
By your definition, the fourth column is ALWAYS going to be null (empty).

In fact it is as follow:
if the way_of_payment is null then the column should display "not payed yet"

Is this possible?

Thanks

By your definition, the fourth column is ALWAYS going to be null (empty).

SELECT [Invoice Number]
, IIF(Way_of_Payment = "C",[Value],Null) as CPay
, IIF(Way_of_Payment = "O",[Value],Null) as OPay
, Null
FROM [YourTable]

Getting a sum in the report footer should be simple.

To do this in query design view, use a calculated field and type in the
following for Cpay. Do something similar for OPay.
Field: CPay: IIF(Way_of_Payment = "C",[Value],Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi,

This is hard to explain....

I have a table containing all bills made.
There are 3 important fields:
invoice number
way_of_payment
value

the way_of_payment can have 3 values:
"C"
"O"
or empty

I must have a report based upon a querry so the final report should
look like:

invoice number in first column
and then (and this is tricky for me)
in the second column the field "value" if the way_of_payment is "C"
(third en 4th column remains empty)
if way_of_payment is "O" then the third column should contain the
value but the secodn and the 4th column must be empty
if way_of_payment is empty
second, 3th, 4th column are empty

How to create this querry and
finaly I want the sum of the second, 3th and 4th column in the report
footer...

Any help is welcome
Thanks

 

[John Spencer]

Sure, the expression is

IIF(Way_of_Payment is Null,"Not paid yet",Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Great John...
1 small extra:
You wrote:
By your definition, the fourth column is ALWAYS going to be null (empty).

In fact it is as follow:
if the way_of_payment is null then the column should display "not payed
yet"

Is this possible?

Thanks

By your definition, the fourth column is ALWAYS going to be null (empty).

SELECT [Invoice Number]
, IIF(Way_of_Payment = "C",[Value],Null) as CPay
, IIF(Way_of_Payment = "O",[Value],Null) as OPay
, Null
FROM [YourTable]

Getting a sum in the report footer should be simple.

To do this in query design view, use a calculated field and type in
the following for Cpay. Do something similar for OPay.
Field: CPay: IIF(Way_of_Payment = "C",[Value],Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi,

This is hard to explain....

I have a table containing all bills made.
There are 3 important fields:
invoice number
way_of_payment
value

the way_of_payment can have 3 values:
"C"
"O"
or empty

I must have a report based upon a querry so the final report should
look like:

invoice number in first column
and then (and this is tricky for me)
in the second column the field "value" if the way_of_payment is "C"
(third en 4th column remains empty)
if way_of_payment is "O" then the third column should contain the
value but the secodn and the 4th column must be empty
if way_of_payment is empty
second, 3th, 4th column are empty

How to create this querry and
finaly I want the sum of the second, 3th and 4th column in the report
footer...

Any help is welcome
Thanks

 

[Marshall Barton]

Sure, the expression is

IIF(Way_of_Payment is Null,"Not paid yet",Null)

How about
Nz(Way_of_Payment,"Not paid yet")

or, better, just bind the text box to the Way_of_Payment
field and set its Format to
@;"Not paid yet"

 

[John Spencer]

(1) Since I started with one method, I chose to remain consistent.

(2) Almost viable, since they will show Way_of_Payment values no matter
what and the poster wanted the column to be "empty" unless the value was
null. Your choices would show "O", "C" or "Not Paid Yet".

Also, I should have checked that the value was null OR a zero-length
string. So the best solution might have been

IIF(Way_Of_Payment is Null or Way_Of_Payment <> "",
"Not Paid Yet",Null)

Although do to a quirk in the way IIF works, I think you could get away
with the following:
IIF(Way_of_Payment <> "",Null,"Not Paid Yet")

The quirk being that IIF returns the second argument if the first
argument is true; otherwise, it always (in my testing) returns the third
argument.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[JEPE]

my problem is solved
The synrax Marshall suggested didn't return the same results as yours...

Thanks for your kind help
JP, Belgium, Europe

Sure, the expression is

IIF(Way_of_Payment is Null,"Not paid yet",Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Great John...
1 small extra:
You wrote:
By your definition, the fourth column is ALWAYS going to be null (empty).

In fact it is as follow:
if the way_of_payment is null then the column should display "not
payed yet"

Is this possible?

Thanks

By your definition, the fourth column is ALWAYS going to be null
(empty).

SELECT [Invoice Number]
, IIF(Way_of_Payment = "C",[Value],Null) as CPay
, IIF(Way_of_Payment = "O",[Value],Null) as OPay
, Null
FROM [YourTable]

Getting a sum in the report footer should be simple.

To do this in query design view, use a calculated field and type in
the following for Cpay. Do something similar for OPay.
Field: CPay: IIF(Way_of_Payment = "C",[Value],Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


JEPE wrote:
Hi,

This is hard to explain....

I have a table containing all bills made.
There are 3 important fields:
invoice number
way_of_payment
value

the way_of_payment can have 3 values:
"C"
"O"
or empty

I must have a report based upon a querry so the final report should
look like:

invoice number in first column
and then (and this is tricky for me)
in the second column the field "value" if the way_of_payment is "C"
(third en 4th column remains empty)
if way_of_payment is "O" then the third column should contain the
value but the secodn and the 4th column must be empty
if way_of_payment is empty
second, 3th, 4th column are empty

How to create this querry and
finaly I want the sum of the second, 3th and 4th column in the
report footer...

Any help is welcome
Thanks

 

[Marshall Barton]

(2) Almost viable, since they will show Way_of_Payment values no matter
what and the poster wanted the column to be "empty" unless the value was
null. Your choices would show "O", "C" or "Not Paid Yet".

Good point John.

How about this format?
"";"Not Paid Yet"

 

[John Spencer]

Marshall,
As far as I know that will just go ahead and print the field contents. I just
ran a really quick test (Access 2003) and the contents of the field printed.
Interestingly, it also surpressed anything from printing if the field was null.

"";"Not Paid Yet" -printed the column value and if the column was null
nothing was printed.

@;"Not Paid Yet" -printed the column value and if the column was null printed
"Not Paid Yet" (without the quotes).

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

As far as I know that will just go ahead and print the field contents. I just
ran a really quick test (Access 2003) and the contents of the field printed.
Interestingly, it also surpressed anything from printing if the field was null.

"";"Not Paid Yet" -printed the column value and if the column was null
nothing was printed.

@;"Not Paid Yet" -printed the column value and if the column was null printed
"Not Paid Yet" (without the quotes).

Grrrr, You're right. I would have sworn that I had done
that some time in the past, but it must have been with a
number format. I even went back and checked it in A97 and
it's the same, proving, once again, how faulty my memory can
be.

Regardless of my memory's reliability and all other
considerations aside, I think this situation is a bug. Even
if they intentionally ignored a constant in the first format
part, they should not invalidate the Null part too.