Printing iif results in a report

  • Thread starter Thread starter porboy423
  • Start date Start date
P

porboy423

I am having difficulty printing the results of an iif expression used on a
form. I would like to be able to export the results of the expression into a
single field in a table called "location" for further sorting, but would
settle for just being able to print what it shows in the form.
I set up an inventory database using check boxes for each storage location
because one account may have items stored in several places. I am trying to
make a report that lists either the account number with all the locations
related to the account; or, a list of account numbers sorted by storage
locations for retrieving several items for several accounts at once.
The iif expression is as follows:
=IIf([A]," A,","") & IIf([A1]," A-1,","") & IIf([A2]," A-2,","") &
IIf([A3]," A-3,","") & IIf(," B,","") & IIf([B1]," B-1,","") & IIf([C],"
C,","") & IIf([C1]," C-1,","") & IIf([D]," D,","") & IIf([D1]," D-1,","") &
IIf([E]," E,","") & IIf([F]," F,","") & IIf([G]," G,","") & IIf([G1],"
G-1,","") & IIf([H]," H,","") & IIf([H1]," H-1,","") & IIf(," I,","") &
IIf([I1]," I-1,","") & IIf([J]," J,","") & IIf([J1]," J-1,","") & IIf([K],"
K,","") & IIf([L]," L,","") & IIf([M]," M,","") & IIf([N]," N,","") &
IIf([O]," O,","") & IIf([P]," P,","") & IIf([P1]," P-1,","") & IIf([R],"
R,","") & IIf(," S,","") & IIf([MB1]," MB1,","") & IIf([MB2]," MB2,","") &
IIf([MB3]," MB3,","") & IIf([MB4]," MB4,","")

Any help would be greatly appreciated.
 
Can't you just put this expression in your query that drives the report?

Location: IIf([A]," A,","") & IIf([A1]," A-1,","") & IIf([A2]," A-2,","") &
IIf([A3]," A-3,","") & IIf(," B,","") & IIf([B1]," B-1,","") &
IIf([C],"
C,","") & IIf([C1]," C-1,","") & IIf([D]," D,","") & IIf([D1]," D-1,","")
&
IIf([E]," E,","") & IIf([F]," F,","") & IIf([G]," G,","") & IIf([G1],"
G-1,","") & IIf([H]," H,","") & IIf([H1]," H-1,","") & IIf(," I,","") &
IIf([I1]," I-1,","") & IIf([J]," J,","") & IIf([J1]," J-1,","") &
IIf([K],"
K,","") & IIf([L]," L,","") & IIf([M]," M,","") & IIf([N]," N,","") &
IIf([O]," O,","") & IIf([P]," P,","") & IIf([P1]," P-1,","") & IIf([R],"
R,","") & IIf(," S,","") & IIf([MB1]," MB1,","") & IIf([MB2],"
MB2,","") &
IIf([MB3]," MB3,","") & IIf([MB4]," MB4,","")
Click to expand...


Then in the report you have a field called "location".
You might need to add the table name next to the field name (if the query
has multiple tables)
example: [tblInventory].[A1]

Maybe I misunderstood your issue?
Mark


porboy423 said:
I am having difficulty printing the results of an iif expression used on a
form. I would like to be able to export the results of the expression
into a
single field in a table called "location" for further sorting, but would
settle for just being able to print what it shows in the form.
I set up an inventory database using check boxes for each storage location
because one account may have items stored in several places. I am trying
to
make a report that lists either the account number with all the locations
related to the account; or, a list of account numbers sorted by storage
locations for retrieving several items for several accounts at once.
The iif expression is as follows:
=IIf([A]," A,","") & IIf([A1]," A-1,","") & IIf([A2]," A-2,","") &
IIf([A3]," A-3,","") & IIf(," B,","") & IIf([B1]," B-1,","") &
IIf([C],"
C,","") & IIf([C1]," C-1,","") & IIf([D]," D,","") & IIf([D1]," D-1,","")
&
IIf([E]," E,","") & IIf([F]," F,","") & IIf([G]," G,","") & IIf([G1],"
G-1,","") & IIf([H]," H,","") & IIf([H1]," H-1,","") & IIf(," I,","") &
IIf([I1]," I-1,","") & IIf([J]," J,","") & IIf([J1]," J-1,","") &
IIf([K],"
K,","") & IIf([L]," L,","") & IIf([M]," M,","") & IIf([N]," N,","") &
IIf([O]," O,","") & IIf([P]," P,","") & IIf([P1]," P-1,","") & IIf([R],"
R,","") & IIf(," S,","") & IIf([MB1]," MB1,","") & IIf([MB2],"
MB2,","") &
IIf([MB3]," MB3,","") & IIf([MB4]," MB4,","")

Any help would be greatly appreciated.
Click to expand...
 
Thank you for the suggestion. I tried that and get 0 results from the query.
I think the complicating factor is the fact that I am trying to pull from
many fields and concatanate into one. I also tried writing a macro using
append queries but can't get that to work either.

At this point I will try either road to get it to work; the iif statement in
a table or query, or a macro using several queries.

I just don't know where to focus my energies.


Mark Andrews said:
Can't you just put this expression in your query that drives the report?

Location: IIf([A]," A,","") & IIf([A1]," A-1,","") & IIf([A2]," A-2,","") &
IIf([A3]," A-3,","") & IIf(," B,","") & IIf([B1]," B-1,","") &
IIf([C],"
C,","") & IIf([C1]," C-1,","") & IIf([D]," D,","") & IIf([D1]," D-1,","")
&
IIf([E]," E,","") & IIf([F]," F,","") & IIf([G]," G,","") & IIf([G1],"
G-1,","") & IIf([H]," H,","") & IIf([H1]," H-1,","") & IIf(," I,","") &
IIf([I1]," I-1,","") & IIf([J]," J,","") & IIf([J1]," J-1,","") &
IIf([K],"
K,","") & IIf([L]," L,","") & IIf([M]," M,","") & IIf([N]," N,","") &
IIf([O]," O,","") & IIf([P]," P,","") & IIf([P1]," P-1,","") & IIf([R],"
R,","") & IIf(," S,","") & IIf([MB1]," MB1,","") & IIf([MB2],"
MB2,","") &
IIf([MB3]," MB3,","") & IIf([MB4]," MB4,","")
Click to expand...


Then in the report you have a field called "location".
You might need to add the table name next to the field name (if the query
has multiple tables)
example: [tblInventory].[A1]

Maybe I misunderstood your issue?
Mark


porboy423 said:
I am having difficulty printing the results of an iif expression used on a
form. I would like to be able to export the results of the expression
into a
single field in a table called "location" for further sorting, but would
settle for just being able to print what it shows in the form.
I set up an inventory database using check boxes for each storage location
because one account may have items stored in several places. I am trying
to
make a report that lists either the account number with all the locations
related to the account; or, a list of account numbers sorted by storage
locations for retrieving several items for several accounts at once.
The iif expression is as follows:
=IIf([A]," A,","") & IIf([A1]," A-1,","") & IIf([A2]," A-2,","") &
IIf([A3]," A-3,","") & IIf(," B,","") & IIf([B1]," B-1,","") &
IIf([C],"
C,","") & IIf([C1]," C-1,","") & IIf([D]," D,","") & IIf([D1]," D-1,","")
&
IIf([E]," E,","") & IIf([F]," F,","") & IIf([G]," G,","") & IIf([G1],"
G-1,","") & IIf([H]," H,","") & IIf([H1]," H-1,","") & IIf(," I,","") &
IIf([I1]," I-1,","") & IIf([J]," J,","") & IIf([J1]," J-1,","") &
IIf([K],"
K,","") & IIf([L]," L,","") & IIf([M]," M,","") & IIf([N]," N,","") &
IIf([O]," O,","") & IIf([P]," P,","") & IIf([P1]," P-1,","") & IIf([R],"
R,","") & IIf(," S,","") & IIf([MB1]," MB1,","") & IIf([MB2],"
MB2,","") &
IIf([MB3]," MB3,","") & IIf([MB4]," MB4,","")

Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
I do that kind of thing all the time (maybe with not so many fields).

Perhaps some of your fields are Null and the expression is crashing? Try
making sure all fields have a 0 or -1. Since you are using them as
true/false.

You could use nz(fieldname,"").

You could send me a simple database with the table(s) needed and this query
and I'll take a look for free.

Create a new database and import in just the minimum table(s) and the query
from your current db.

See my site for contact info,
Mark
RPT Software
http://www.rptsoftware.com

porboy423 said:
Thank you for the suggestion. I tried that and get 0 results from the
query.
I think the complicating factor is the fact that I am trying to pull from
many fields and concatanate into one. I also tried writing a macro using
append queries but can't get that to work either.

At this point I will try either road to get it to work; the iif statement
in
a table or query, or a macro using several queries.

I just don't know where to focus my energies.


Mark Andrews said:
Can't you just put this expression in your query that drives the report?

Location: IIf([A]," A,","") & IIf([A1]," A-1,","") & IIf([A2]," A-2,","")
&
IIf([A3]," A-3,","") & IIf(," B,","") & IIf([B1]," B-1,","") &
IIf([C],"
C,","") & IIf([C1]," C-1,","") & IIf([D]," D,","") & IIf([D1],"
D-1,","")
&
IIf([E]," E,","") & IIf([F]," F,","") & IIf([G]," G,","") & IIf([G1],"
G-1,","") & IIf([H]," H,","") & IIf([H1]," H-1,","") & IIf(,"
I,","") &
IIf([I1]," I-1,","") & IIf([J]," J,","") & IIf([J1]," J-1,","") &
IIf([K],"
K,","") & IIf([L]," L,","") & IIf([M]," M,","") & IIf([N]," N,","") &
IIf([O]," O,","") & IIf([P]," P,","") & IIf([P1]," P-1,","") &
IIf([R],"
R,","") & IIf(," S,","") & IIf([MB1]," MB1,","") & IIf([MB2],"
MB2,","") &
IIf([MB3]," MB3,","") & IIf([MB4]," MB4,","")
Click to expand...


Then in the report you have a field called "location".
You might need to add the table name next to the field name (if the query
has multiple tables)
example: [tblInventory].[A1]

Maybe I misunderstood your issue?
Mark


porboy423 said:
I am having difficulty printing the results of an iif expression used on
a
form. I would like to be able to export the results of the expression
into a
single field in a table called "location" for further sorting, but
would
settle for just being able to print what it shows in the form.
I set up an inventory database using check boxes for each storage
location
because one account may have items stored in several places. I am
trying
to
make a report that lists either the account number with all the
locations
related to the account; or, a list of account numbers sorted by storage
locations for retrieving several items for several accounts at once.
The iif expression is as follows:
=IIf([A]," A,","") & IIf([A1]," A-1,","") & IIf([A2]," A-2,","") &
IIf([A3]," A-3,","") & IIf(," B,","") & IIf([B1]," B-1,","") &
IIf([C],"
C,","") & IIf([C1]," C-1,","") & IIf([D]," D,","") & IIf([D1],"
D-1,","")
&
IIf([E]," E,","") & IIf([F]," F,","") & IIf([G]," G,","") & IIf([G1],"
G-1,","") & IIf([H]," H,","") & IIf([H1]," H-1,","") & IIf(,"
I,","") &
IIf([I1]," I-1,","") & IIf([J]," J,","") & IIf([J1]," J-1,","") &
IIf([K],"
K,","") & IIf([L]," L,","") & IIf([M]," M,","") & IIf([N]," N,","") &
IIf([O]," O,","") & IIf([P]," P,","") & IIf([P1]," P-1,","") &
IIf([R],"
R,","") & IIf(," S,","") & IIf([MB1]," MB1,","") & IIf([MB2],"
MB2,","") &
IIf([MB3]," MB3,","") & IIf([MB4]," MB4,","")

Any help would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top