showing last memo entry in report

[supersonix]

Hello,

I have a db that tracks projects and their details. I have a memo
field entitled 'Notes' which is used to enter project data. This
field can be quite large as notes can be added on various dates. To
keep track of the dates that a particular note was added I simply put
in the after update field on the form the following code to add in the
date that a note was added:

Me.Notes = Me.Notes & " -* " & Format(Date, "dd-mmm-yy") & "*" &
vbCrLf

I currently have a report that I run for the project details and what
I would like is not to have the entire notes field displayed but
rather just the last note for each project. Is this possible? Any
help would be appreciated.

Thanks,

Lalit

 

[John Spencer]

You might try using InStrRev to find the next to last occurence of the * and
truncate the memo field based on that.

Something like the following although you may need to adjust the 15 .
Check the field length and make sure that there is an asterisk in the field

IIF(MemoField & "" Like "*???????????????[*]*" ,MID(MemoField,
InStrRev(Left(MemoField,Len(MemoField)-15),"S",-1,vbTextCompare)),MemoField)

Personnally, I would add a table to the database that had Four fields
PrimaryKey - Autonumber field
ForeignKey - PrimaryKey of the record you are creating the note for
DateField - Date (and possibly time) that the note was created
Note - memo field containing the note

That would make life a lot simpler.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[supersonix]

Thanks for the response John,

I kinda figured the best bet would be to create a separate table for
the notes and that is most likely what I will do but I have a question
regarding the code that gave an example of. Where would that be
entered? In the Format section of that memo field?

You might try using InStrRev to find the next to last occurence of the * and
truncate the memo field based on that.

Something like the following although you may need to adjust the 15 .
Check the field length and make sure that there is an asterisk in the field

IIF(MemoField & "" Like "*???????????????[*]*" ,MID(MemoField,
InStrRev(Left(MemoField,Len(MemoField)-15),"S",-1,vbTextCompare)),MemoField)

Personnally, I would add a table to the database that had Four fields
PrimaryKey - Autonumber field
ForeignKey - PrimaryKey of the record you are creating the note for
DateField - Date (and possibly time) that the note was created
Note - memo field containing the note

That would make life a lot simpler.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hello,

I have a db that tracks projects and their details. I have a memo
field entitled 'Notes' which is used to enter project data. This
field can be quite large as notes can be added on various dates. To
keep track of the dates that a particular note was added I simply put
in the after update field on the form the following code to add in the
date that a note was added:

Me.Notes = Me.Notes & " -* " & Format(Date, "dd-mmm-yy") & "*" &
vbCrLf

I currently have a report that I run for the project details and what
I would like is not to have the entire notes field displayed but
rather just the last note for each project. Is this possible? Any
help would be appreciated.

Lalit

 

[John Spencer]

No, in the query that you are using to get the records for the report.

Field: LastMemo: IIF(MemoField & "" Like "*???????????????[*]*"
,MID(MemoField,InStrRev(Left(MemoField,Len(MemoField)-15),"S",-1,vbTextCompare)),MemoField)

Hopefully that UNTESTED expression will work for you.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks for the response John,

I kinda figured the best bet would be to create a separate table for
the notes and that is most likely what I will do but I have a question
regarding the code that gave an example of. Where would that be
entered? In the Format section of that memo field?

You might try using InStrRev to find the next to last occurence of the * and
truncate the memo field based on that.

Something like the following although you may need to adjust the 15 .
Check the field length and make sure that there is an asterisk in the field

IIF(MemoField & "" Like "*???????????????[*]*" ,MID(MemoField,
InStrRev(Left(MemoField,Len(MemoField)-15),"S",-1,vbTextCompare)),MemoField)

Personnally, I would add a table to the database that had Four fields
PrimaryKey - Autonumber field
ForeignKey - PrimaryKey of the record you are creating the note for
DateField - Date (and possibly time) that the note was created
Note - memo field containing the note

That would make life a lot simpler.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hello,
I have a db that tracks projects and their details. I have a memo
field entitled 'Notes' which is used to enter project data. This
field can be quite large as notes can be added on various dates. To
keep track of the dates that a particular note was added I simply put
in the after update field on the form the following code to add in the
date that a note was added:
Me.Notes = Me.Notes & " -* " & Format(Date, "dd-mmm-yy") & "*" &
vbCrLf
I currently have a report that I run for the project details and what
I would like is not to have the entire notes field displayed but
rather just the last note for each project. Is this possible? Any
help would be appreciated.
Thanks,
Lalit