Comment History Sort order (1 Viewer)

W

William Demuth

Registered User.

Local time

Today, 18:52

Joined

Feb 16, 2011

Messages

35

• Aug 28, 2013

• #1

Howdy all

I have after much stress gotten my comments history field to appear on a form.

The following code gives me what I needed

Code:Copy to clipboard

[B]=ColumnHistory([RecordSource],"LastUpdateBy","[ID]=" & Nz([ID],0))[/B]

I have only one simple thing left, that is perplexing to say the least.

Some comment fields will be many short notes, spread over time.

My desire is to change the sort order of the results so the most recent (rather than the initial) comment is at the top.

Is this even possible, and can anyone point me in the right direction?

 

pr2-eugin

Super Moderator

Local time

Today, 23:52

Joined

Nov 30, 2011

Messages

8,494

• Aug 28, 2013

• #2

Could you provide us with the code for ColumnHistory? Please use Code Tags when posting VBA Code

 

C

CJ_London

Super Moderator

Staff member

Local time

Today, 23:52

Joined

Feb 19, 2013

Messages

16,607

• Aug 28, 2013

• #3

I haven't used columnhistory, but the string produced presumably has a vbCrLf at the end of each line.

Assuming this is the case you should be able to split the string using the split function, pass it through the sort array function on this link

http://www.access-programmers.co.uk/forums/showthread.php?p=975589

and convert back to a string again for displaying on your form or report.

Something like:

Code:Copy to clipboard

Dim StrArray() as string
Dim Sorted as string
Dim i as integer
 
StrArray=Split(ColumnHistory([RecordSource],"LastUpdateBy","[ID]=" & Nz([ID],0)),vbCrLf)
StrArray=sortarray(StrArray)
for i=0 to ubound(StrArray)-1
    Sorted=Sorted & StrArray(i) & vbCrLf
next i
me.txtColumnHistory.controlsource="=""" & sorted & """"

You'll need to copy the sortarray function into your application

 

C

CJ_London

Super Moderator

Staff member

Local time

Today, 23:52

Joined

Feb 19, 2013

Messages

16,607

• Aug 28, 2013

• #4

Paul:Could you provide us with the code for ColumnHistory?

ColumnHistory is a new functionality for memo fields available in Access 2007.

 

pr2-eugin

Super Moderator

Local time

Today, 23:52

Joined

Nov 30, 2011

Messages

8,494

• Aug 28, 2013

• #5

CJ_London said:

ColumnHistory is a new functionality for memo fields available in Access 2007.

Click to expand...

Ah ! I see.. Never used it, no wonder I did not know that.. Thanks for clearing that CJ..

Sorry William for butting in..

 

W

William Demuth

Registered User.

Local time

Today, 18:52

Joined

Feb 16, 2011

Messages

35

• Aug 28, 2013

• #6

The data is displayed in the form via a text box.

I just want to reverse the order in one particular box.

Can that be done on the form setup?

It seems intuitive it should be possible, which usually means it isn't.

I can parse the data, but reversing it would seem elegant!

Any clues?

 

B

BC_Seattle

Registered User.

Local time

Today, 15:52

Joined

Jun 18, 2019

Messages

24

• Jun 18, 2019

• #7

Hello, I know this is a really old post and thought maybe this would help someone else find something that might be of use. I found this function in my Form Property Sheet where I was able to tell it to Order By: [Comments].[Date] DESC, [Lookup_Owner].[Employee Name] -Attached Screenshot

I am no pro or anything just teaching myself how to use and create Access for my needs. What I was looking for is my "new" box is down at the bottom so on several note posts I have to scroll all the way to the bottom to enter a new comment. I wanted to see if there was a way to make this stay at the top. Anyway the search continues.

 

Attachments

• 
  Access.JPG

  39.1 KB · Views: 161