Changing multiple choice answers into paragraph text

[clmarks]

I am using Access 2003. I have a lengthy questionnaire which contains mostly yes/no and multiple choice questions. On some questions a follow-up text field must be filled in based upon the answer that was provided in the multiple choice block. I have to generate a report from this data which looks like a typewritten report (paragraph text).

For example, I have the persons first, middle, and last name, along with their date of birth, and gender. I also have their religious preference. This is a multiiple choice questions, but if they answer "other", there is a text field for them to describe what "other" means. And finally they must answer whether they were in a controlled environment during the 30 days prior to coming to our facility. If yes, they must indicate how many days and what type of facility it was.

I need to turn that information into a paragraph that looks something like:

Mr. Smith is a 31-year-old Hispanic mail born on August 4,
1978. He stated that his religious preference is Protestant.
Mr. Smith has lived at the address listed above for three
years prior to this incarceration. He stated that he had been
in jail for 21 of the 30 days leading up to his current confinement.

Has anyone ever done this kind of thing before? If so, will you please let me know how you accomplished it? Any help will be appreciated. This is a long questionnaire and I would dearly love for this project to be over. I find it kind of scary.

Thanks,

Cherry

 

[SOS]

I will typically create the basic text (using a memo field) and use some placeholders like this:

|Salutation||Person| is a |PersAge|-year-old |Ethnicity| |persSex| born on |DateOfBirth|. |SexPronoun| stated that |SexPronoun2| religious preference is |Religion|.

You get the picture. That I save in a table for the letter text. Then I use a recordset and VBA to substitute the appropriate field data for the placeholders:

Function GetDataForLetter(lngID As Long) As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strLetter As String
Dim strText As String
 
Set db = CurrentDb
Set rst = CurrentDb("Select * From QueryWhereDataIs WHERE PersonID =" & lngID)
 
strLetter = Nz(DLookup("LetterField", "TableName"),"")
 
If strLetter <> "" Then
   With rst
     strLetter =Replace(strLetter, "|Salutation|", !Salutation)
     strLetter = Replace(strLetter, "|Person|",!Person)
' and so on
   End With
rst.Close
Set rst = Nothing

I also usually have a table which has all of the placeholders defined so I just use a recordset to iterate through those for the record and substitute.

I hope that helps somewhat.

 

[clmarks]

Thanks. This is definitely a learning moment for me. I'll give it a try.

Cherry