New User: IIF Statement placement and Syntax Error

Hi,

I appreciate any help I can get. I have a book on this but I still
can not get it. I am getting a syntax error on my IIF statement and I
am not sure where I should put the IIF statement. Right now I have it
placed it in the report under the details section but I wasn't sure if
I needed to create a calculated field in my table instead. The
following is my 1st IIF statement placed under the "Roast Beef" header
on my report as well as table info. Once I have this IIF statement
correct I will follow suite for the IIF statement on the Turkey and
the Rolls.

Thanks for your help.

=iif([Item Code]="RB",[Customer FName] AND " " AND [Customer LName]
AND " - " AND [Quantity] AND " " AND [QTY Code]," ")

My data base is simple:

Customer FName
Customer LName
Item Code
Item Name
Item Details
Quantity
QTY Code

Data Example 1

Customer FName=Jim
Customer LName=Smith
Item Code=RB
Item Name=Roast Beef
Item Details=Sliced Thin
Quantity=6
QTY Code=lbs

Data Example 2

Customer FName=Jim
Customer LName=Smith
Item Code=RBTR
Item Name=Rolls Buttercrust
Item Details=Sliced
Quantity=3
QTY Code=dz

Data Example 3

Customer FName=Jim
Customer LName=Smith
Item Code=TG
Item Name=Turkey & Gravy
Item Details=Sliced Thick
Quantity=4
QTY Code=lbs

Data Example 4

Customer FName=Mike
Customer LName=Jones
Item Code=RB
Item Name=Roast Beef and Gravy
Item Details=Light on the gravy
Quantity=3
QTY Code=lbs

Data Example 5

Customer FName=Mike
Customer LName=Jones
Item Code=RBTR
Item Name=Rolls Marble Rye
Item Details=
Quantity=4
QTY Code=dz

Data Example 6

Customer FName=Mike
Customer LName=Jones
Item Code=TG
Item Name=Turkey & Gravy
Item Details=Sliced Thin
Quantity=5
QTY Code=lbs

Report Output Needed:

Roast Beef Turkey Rolls

Jim Smith - 6 lbs Jim Smith - 4 lbs Jim Smith - 3 dz Rolls
Buttercrust
Sliced Thin Sliced Thick Sliced

Mike Jones - 3 lbs Mike Jones - 5 lbs Mike Jones - 4 dz Rolls
Marble Rye
Light on the Gravy Sliced Thin

Re: New User: IIF Statement placement and Syntax Error

"bunzmon" wrote in message
news:104aa3d3-afa2-47b6-ba69-c64fffef43dd@s19g2000prg.google groups.com...
> Hi,
>
> I appreciate any help I can get. I have a book on this but I still
> can not get it. I am getting a syntax error on my IIF statement and I
> am not sure where I should put the IIF statement. Right now I have it
> placed it in the report under the details section but I wasn't sure if
> I needed to create a calculated field in my table instead. The
> following is my 1st IIF statement placed under the "Roast Beef" header
> on my report as well as table info. Once I have this IIF statement
> correct I will follow suite for the IIF statement on the Turkey and
> the Rolls.
>
> Thanks for your help.
>
> =iif([Item Code]="RB",[Customer FName] AND " " AND [Customer LName]
> AND " - " AND [Quantity] AND " " AND [QTY Code]," ")
>
> My data base is simple:
>
> Customer FName
> Customer LName
> Item Code
> Item Name
> Item Details
> Quantity
> QTY Code
>
> Data Example 1
>
> Customer FName=Jim
> Customer LName=Smith
> Item Code=RB
> Item Name=Roast Beef
> Item Details=Sliced Thin
> Quantity=6
> QTY Code=lbs
>
> Data Example 2
>
> Customer FName=Jim
> Customer LName=Smith
> Item Code=RBTR
> Item Name=Rolls Buttercrust
> Item Details=Sliced
> Quantity=3
> QTY Code=dz
>
> Data Example 3
>
> Customer FName=Jim
> Customer LName=Smith
> Item Code=TG
> Item Name=Turkey & Gravy
> Item Details=Sliced Thick
> Quantity=4
> QTY Code=lbs
>
> Data Example 4
>
> Customer FName=Mike
> Customer LName=Jones
> Item Code=RB
> Item Name=Roast Beef and Gravy
> Item Details=Light on the gravy
> Quantity=3
> QTY Code=lbs
>
> Data Example 5
>
> Customer FName=Mike
> Customer LName=Jones
> Item Code=RBTR
> Item Name=Rolls Marble Rye
> Item Details=
> Quantity=4
> QTY Code=dz
>
> Data Example 6
>
> Customer FName=Mike
> Customer LName=Jones
> Item Code=TG
> Item Name=Turkey & Gravy
> Item Details=Sliced Thin
> Quantity=5
> QTY Code=lbs
>
> Report Output Needed:
>
> Roast Beef Turkey Rolls
>
> Jim Smith - 6 lbs Jim Smith - 4 lbs Jim Smith - 3 dz Rolls
> Buttercrust
> Sliced Thin Sliced Thick Sliced
>
> Mike Jones - 3 lbs Mike Jones - 5 lbs Mike Jones - 4 dz Rolls
> Marble Rye
> Light on the Gravy Sliced Thin

