Re: convert "yes" to '2' then add all fields

• Previous message: Draedric: "Question regarding Distributing"
• In reply to: PaulFort: "convert "yes" to '2' then add all fields"
• Next in thread: PaulFort: "Re: convert "yes" to '2' then add all fields"
• Reply: PaulFort: "Re: convert "yes" to '2' then add all fields"
• Messages sorted by: [ date ] [ thread ]

Date: Mon, 25 Oct 2004 15:48:14 -0400

Are Yes and No text fields, or is that a Boolean field that's formatted as
Yes/No, rather than True/False?

In either case, you can create a query that has a computed field in it: the
details will be a little different, though.

Create the Select query and drag whatever fields you care about into the
grid. Pick an empty column in the grid, put your cursor in the Field row of
that column and hit Shift-F2 to bring up the Zoom box.

In the Zoom box, type:

Total: IIf(Len(Trim([tall] & "")) = 0, 0, IIf([tall] ="yes", 2, 1)) +
IIf(Len(Trim([strong] & "")) = 0, 0, IIf([strong] = "yes", 2, 1)) +
IIf(Len(Trim([heavy] & "")) = 0, 0, IIf([heavy] ="yes", 2, 1)) +
IIf(Len(Trim([thin] &"")), 0 , IIf([thin] ="yes", 2, 1))

if they're text, and

Total: IIf(IsNull([tall]), 0, IIf([tall], 2, 1)) + IIf(IsNull([strong]), 0,
IIf([strong], 2, 1)) + IIf(IsNull([heavy]), 0, IIf([heavy], 2, 1)) +
IIf(IsNull([thin]), 0 , IIf([thin], 2, 1))

if they're boolean.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"PaulFort" <PaulFort@discussions.microsoft.com> wrote in message
news:9F126709-DBFA-48FB-9922-A2B8D82E6400@microsoft.com...
> Hi All,
> This should be simple....
> To make my question clear, I put a demo table here:
> http://tallahasseech.org/accessquestion.htm
>
> - I want to assign the number '2' to each yes and  assign '1' to a "no"
>
> - I want to then add up all the numbers in the fields for each record
(person)
>
> > So the answers for my demo would be:
> tom =  7 (2+2+2+1)
> tim =  6 (1+2+2+1)
> james = 0
>
> How do I do this.
> I appreciate the help.
> Paul
>
>

• Previous message: Draedric: "Question regarding Distributing"
• In reply to: PaulFort: "convert "yes" to '2' then add all fields"
• Next in thread: PaulFort: "Re: convert "yes" to '2' then add all fields"
• Reply: PaulFort: "Re: convert "yes" to '2' then add all fields"
• Messages sorted by: [ date ] [ thread ]