Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    6

    Unanswered: Inserting a line into a database field

    I am using the merge mail functionality in access to populate the data from a template. However, there are a couple of fields that need to have data in multiple lines. for example, if the data is LINE1|LINE2, I need it shown as
    LINE1
    LINE2

    in the word document. I think the field itself will support such a format. Is there a sql to replace LINE1|LINE2 into
    LINE1
    LINE2
    ?

    When I populate the data the way I want in the input text file before loading into Access using the import wizard, it tries to read the data in the next line as a new record. If there is an alternate way to load the data, it will solve the problem. Any ideas?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    For mimicking the line feed you would use the vbcrlf or the chr(13).

    ex:
    MsgBox "1st line" & vbCrLf & "2nd line"
    or
    MsgBox "1st line" & Chr(13) & "2nd line"

    If you're importing where it's multiple lines in 1 field, you may need to set up an import specification or design an import routine which walks through the file line by line. If it's a Word document, try exporting/saving the data from Word in a way which might distinguish the fields better.

    I've done it where I imported data with multiple lines from word all into 1 field and then wrote some code to look for the linefeed character (and other characters) to distinguish the seperate lines into fields to import into another table (i.e. Word labels with multiple lines on the address.)
    Last edited by pkstormy; 01-14-08 at 21:08.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Apr 2007
    Posts
    6
    Thank you for your reply. Can I use chr(13) in the sql itself to reflect the return line in the field? The problem in my case is that it has to be reflected in the data because I am using the mail merge. I am not reading field by field to populate the data into the word document.

  4. #4
    Join Date
    Apr 2007
    Posts
    6
    Quote Originally Posted by pkstormy
    For mimicking the line feed you would use the vbcrlf or the chr(13).

    ex:
    MsgBox "1st line" & vbCrLf & "2nd line"
    or
    MsgBox "1st line" & Chr(13) & "2nd line"

    If you're importing where it's multiple lines in 1 field, you may need to set up an import specification or design an import routine which walks through the file line by line. If it's a Word document, try exporting/saving the data from Word in a way which might distinguish the fields better.

    I've done it where I imported data with multiple lines from word all into 1 field and then wrote some code to look for the linefeed character (and other characters) to distinguish the seperate lines into fields (i.e. Word labels with multiple lines on the address.)
    The import routine will probably be the best solution in my case. I tried the import specification, but it is giving me the same errors as doing with the import wizard. Do you have a sample specification that I can use. I saved the import spec while doing it through the wizard, but it is not showing me the path of the saved file. If you have any such sample, I can play around with it. Thank you very much for your help.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I don't have any examples offhand (mostly old stuff in my archives I'd need to dig up) but I believe you would utilize the Input command if you need to walk through the file line by line. You may want to check for the help on this or some examples from googling it.

    From MSAccess help....

    Dim MyChar
    Open "TESTFILE" For Input As #1 ' Open file.
    Do While Not EOF(1) ' Loop until end of file.
    MyChar = Input(1, #1) ' Get one character.
    Debug.Print MyChar ' Print to the Immediate window.
    Loop
    Close #1 ' Close file.

    Dim MyString, MyNumber
    Open "TESTFILE" For Input As #1 ' Open file for input.
    Do While Not EOF(1) ' Loop until end of file.
    Input #1, MyString, MyNumber ' Read data into two variables.
    Debug.Print MyString, MyNumber ' Print data to the Immediate window.
    Loop
    Close #1 ' Close file.


    Dim TextLine
    Open "TESTFILE" For Input As #1 ' Open file.
    Do While Not EOF(1) ' Loop until end of file.
    Line Input #1, TextLine ' Read line into variable.
    Debug.Print TextLine ' Print to the Immediate window.
    Loop
    Close #1 ' Close file.

    There might be an easier way though and you may want to see if you can export the data from Word in a more import friendly way. When I last did it, I again imported it into 1 field and then looked for characters such as chr(13) to separate it and import it into other fields. It was messy but it got it done as it was a 1 time procedure.

    You may want to also consider if it's importing certain data into another field, possibly import it as is and then simply concatenate the 2 fields together into a 3rd field.
    Last edited by pkstormy; 01-14-08 at 21:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    vbcrlf isn't the same as chr$(13), its the same as chr$(11) & chr$(13). Its a composite of the linefeed character chr$(11) and the carriage return character chr$(13).. it may be chr$(13) & chr$(11) as opposed to chr$(11) & chr$(13), but vbcrlf is definitely a composite of 2 non printing characters

    so once you know what fordces a linefeed then 'all' you need to do is to update the data

    you could do soemthign like
    update <mytable> set <mycolumn> = replace(mycolumn,"|",vbcrlf)


    I'd want to check that vbcrlf is appropriate for your proposed use.. it works fine in Access, just it may not int he targert application. so create a new record in you db and apply that as a change (or set an appropriate where clause to limit the change to say one row.

    assuming it works OK then apply it to all the data

    this query is actually making use of the VBA function replace(), rather than SQL per se.. have a look at that in the help file.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ooops. You're right healdem. Thanks - I wasn't thinking correctly.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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