Try this:

=iif([Item Code]="RB",[Customer FName] & " " & [Customer LName] & " - " &
[Quantity] & " " & [QTY Code]," ")

Fred Zuckerman

Re: New User: IIF Statement placement and Syntax Error

On Mon, 31 Dec 2007 14:03:28 -0800 (PST), bunzmon wrote:

> Hi,
>
> I appreciate any help I can get. I have a book on this but I still
> can not get it. I am getting a syntax error on my IIF statement and I
> am not sure where I should put the IIF statement. Right now I have it
> placed it in the report under the details section but I wasn't sure if
> I needed to create a calculated field in my table instead. The
> following is my 1st IIF statement placed under the "Roast Beef" header
> on my report as well as table info. Once I have this IIF statement
> correct I will follow suite for the IIF statement on the Turkey and
> the Rolls.
>
> Thanks for your help.
>
> =iif([Item Code]="RB",[Customer FName] AND " " AND [Customer LName]
> AND " - " AND [Quantity] AND " " AND [QTY Code]," ")
>
> My data base is simple:
>
> Customer FName
> Customer LName
> Item Code
> Item Name
> Item Details
> Quantity
> QTY Code
>
> Data Example 1
>
> Customer FName=Jim
> Customer LName=Smith
> Item Code=RB
> Item Name=Roast Beef
> Item Details=Sliced Thin
> Quantity=6
> QTY Code=lbs
>
> Data Example 2
>
> Customer FName=Jim
> Customer LName=Smith
> Item Code=RBTR
> Item Name=Rolls Buttercrust
> Item Details=Sliced
> Quantity=3
> QTY Code=dz
>
> Data Example 3
>
> Customer FName=Jim
> Customer LName=Smith
> Item Code=TG
> Item Name=Turkey & Gravy
> Item Details=Sliced Thick
> Quantity=4
> QTY Code=lbs
>
> Data Example 4
>
> Customer FName=Mike
> Customer LName=Jones
> Item Code=RB
> Item Name=Roast Beef and Gravy
> Item Details=Light on the gravy
> Quantity=3
> QTY Code=lbs
>
> Data Example 5
>
> Customer FName=Mike
> Customer LName=Jones
> Item Code=RBTR
> Item Name=Rolls Marble Rye
> Item Details=
> Quantity=4
> QTY Code=dz
>
> Data Example 6
>
> Customer FName=Mike
> Customer LName=Jones
> Item Code=TG
> Item Name=Turkey & Gravy
> Item Details=Sliced Thin
> Quantity=5
> QTY Code=lbs
>
> Report Output Needed:
>
> Roast Beef Turkey Rolls
>
> Jim Smith - 6 lbs Jim Smith - 4 lbs Jim Smith - 3 dz Rolls
> Buttercrust
> Sliced Thin Sliced Thick Sliced
>
> Mike Jones - 3 lbs Mike Jones - 5 lbs Mike Jones - 4 dz Rolls
> Marble Rye
> Light on the Gravy Sliced Thin


