Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Thanked 0 Times in 0 Posts

    Update Query (2000)

    I need to create an update query where I place a text string in a field at the end of the existing text that may already be in that field. Is this even possible when using an Update Query? Like always...any help would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Update Query (2000)

    Say that you want to update the field MyField. In the Update To line for that field in the update query, put

    [MyField] & "text to be appended"

    or, if the text to be appended is in another field

    [MyField] & [OtherField]

    etc., vary as needed. You may want to insert a space between the existing text and the text to be appended, for example:

    [MyField] & " " & [OtherField]

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Arlington, Virginia, USA
    Thanked 3 Times in 3 Posts

    Re: Update Query (2000)

    Another option, when I do something like this I like to add a carriage return (or two) between old text & new text (this applicable primarily to memo fields). But if field being updated is currently blank, do not want extra blank line (or space) at beginning of text. So add test for current field being Null or a zero-length string (Nz function covers both possibilities):

    SET TABLE1.COMMENTS = IIf(Len(Nz(<!t>[TABLE1]<!/t>!<!t>[COMMENTS]<!/t>))>0,<!t>[TABLE1]<!/t>!<!t>[COMMENTS]<!/t> & Chr$(13) & Chr$(10) & <!t>[TABLE2]<!/t>!<!t>[COMMENTS]<!/t>,<!t>[TABLE2]<!/t>!<!t>[COMMENTS]<!/t>)

    In example COMMENTS field from TABLE2 is being appended to same field in TABLE1. The conditional statement adds carriage return only if there is currently some text in TABLE1 COMMENTS field, otherwise merely sets value to TABLE2 COMMENTS.


Posting Permissions

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