adding a carriage return to a field in access

Hi. The carriage return in Access (in Windows OS, really) consists of two characters: CR + LF.

To add them to your data, you would concatenate Chr(13) & Chr(10) into it. However, I am not sure how to apply that idea to your data, because you seem to want to insert the carriage returns in certain places.

If you're using a Long Text field, there may be another option, which is to use the Rich Text Format, so the user can insert the carriage returns easily during data entry.

Just a thought...

I found a property for forms that controls what happens on the enter key (set it to new line in field) but I still have an issue with the datasheet mode. It does not let me enter a return but goes to the next field. Any clues?

What I suggest is that you create a so called continues form (multiple items form). Then, as you note, you can change/set the enter key behavior to cause a new line.

So, say a form like this:

So, while the above is "not quite" like a data sheet, adopting a continues form (multiple items form), then you can control the behavour of the enter key for that text box.

And bonus points is that the above type of form allows check box, combo box, and near unlimited customizing. Datasheets can be fun, quick, but for advanced customizing, then try a continues items form like above (use the wizard to help you create the basic form, and then in design mode you can clean up and customize the results for your needs.

The following is a quick and dirty function which will parse the string expression into a series of substrings separated by a double carriage return/line feed as in your example:

Public Function ParseString(ByVal strIn As String)

    Dim strQuestion As String
    Dim strAnswer As String
    Dim strLine As String
    
    Do Until InStr(strIn, ":") = 0
        strQuestion = Left(strIn, InStr(strIn, ":"))
        strIn = Mid(strIn, Len(strQuestion) + 1)
        strAnswer = Left(strIn, InStr(strIn, ":"))
        strAnswer = RTrim(Left(strAnswer, InStrRev(strAnswer, " ")))
        If Len(strAnswer) = 0 Then
            strAnswer = Mid(strIn, InStr(strIn, ":") + 1)
        End If
        strLine = strLine & vbNewLine & vbNewLine & UCase(Left(strQuestion, 1)) & Mid(strQuestion, 2) & strAnswer
        strIn = Mid(strIn, Len(strAnswer) + 2)
    Loop
    
    ParseString = Mid(strLine, 5)
    
End Function

You can see how it works in the Immediate window:

strTest = "Who: the cat what: wants to cross the road where: at the house when: everyday why: because it's a cat."

? ParseString(strTest)
Who: the cat

What: wants to cross the road

Where: at the house

When: everyday

Why: because it's a cat.

The function does of course assume a uniformity of formatting of the string expression passed into it.