There is a difference between AND and &. The ampersand (&) is used to
concatenate fields, not "AND".
Add an unbound text control to the report.
Set it's control source to:

=iif([Item Code]="RB",[Customer FName] & " " & [Customer LName]
& " - " & [Quantity] & " " & [QTY Code]," ")

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Re: New User: IIF Statement placement and Syntax Error

I do hope I am misinterpreting your question where you refer to a table.

Your Db should have 4 tables, 1 for Customer, 1 for Item, 1 for Qty Code and
1 for the Order. I will give more info if required

Phil

"bunzmon" wrote in message
news:104aa3d3-afa2-47b6-ba69-c64fffef43dd@s19g2000prg.google groups.com...
> Hi,
>
> I appreciate any help I can get. I have a book on this but I still
> can not get it. I am getting a syntax error on my IIF statement and I
> am not sure where I should put the IIF statement. Right now I have it
> placed it in the report under the details section but I wasn't sure if
> I needed to create a calculated field in my table instead. The
> following is my 1st IIF statement placed under the "Roast Beef" header
> on my report as well as table info. Once I have this IIF statement
> correct I will follow suite for the IIF statement on the Turkey and
> the Rolls.
>
> Thanks for your help.
>
> =iif([Item Code]="RB",[Customer FName] AND " " AND [Customer LName]
> AND " - " AND [Quantity] AND " " AND [QTY Code]," ")
>
> My data base is simple:
>
> Customer FName
> Customer LName
> Item Code
> Item Name
> Item Details
> Quantity
> QTY Code
>
> Data Example 1
>
> Customer FName=Jim
> Customer LName=Smith
> Item Code=RB
> Item Name=Roast Beef
> Item Details=Sliced Thin
> Quantity=6
> QTY Code=lbs
>
> Data Example 2
>
> Customer FName=Jim
> Customer LName=Smith
> Item Code=RBTR
> Item Name=Rolls Buttercrust
> Item Details=Sliced
> Quantity=3
> QTY Code=dz
>
> Data Example 3
>
> Customer FName=Jim
> Customer LName=Smith
> Item Code=TG
> Item Name=Turkey & Gravy
> Item Details=Sliced Thick
> Quantity=4
> QTY Code=lbs
>
> Data Example 4
>
> Customer FName=Mike
> Customer LName=Jones
> Item Code=RB
> Item Name=Roast Beef and Gravy
> Item Details=Light on the gravy
> Quantity=3
> QTY Code=lbs
>
> Data Example 5
>
> Customer FName=Mike
> Customer LName=Jones
> Item Code=RBTR
> Item Name=Rolls Marble Rye
> Item Details=
> Quantity=4
> QTY Code=dz
>
> Data Example 6
>
> Customer FName=Mike
> Customer LName=Jones
> Item Code=TG
> Item Name=Turkey & Gravy
> Item Details=Sliced Thin
> Quantity=5
> QTY Code=lbs
>
> Report Output Needed:
>
> Roast Beef Turkey Rolls
>
> Jim Smith - 6 lbs Jim Smith - 4 lbs Jim Smith - 3 dz Rolls
> Buttercrust
> Sliced Thin Sliced Thick Sliced
>
> Mike Jones - 3 lbs Mike Jones - 5 lbs Mike Jones - 4 dz Rolls
> Marble Rye
> Light on the Gravy Sliced Thin

Re: New User: IIF Statement placement and Syntax Error

