String Query (1 Viewer)

[Snowflake68]

Hi I have inherited a database that I will need to develop at some point but in the meantime I am trying to understand some of the code within it.

Can anyone help with understand the syntax below which is part of a query that displays the last note added to a memo field in a table. The data in the table has carriage returns with many user notes added.

Trim(Right(Replace([Task Update],Chr(10),String(500," ")),500))

I can see the results and can understand a little with how it works but I am trying to get my head around how the syntax it made up. I have tried breaking this down into separate sections but I am still a little puzzled as to how it all works together, especially the 'String' bit.
I know that Trim removes leading and trailing spaces and I can see that the right function is taking the last 500 characters of the Notes field but its just the replace bit that really puzzles me.

Can anyone help explain the whole thing please.

Thanks

 

[isladogs]

Trim(Right(Replace([Task Update],Chr(10),String(500," ")),500))

OK taking it bit by bit:

Chr(10) = line feed character (new line)
String(500),"") creates a string of 500 spaces

so Replace([Task Update],Chr(10),String(500," ")
replaces all line feeds in Task Update field with 500 spaces

Right(...,500) gets the last 500 characters in the above expression (which could well be spaces)

Trim removes all spaces at start and end

Now if I'm reading that correctly it means that if the Task Update field ends with a line field the result will be an empty string
Otherwise it will be the last 500 characters in the field so omitting any leading spaces

If so, that is a ridiculously complicated way of getting the last line of the field!

 

[Snowflake68]

Trim(Right(Replace([Task Update],Chr(10),String(500," ")),500))

OK taking it bit by bit:

Chr(10) = line feed character (new line)
String(500),"") creates a string of 500 spaces

so Replace([Task Update],Chr(10),String(500," ")
replaces all line feeds in Task Update field with 500 spaces

Right(...,500) gets the last 500 characters in the above expression (which could well be spaces)

Trim removes all spaces at start and end

Now if I'm reading that correctly it means that if the Task Update field ends with a line field the result will be an empty string
Otherwise it will be the last 500 characters in the field so omitting any leading spaces

If so, that is a ridiculously complicated way of getting the last line of the field!

Thanks for the reply Colin.

I would be very interested to know if you have a better way of getting the last line of the field. As said this is not my code and I am still learning.

 

[isladogs]

Send me a coupe of examples and I'll provide a better version

Also check whether Task Update is a text field or a memo (long text) field

 

[isladogs]

Don't worry I've done it...

I created a table with 2 records then ran the following query to get the last line

SELECT DISTINCT Table1.ID, Table1.[Task Update], Replace([Task Update],Chr(10),"|") AS Expr1, InStrRev(Replace([Task Update],Chr(10),"|"),"|") AS Expr2, Mid(Replace([Task Update],Chr(10),"|"),InStrRev(Replace([Task Update],Chr(10),"|"),"|")+1) AS Expr3
FROM Table1;

The expression in the final column does the job

Mid(Replace([Task Update],Chr(10),"|"),InStrRev(Replace([Task Update],Chr(10),"|"),"|")+1)

Its certainly no simpler, but its quicker for a computer to process

There are many other ways of doing this e.g. use of split function
I expect someone else will provide a neater version

 

[Snowflake68]

Don't worry I've done it...

I created a table with 2 records then ran the following query to get the last line

SELECT DISTINCT Table1.ID, Table1.[Task Update], Replace([Task Update],Chr(10),"|") AS Expr1, InStrRev(Replace([Task Update],Chr(10),"|"),"|") AS Expr2, Mid(Replace([Task Update],Chr(10),"|"),InStrRev(Replace([Task Update],Chr(10),"|"),"|")+1) AS Expr3
FROM Table1;

The expression in the final column does the job

Mid(Replace([Task Update],Chr(10),"|"),InStrRev(Replace([Task Update],Chr(10),"|"),"|")+1)

Its certainly no simpler, but its quicker for a computer to process

There are many other ways of doing this e.g. use of split function
I expect someone else will provide a neater version

Brilliant, you are a genius and made me laugh at the same time (the mind boggles)

 

[isladogs]

I aim to please ...

BTW I deliberately added a blank line to check if it would cause problems - it didn't