Concatenation and commas (Access 2K)

Topic

New Reply

I have a form on which the user will list all of or some the symptoms identified on a patient. Some patients will display only one symptom while others might displayed 3,5, 8 or 10 other symptoms listed. on some symptoms, my user will have to type in additional info to further clarify each symptom. for example, say I have these symptoms listed:
1- client is under age. (a textbox is provided to type in the exact age on the form)
2- client is delusional (a textbox is provided to give short explanation on the form)
3- client’s substance abuse resulted in (another textbox to be filled in)

On the report, I need to concatenate each symptom and maintain correct punctuation. How can I accomplish this? Say a client have 2 symptoms (1 and 3), how can I concatenate the symtoms so the final sentence will look like this “Client is underage (15 years old) AND client’s substance abuse has resulted in 3 DUI citations.” i.e. itf there is only one symptom, I want a period at the end of the sentence, if there are 2 symptoms I need to put an ‘AND’ between the 1st and 2nd symptoms and a period at the end of the 2nd symptom. On the other hand, if there are 3 symptoms, a comma should be placed at the end of the 1st symptom, an ‘AND’ should appear between the 2nd and 3rd symptoms and a period should go at the end of the 3rd symptom. If there are 4 symptoms, there should be a comma after the 1st and 2nd symptoms, the word ‘AND’ at the end of the 3rd and a period at the end of the 4th symptom. I hope I am making sense here. On my form I’ve made each symptom as a checkbox. I figure I should try counting how many checkboxes are checked to figure out how many commas and the like but beyond this I have no idea how to proceed. Any insight is very much welcome. Thank you.

Reply | Quote

Replies

a) Is there a text box for additional information for EACH check box?
Is the additional information required, or can the user leave some text boxes empty?

Reply | Quote

Yes, there is a textbox for each checkbox and no, the users doesn’t always have to fill in each textbox.

Reply | Quote

Can you provide some details about the names of the controls involved?

Reply | Quote

I hope this help Hans.
For each symptom I have a checkbox (chkSymptom1 through chkSymptom11). Each corresponding texbox that my user user will be filling in on as needed basis is named txtSymptom1 through txtSymptom11.

Francois
How do I implement your suggestion?

Reply | Quote

And do you have a table that lists Symptom1 through Symptom11?

Reply | Quote

Here a quick and dirty sample of what I mean.
I have not yet build the check to see if the textboxes are empty or not.

Reply | Quote

Hello hans, francois
I am attaching a barebone version of the databse I am working on. I kinda able get the counter and part of the concatenation done but I am not sure how to iterate so I can get the punctuation correct. Please help!

Reply | Quote

See if the attached version does what you want. Note: if you check everything, the text in the message box is truncated. MsgBox displays at most 1,024 characters. The variable strLongSentence will hold the complete text.

Reply | Quote

Thank you! Thank you! Thank you. I am forever indebted!

Reply | Quote

If you can count how many concat you have to do, then you can use a select case after each concat.
If it is the last one, add a period.
If it is the last -1, then add an AND.
In the other cases, add a comma.
This suppose that you have take care of the textboxes are not empty when a check box is checked.
HTH

P.S. If a textbox is empty, you can add a select to add a empty string (“”)

Reply | Quote