concatenating string using Access Expression Builder

Question

I have an Access table with around 10 yes/no fields regarding patient diet. (For instance: Low-Fat, Low-Sugar,No-Restrictions, etc. The point is that for each patient we check off the fields accordingly). I'd like to create a report that displays the diet for each patient. So for one patient it might read as thus: John Smith Low-Fat,Low-Carb. You get the idea.

Here's what I would need Access to do. For each row, check each column, if it is checked, concatenate that diet type to a string. That string is what Access Report should display.

I thought of doing it like this: myString = ""; myString += IIF([LOW_FAT]==-1,"Low-Fat",""); myString += IIF([LOW_CARB]==-1,"Low-Carb","");

and so on, using the expression builder.

Can this be done? If not, how would I use the code builder for this?

Thanks

Fionnuala Fionnuala · Accepted Answer · 2011-01-04T17:21:31

You need to reconsider your table deign. It would be better to have a diet table, rather than a bunch of yes/no fields. The diet table might include:

PatientID
DietType
DateFrom
OrderedBy
Etc

The Diet types can be selected from a look-up table through a combo-box (please do not use a look-up field in your table), the diet table can be placed on the form in a subform with PatientID as the Link Child and Link Master field.

The result would be a table that looked something like:

PatientID DietType
1         Low Fat
1         Low Carb
56        Low Carb
56        Do not feed after dark
56        No water
30        Nil By Mouth

This is a standard design, because it is very flexible and easy to query.

concatenating string using Access Expression Builder

1 Answers