Yes/No queries as string

M

Melissa

I was wondering if one can call up a query that pulls
from yes/no fields and combines them into a delimited
string format on a report. For example, when a record
includes multiple check boxes, instead of viewing all the
fields in the query and observing the checked boxes, is
there any way, to take the field name and make it into a
stringed answer
ie. meat(y), veggies(n), milk(y), tuna(y)===
List= meat, milk, tuna

I assume there MUST be a code that allows one to do
this. Any ideas?
 
J

John Vinson

I was wondering if one can call up a query that pulls
from yes/no fields and combines them into a delimited
string format on a report. For example, when a record
includes multiple check boxes, instead of viewing all the
fields in the query and observing the checked boxes, is
there any way, to take the field name and make it into a
stringed answer
ie. meat(y), veggies(n), milk(y), tuna(y)===
List= meat, milk, tuna

I assume there MUST be a code that allows one to do
this. Any ideas?

Well, you're having trouble because the table design is improperly
normalized: you're storing data in fieldnames. If you have a Many to
Many relationship between (meals? diets?) and foods, you really should
model it as a many to many relationship!

That said... try putting in a calculated field:

Foods: IIF([Meat], "meat, ", ""), IIF([veggies], "veggies, ", ""),
IIF([tuna], "tuna, ", ""), IIF(<etc etc>)

This will leave you with a trailing comma after the last YES - you may
want to write a little VBA function to build the string so you can
more easily trim this off.
 
G

Guest

Ok, you are a genious! When I put my first IIf argument
in, it worked. But, I'm still having trouble connectong
all the IIf codes into one string. An error box tells me
I have invalid syntax.
I also don't know what you mean by a many-to many
relationship: is that between fields or queries or
tables, ?

-----Original Message-----
I was wondering if one can call up a query that pulls
from yes/no fields and combines them into a delimited
string format on a report. For example, when a record
includes multiple check boxes, instead of viewing all the
fields in the query and observing the checked boxes, is
there any way, to take the field name and make it into a
stringed answer
ie. meat(y), veggies(n), milk(y), tuna(y)===
List= meat, milk, tuna

I assume there MUST be a code that allows one to do
this. Any ideas?

Well, you're having trouble because the table design is improperly
normalized: you're storing data in fieldnames. If you have a Many to
Many relationship between (meals? diets?) and foods, you really should
model it as a many to many relationship!

That said... try putting in a calculated field:

Foods: IIF([Meat], "meat, ", ""), IIF ([veggies], "veggies, ", ""),
IIF([tuna], "tuna, ", ""), IIF(<etc etc>)

This will leave you with a trailing comma after the last YES - you may
want to write a little VBA function to build the string so you can
more easily trim this off.


.
 
J

John Vinson

Ok, you are a genious! When I put my first IIf argument
in, it worked. But, I'm still having trouble connectong
all the IIf codes into one string. An error box tells me
I have invalid syntax.

Oops. My typo - the IIF statements should be separated by ampersands
not commas:

Foods: IIF([Meat], "meat, ", "") & IIF([veggies], "veggies, ", "") &
IIF([tuna] said:
I also don't know what you mean by a many-to many
relationship: is that between fields or queries or
tables, ?

Your tables should be restructured. Your fields [Meat] and [Veggies]
SHOULD NOT EXIST.

Instead, you should consider having your data stored in THREE tables -
a standard many-to-many relationship, which resolves into two
one-to-many relationships. Again, guessing at the context:

tblMeals
MealID <autonumber or other primary key>
<other fields, e.g. personID, date-time, etc.>

tblFoods
FoodID <autonumber perhaps>
FoodName <e.g. "Meat", "Dessert", "Pilsner Urquell">

tblConsumed
MealID <link to tblMeals>
FoodID <link to tblFoods>

Rather than putting a checkmark in the Meat checkbox, you would add -
using a subform - a record to tblConsumed with Meat as the foodID.
 

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

Top