Add a return value inside alias query

[travismp]

I am need to combine 2 memo fields from the same table.

MEMO: [SpecialComments]+"  -   "+[CompNotesInternal]

Can I take out the " - " and somehow add a return key? If I do the merge right now it shows one continuous string of text. I would rather they have two different lines. Thanks.

 

[raskew]

Hi -

Try this (untested):

Memo: trim([SpecialComments]) & vbCrLf & trim([CompNotesInternal])

Bob

 

[travismp]

raskew thank you for the try but it did not work. it changes it to

Memo: Trim([Special Comments]) & [vbCrLf] & Trim([CompNotesInternal])

it then looks for a field called "vbCrLf"

anything else?

 

[dkinley]

I don't think the vbCrLf can be passed from a query ...

Why not just concatenate the fields in the report itself (with the code Raskew is using)?

-dK

 

[travismp]

I have 2 memo fields that I need to combine into one memo field in the table.

 

[dkinley]

Oh ... the vbCrLf is a constant for VBA ... might want to try actual character codes then ... carriage return: Chr(10) and line feed: Chr(13).

Memo: Trim([Special Comments]) & Chr(10) & Chr(13) & Trim([CompNotesInternal])

I thought was in a report not combining them in and putting them back into a table.

-dK

 

[WayneRyan]

DK,

Thanks for stepping up, Bob's obviously distraught over that other thread ...

But don't Memo fields get truncated in certain kinds of queries?

Bob, still lookin'
Wayne

 

[dkinley]

At least in online articles I've read, truncation and corruption occurs in memo fields with queries (maybe certain types of queries - can't recall) ran on them - I don't have any data to confirm or deny the authors' opinions (it could just be people with an axe to grind against Access, I've no idea), because it appears in some articles but not others. Instead ... in my opinion and practice I only use them to store inconsequential data, anything pertinent goes into a text field.

I am not questioning the why of collapsing memo fields into a single field for storage when they could just be put together as-needed for presentation in a form or a report - just trying to help user out to accomplish immediate goals.

-dK

 

[WayneRyan]

DK,

You're absolutely right!

I was just trying to give Bob a hard time.

But, there are certain queries (Union?) that lead to truncation of memo
fields. Just thought I'd bring that up in the interim.

The Chr(13) & Chr(10) is the way to go. Why can't queries understand VBA?

Note the corrected order,
Wayne

just kidding 'bout the order ... Old School

 

[dkinley]

Why can't queries understand VBA?

Better yet, why can't they just understand English or mental telepathy?

-dK

 

[WayneRyan]

DK,

They don't even know vbaGlogals, let alone VBAEnglish or VBA Telepathy.


But, Bob still needs your help.

http://www.access-programmers.co.uk/forums/showthread.php?t=155586

Wayne

 

[dkinley]

Hehehe ... I just made an offer as an 'Oh Yeah! That could have happened ... '

I stay out of the way on here -- just trying to return the help from all the knowledge I've siphoned off this site in the past.

-dK

 

[travismp]

DK & WAYNE FOR THE WIN!!! That worked great. Thank you both for the fix. That worked great and I say THANK YOU to both of you for the help tonight.

 

[WayneRyan]

Travis,

DK did the work, I was just along for the ride.

Wayne

 

[dkinley]

You betcha .. glad it worked -- I guessed at that solution and was hoping you wouldn't come back with a 'what next?' =]

-dK

 

[raskew]

Hey -

Glad it all worked out in the end. As Wayne so politely pointed out, I am just slightly distraught. Don't like to give out incorrect answers. Would have tested it out had I had a table with two memo fields.

But, on the upside I was apparently 2/3 correct. Got the left side and the right side correct, it was just the stuff in the middle that got muddled.

Wayne, my special thanks for your kind encouragement!

Best wishes to all - Bob

 

[tranchemontaigne]

Hmmmmm - I was attempting to do the same thing with MS Access 2000.....I'm having rendering problems. Each carriage return and line feed is displayed on the report as a box within a text string. Here is the background info:

(1) I attempted concatenating values within an unbound text box on the face of the report and found that that value displayed within the unbound text box was "#error#". This happened regardless of whether I typed formulas by hand or used the expression builder.

(2) Next I attempted to perform the concatenation within the recordsource property of the entire report

I attempted this using both the chr(10) and chr(13) fields and this fails too.

(3) I searched the web for solutions and found this similar post

my rowsource looks like this

'carriage returns have been entered into the recordsource property within this code block to improve readability within this post - all ASCII character codes are the same within the original and code snippet
 
.recordsource = "SELECT 
[t02_Inventory].[t02_Inventory_ID], 
[t01_Description] & Chr(13) & Chr(13) & [t02_Name] & Chr(13) & "(" & 
[t02_Acronym] & ")" AS Program_Area, 
"Overview" & Chr(10) & Chr(10) & [t02_Overview] & Chr(10) & Chr(10) & Chr(10) & Chr(10) & "Assumptions" & Chr(10) & Chr(10) & [t02_Assumptions] AS Overview_Assumptions, 
[t02_Inventory].[t02_Assumptions] 
FROM t01_Codes 
INNER JOIN t02_Inventory 
ON [t01_Codes].[t01_Codes_ID]=[t02_Inventory].[t02_Bureau_ID] 
WHERE ((([t02_Inventory].[t02_Assumptions]) Is Not Null)); "

________
STARCRAFT 2 REPLAYS