On Jan 1, 9:03=A0am, bunzmon wrote:
> Hi,
>
> I appreciate any help I can get. =A0I have a book on this but I still
> can not get it. =A0I am getting a syntax error on my IIF statement and I
> am not sure where I should put the IIF statement. =A0Right now I have it
> placed it in the report under the details section but I wasn't sure if
> I needed to create a calculated field in my table instead. =A0The
> following is my 1st IIF statement placed under the "Roast Beef" header
> on my report as well as table info. =A0Once I have this IIF statement
> correct I will follow suite for the IIF statement on the Turkey and
> the Rolls.
>
> Thanks for your help.
>
> =3Diif([Item Code]=3D"RB",[Customer FName] AND " " AND [Customer LName]
> AND " =A0- =A0" AND [Quantity] AND " " AND [QTY Code]," ")
>
> My data base is simple:
>
> Customer FName
> Customer LName
> Item Code
> Item Name
> Item Details
> Quantity
> QTY Code
>
> Data Example 1
>
> Customer FName=3DJim
> Customer LName=3DSmith
> Item Code=3DRB
> Item Name=3DRoast Beef
> Item Details=3DSliced Thin
> Quantity=3D6
> QTY Code=3Dlbs
>
> Data Example 2
>
> Customer FName=3DJim
> Customer LName=3DSmith
> Item Code=3DRBTR
> Item Name=3DRolls Buttercrust
> Item Details=3DSliced
> Quantity=3D3
> QTY Code=3Ddz
>
> Data Example 3
>
> Customer FName=3DJim
> Customer LName=3DSmith
> Item Code=3DTG
> Item Name=3DTurkey & Gravy
> Item Details=3DSliced Thick
> Quantity=3D4
> QTY Code=3Dlbs
>
> Data Example 4
>
> Customer FName=3DMike
> Customer LName=3DJones
> Item Code=3DRB
> Item Name=3DRoast Beef and Gravy
> Item Details=3DLight on the gravy
> Quantity=3D3
> QTY Code=3Dlbs
>
> Data Example 5
>
> Customer FName=3DMike
> Customer LName=3DJones
> Item Code=3DRBTR
> Item Name=3DRolls Marble Rye
> Item Details=3D
> Quantity=3D4
> QTY Code=3Ddz
>
> Data Example 6
>
> Customer FName=3DMike
> Customer LName=3DJones
> Item Code=3DTG
> Item Name=3DTurkey & Gravy
> Item Details=3DSliced Thin
> Quantity=3D5
> QTY Code=3Dlbs
>
> Report Output Needed:
>
> Roast Beef =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Turkey =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0Rolls
>
> Jim Smith - 6 lbs =A0 =A0 =A0 Jim Smith - 4 lbs =A0 =A0Jim Smith - 3 dz Ro=
lls
> Buttercrust
> Sliced Thin =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Sliced Thick =A0 =A0 =A0 =A0 =
=A0Sliced
>
> Mike Jones - 3 lbs =A0 =A0 Mike Jones - 5 lbs =A0Mike Jones - 4 dz Rolls
> Marble Rye
> Light on the Gravy =A0 =A0 Sliced Thin

Bunzmon
You are where I was a few years ago (and I am far far from being an
expert) but its probably worth getting some real basics sorted out
first:

Tables - Where you store data and you should have tables that do not
duplicate data eg store the names and address in one table and the
items you sell in another and the actual sales in another. You can
get very fussy with this non duplication (its called normalisation and
it is smart to do so but probably better addressed later (no pun
intended). Biggest mistake I used to (and sometimes still do) is to
put too much information in one table. Much better to have many
smaller tables which are joined (linked) by a particular field. Eg
the Names table may have a field for Country. Instead of storing each
country name in there, you would store a link to another table with
all the countries in it. Both much more efficient and if a Country
name changes you only have to change the name once in the Country
table

Queries - Where you select or manipulate data from Tables or other
queries. Eg you might only want certain people from your name and
address Table. You would create a query based on the Names table and
use criteria to limit the list to certain people. (eg name =3D Fred).
You also use queries to do calculations eg the Items table has the
value of a sandwich and your sales table has the quantity sold. A
query would be used to multiply the quantity from the sales table by
the value in the item table to get a total sales value

Reports- Used to show information in a friendly (?) form. Can be
based on information from Tables or Queries or a combination.

Back to your original question: eg if you wanted Roast Beef sales for
Fred Nurks only, you would create a query which connected (joined)
your Names table, your sales table and your item table
The iif statement would be used in this query to show a new field
that can be used to select just the items that met your if statement
criteria.

Apologies. That turned out to be surpisingly difficult to simplify
without being patronising or simplify so much I steered you in the
wrong directions. Bottom line - go back to basics and get very clear
on the difference between Tables and Queries or you could waste a lot
of time going the wrong way
Steve