2 Replies Latest reply: May 15, 2014 10:07 AM by Data Kruncher RSS

    Calculated field - trigger new line in memo

    consu _

      I'm a v10.5 Pro user, and enjoying the calculated field functionality. It is simply just spectacular!!! I need to generate a memo field, based on values exisiting in columns. I would like the values to be structured with a new line, instead of having the text just filling the field. In access I use chr(13)&chr(10) to achieve this, but I was not able to figure out how to do same in Monarch. I was hoping to do it on the source data, rather than in Access afterwards!

      Probably a simple solution, but I did not find a clue in manual or the forum on the subject. Thanks in advance!

       

      Calculated field code sample:

      "Location.: "chr(13)chr(10)"Multi ID.:"chr(13)chr(10)"System.: "+

       

      Result is:

      Location.: HIGH PRESSURE CO2 FIRE  Multi ID.: 1 System.: CO2 FIRE EXTINGUISHING SYSTEM NK

       

      But I wish for this result:

      Location.: HIGH PRESSURE CO2 FIRE

      Multi ID.: 1

      System.: CO2 FIRE EXTINGUISHING SYSTEM NK

        • Calculated field - trigger new line in memo
          Olly Bond

          Hello consu,

           

          If you use this formula, and export the table data to fixed-width text, you get the result you were looking for. Using v9 Pro, exporting the table to XLS or CSV doesn't preserve the line break.

           

          You could try putting in codes like "
          " into the memo field, instead of the CHR values, and then having a macro in Access that replacing these codes automatically with CHR 13, CHR 10.

           

          Best wishes,

           

          Olly

            • Calculated field - trigger new line in memo
              Data Kruncher

              consu,

               

              You're absolutely on the right track with your approach, and it should have worked as you intended.

               

              Unfortunately, as you discovered, it didn't, and - for now at least - it won't (see my first reply, #2, in [URL="http://www.monarchforums.com/showthread.php?t=3143"]this thread[/URL]).

               

              Olly has supplied a great option. Just be sure to add some very unique string to your data so that Access can handle the carriage return and new line that you'd intended Monarch to handle. Something like "QWQWQWQ" or some such string that won't ever appear in any normal data.

               

              Excel has a VBA constant that Access ought to have as well, but I haven't verified it: vbCrLF.

               

              Kruncher