Yes/No queries as string

  • Thread starter Thread starter Melissa
  • Start date Start date
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?
 
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?
Click to expand...

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.
 
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?
Click to expand...

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.


.
Click to expand...
Click to expand...
 
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.
Click to expand...

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, ?
Click to expand...

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.
 
Back
Top