printable size of memo field

[Roger]

Hi all...
I'm a newby with a 'memo' field, question. I have made a database with
a memo field for inputting engineers reports on a weekly basis. Can I
make the older entries roll off when I enter the latest entries so
controlling the printout size of the memo field?
At present the printout can go into several pages of engineers history
which is not required.

Rojen

 

[John Spencer]

A more robust solution would be to add a table to hold the memo and a datetime
field for the memo and a field to relate the memo field to the report table.
That way you could print all the memos associated with a report, just the latest
memo associated with the report, or the last five (or other number).

The only way to do it with one memo field is to have some string of characters
that indicates the break between entries and then use vba to find that break and
delete all characters between the break and the end or beginning of the memo field.

How is the data entered into the memo field now? That is what does it look like?

Do you add new data at the beginning of the memo field? At the end of the memo
field? How do you decide which entries to keep? Do you want to keep all the
data, but just display some of it?

 

[Roger]

John

I enter the data by copy typing from the engineers report like this:
05/02/06 Serviced/3658/256214 Dave - {6957425/125 repair BGU's}
06/02/06 Repair/3568/365895 Roger - {695874/001 supply only}
06/02/06 Callout/2556/789456 Phil - {695365/005 water leak}

It is a field that is only used as a reference to see a history of the
site.
The numbers are document and job number references which are used to
file the scanned copies of the original engineer sheets.

The problem I have is: a window is shown on the database which expands
when printed to include all of the entries made.
I would like to retain the window at the original size but only print
out the latest 12 entries whilst keeping all of the history within the
database. I can achieve the result I want by making the 'Can grow' to
'no' but it prints out the oldest entries not the newest.

Can you help?

 

[John Spencer]

You either have to put the NEWEST items on top instead of at the bottom or
do as I suggested and add a table to hold this information. The table might
be something like:

ActionDate - date field
ActionType - Text field (Serviced, Repaired, Callout)
Details (althought this looks as if it could be multiple fields - Name of
engineer, action taken, etc)

If you have all the data in one memo field as you do now, there is nothing
that I know that you can do to select the most recent items and print them
only unless you use some vba code to split the data out into multiple lines
and then select the latest.

Assumptions:
Each piece of data has a carriage return line feed
You are using Access 2000 or later.

Add the following code to a module and then call it in your query. DO NOT
name the module the same as the function or you will get an error.

Field: JustTheLatest: getTheLatest([YourMemoField],12)



Public Function getTheLatest(txtIN, Optional iLines As Integer = 6) As
String
Dim strOut As String, iLoop As Integer
Dim iStart As Integer, iEnd As Integer
Dim strArray As Variant

If Len(txtIN & vbNullString) = 0 Then
getTheLatest = vbNullString
Else
strArray = Split(txtIN, Chr(13) & Chr(10), -1, vbTextCompare)

iEnd = UBound(strArray)
iStart = iEnd - iLines + 1

If iStart < LBound(strArray) Then iStart = LBound(strArray)

For iLoop = iStart To iEnd
strOut = strOut & strArray(iLoop) & Chr(13) & Chr(10)
Next iLoop
getTheLatest = strOut
End If
End Function

 

[Roger]

John thanks for the fast reply.
Just looking through my options now. I'll try the additional table
option first and see how I get on.

Most appreciated.

Roger