Output field names in report as string

[Sheila D]

Using Access 2000, I have a crosstab query to extract unique row values from
a single field. where each value is shown as 1.

I'd then like to create another table or query which ouputs a string for
each record of all field names where value is greater than zero ie.

Field Names: Red Green Blue
Data 1 0 1 - result should be Red Blue
Not great on VB so a simple solution if possible would be great

Thanks, Sheila D

 

[KARL DEWEY]

Try this ---
SELECT YourCrosstabQuery.Data, Trim(IIf([Red]=1,"Red",Null) & ("
"+IIf([Green]=1,"Green",Null)) & (" "+IIf([Blue]=1,"Blue",Null))) AS Expr1
FROM YourCrosstabQuery;

 

[Sheila D]

Hi Karl

Thanks for this - I have done something for another report using the IF
statement but the problem I have with this report is that I can't
pre-determine what the values of the field will be as there is an Other
option which could be anything at all.

Is there a way to output either the fieldname(s) from the Crosstab or to
loop through the values on the many side of my relationship and join them
into a text string in another table or query perhaps......

Sheila

Try this ---
SELECT YourCrosstabQuery.Data, Trim(IIf([Red]=1,"Red",Null) & ("
"+IIf([Green]=1,"Green",Null)) & (" "+IIf([Blue]=1,"Blue",Null))) AS Expr1
FROM YourCrosstabQuery;

--
KARL DEWEY
Build a little - Test a little

Using Access 2000, I have a crosstab query to extract unique row values from
a single field. where each value is shown as 1.

I'd then like to create another table or query which ouputs a string for
each record of all field names where value is greater than zero ie.

Field Names: Red Green Blue
Data 1 0 1 - result should be Red Blue
Not great on VB so a simple solution if possible would be great

Thanks, Sheila D