Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenation and commas (Access 2K)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenation and commas (Access 2K)

    a) Is there a text box for additional information for EACH check box?
    [img]/forums/images/smilies/cool.gif[/img] Is the additional information required, or can the user leave some text boxes empty?

  3. #3
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenation and commas (Access 2K)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenation and commas (Access 2K)

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

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenation and commas (Access 2K)

    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 ("")
    Francois

  6. #6
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenation and commas (Access 2K)

    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?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenation and commas (Access 2K)

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

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenation and commas (Access 2K)

    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.
    Francois

  9. #9
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenation and commas (Access 2K)

    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!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenation and commas (Access 2K)

    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.

  11. #11
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenation and commas (Access 2K)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •