Concatonating in a query

[Guest]

I have several yes/no check boxes that I which to concatonate only if result
is positive. (By the way I am also changing the result to a letter is
positive. I also want to add a comma in between results but once again only
if the next result is positive. What I have is producing commas whether the
result was positive or not.

Ex: Test Subjects: IIf([txtWriting]=-1,"W,",Null) &
IIf([txtMath]=-1,"M,",Null) & IIf([txtReading]=-1,"R,",Null) &
IIf([txtScience]=-1,"S,",Null) & IIf([txtSocialStudies]=-1,"SS",Null)

My results end up as :

W,R,
M,R,S,

How do I get the comma not to show at the end.

Thank you

 

[Tom Ellison]

Dear Kathy:

You could put this around the code you have;

Left(X, Len(X) - 1)

Where it says X, copy all the code you have written. Yes, twice.

Tom Ellison

 

[Guest]

Tom,

Thank you for your time, It doesn't seem to have worked. The only
difference I see in the results is that now any result any record that had no
subjects at all is producing a error. I could fix this with any IIf
statement though so I am not worried about that. The extra , though is still
at the end.

Below is my what I have:
Test Subjects: Left(IIf([txtWriting]=-1,"W,",Null) &
IIf([txtMath]=-1,"M,",Null) & IIf([txtReading]=-1,"R,",Null) &
IIf([txtScience]=-1,"S,",Null) & IIf([txtSocialStudies]=-1,"SS",Null),
Len(IIf([txtWriting]=-1,"W,",Null) & IIf([txtMath]=-1,"M,",Null) &
IIf([txtReading]=-1,"R,",Null) & IIf([txtScience]=-1,"S,",Null) &
IIf([txtSocialStudies]=-1,"SS,",Null)-1))

Dear Kathy:

You could put this around the code you have;

Left(X, Len(X) - 1)

Where it says X, copy all the code you have written. Yes, twice.

Tom Ellison

I have several yes/no check boxes that I which to concatonate only if
result
is positive. (By the way I am also changing the result to a letter is
positive. I also want to add a comma in between results but once again
only
if the next result is positive. What I have is producing commas whether
the
result was positive or not.

Ex: Test Subjects: IIf([txtWriting]=-1,"W,",Null) &
IIf([txtMath]=-1,"M,",Null) & IIf([txtReading]=-1,"R,",Null) &
IIf([txtScience]=-1,"S,",Null) & IIf([txtSocialStudies]=-1,"SS",Null)

My results end up as :

W,R,
M,R,S,

How do I get the comma not to show at the end.

Thank you

 

[Tom Ellison]

Dear Kathy:

I was depending too much on your sample data, which is never without
subjects.

Now it would have to be:

IIf(Len(X) > 1, LEFT(X, Len(X) - 1), X)

Starts to get messy, eh? Using Paste, this won't be so hard to create.

Tom Ellison

Tom,

Thank you for your time, It doesn't seem to have worked. The only
difference I see in the results is that now any result any record that had
no
subjects at all is producing a error. I could fix this with any IIf
statement though so I am not worried about that. The extra , though is
still
at the end.

Below is my what I have:
Test Subjects: Left(IIf([txtWriting]=-1,"W,",Null) &
IIf([txtMath]=-1,"M,",Null) & IIf([txtReading]=-1,"R,",Null) &
IIf([txtScience]=-1,"S,",Null) & IIf([txtSocialStudies]=-1,"SS",Null),
Len(IIf([txtWriting]=-1,"W,",Null) & IIf([txtMath]=-1,"M,",Null) &
IIf([txtReading]=-1,"R,",Null) & IIf([txtScience]=-1,"S,",Null) &
IIf([txtSocialStudies]=-1,"SS,",Null)-1))

Dear Kathy:

You could put this around the code you have;

Left(X, Len(X) - 1)

Where it says X, copy all the code you have written. Yes, twice.

Tom Ellison

I have several yes/no check boxes that I which to concatonate only if
result
is positive. (By the way I am also changing the result to a letter is
positive. I also want to add a comma in between results but once again
only
if the next result is positive. What I have is producing commas
whether
the
result was positive or not.

Ex: Test Subjects: IIf([txtWriting]=-1,"W,",Null) &
IIf([txtMath]=-1,"M,",Null) & IIf([txtReading]=-1,"R,",Null) &
IIf([txtScience]=-1,"S,",Null) & IIf([txtSocialStudies]=-1,"SS",Null)

My results end up as :

W,R,
M,R,S,

How do I get the comma not to show at the end.

Thank you