Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2006
    Posts
    10

    Cool Unanswered: Lotus Notes fields to Access using SQL

    Hi,

    I am an absolute novice in SQL & Access and require some advice.
    I'm attempting to transfer Lotus Notes (Domino) fields to an Access DB memo field using SQL and have done so with limited success via this piece of code...

    AccessApp.DoCmd.RunSQL
    "Update tblProjectMain
    Set Comments = Comments + '" & Date1 & "' + ' - ' + '" & Comments1 & "' Where GATE_Number = '" & GATE & "'"

    This does the job but appends the text at the immediate end of any previous text within the field.
    My issue is HOW DO I CREATE A LINEBREAK in the memo field.

    Cheers,
    Mark@NAB

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd try either
    the constant vbcrlf (equivalent to Carrige return & line feed [ or perhaps other way round])
    OR faling that
    the ASC() values for Control & Carrige Return
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Are you using lotus script with COM/OLE or is this based in VBA?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jan 2006
    Posts
    10
    Quote Originally Posted by Teddy
    Are you using lotus script with COM/OLE or is this based in VBA?
    Hi Teddy,

    COM/OLE is the stuff..

    cheers,

    Mark

  5. #5
    Join Date
    Jan 2006
    Posts
    10
    Quote Originally Posted by healdem
    I'd try either
    the constant vbcrlf (equivalent to Carrige return & line feed [ or perhaps other way round])
    OR faling that
    the ASC() values for Control & Carrige Return
    HTH
    Hi..I did give the vbcrlf a go. I'm fairly sure I'm getting the opertor wrong though. what operator do I use to indicate this as a command rather than just another piece of text?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    strSQL= "Update tblProjectMain
    Set Comments = Comments " & vbcrlf & "#" & Date1 & "# - '" & Comments1 & "' Where GATE_Number = '" & GATE & "'"
    msgbox strsql 'delete this when you are happy its working
    'you may need to <ctrl> & <lf> in place of vbcrlf
    AccessApp.DoCmd.RunSQL strSQL

    just noticed you were using the + operator - which is no good in Access for string manipulation, use the & instead to merge strings.
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2006
    Posts
    10
    Quote Originally Posted by healdem
    strSQL= "Update tblProjectMain
    Set Comments = Comments " & vbcrlf & "#" & Date1 & "# - '" & Comments1 & "' Where GATE_Number = '" & GATE & "'"
    msgbox strsql 'delete this when you are happy its working
    'you may need to <ctrl> & <lf> in place of vbcrlf
    AccessApp.DoCmd.RunSQL strSQL

    just noticed you were using the + operator - which is no good in Access for string manipulation, use the & instead to merge strings.
    HTH
    Thanks Mark...
    When I code...
    AccessApp.DoCmd.RunSQL "Update tblProjectMain Set Comments = Comments " & vbcrlf & "#" & Date1 & "# - '" & Comments1 & "' Where GATE_Number = '" & GATE & "'"
    ...i get a MS Access syntax error (missing Operator)

    When I code..AccessApp.DoCmd.RunSQL strSQL= "Update tblProjectMain Set Comments = Comments " & vbcrlf & "#" & Date1 & "# - '" & Comments1 & "' Where GATE_Number = '" & GATE & "'"
    ...i get MS Access invalid SQL statement, expected DELETE.....

    Cheers,
    Mark

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    For starters, you've got some quote issues in both of those statements.
    Since this is lotus script and not vba, you would do better to append char(10) & char(13) instead of passing named vba constants...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Jan 2006
    Posts
    10
    Quote Originally Posted by Teddy
    For starters, you've got some quote issues in both of those statements.
    Since this is lotus script and not vba, you would do better to append char(10) & char(13) instead of passing named vba constants...
    Thanks...I got it to work using...
    LB = Chr(10) & Chr(13) & Chr(10).....
    AccessApp.DoCmd.RunSQL " Update tblProjectMain Set Comments = comments + '" & LB & "' + '" & Date1 & "' + ' - ' + '" & Comments1 & "' Where GATE_Number = '" & GATE & "'"

    Thanks for your help...

    Cheers,
    Mark

Posting Permissions

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