Automating Record Notes

[MaliciousMike]

Hello,

I'm incharge of development on our access database at work and i've progressed from hardly any knowledge of access to what our database creater explains as "advanced". I disagree, because there's still so much i don't know yet.

Anyway...

We have a notes field for every record in our database where certain decypherable noted are typed by hand by a call centre.
The notes look a little like this:

1. 22/08 EVE RL
2. 23/08 PM KC

Sometimes the notes can go upto 12 events with different situations, but all I would like to automate is the standard note:
[number of note]. [date] [time of day] [initials].

I can do the date easily, i can do the time of day with a few if statements, i can even do the initials with a table and a query.
What i can't seem to get is the number.

Basically what i want my button to do is to get the number of the last note, add 1 and add the rest of the data to it adding it to the notes field.

I'm stumped on how i can get the number from code.

 

[MarkK]

Check out the DMax() function, and add 1.

 

[MaliciousMike]

i read through the msdn tutorial for the DMax and DMin function, and i don't think they are what i'm looking for.

All of the notes are in the same field. It's not a sub-table.

 

[ghudson]

Here is a thought...

You would need to break the note up into four different fields. Then you can use the DMax() to calculate the next highest number. The notes could be stored in a different table and displayed as a subform in the form to show the related notes for the current record.

 

[RCurtin]

Hi,
Is the number of the note the first number in the string? For instance do you need to extract the number 2 in this example?:
2. 23/08 PM KC

If you need to extract that you need to use string functions.
For instance use inStr to find the position of the full stop and the Left function to return the characters before it. (You can't just return the first character because there could be 2 or 3 characters in it)

Then you convert the 2 which is seen as text to a number using the Val function.

 

[ghudson]

Hi,
Is the number of the note the first number in the string? For instance do you need to extract the number 2 in this example?:
2. 23/08 PM KC

If you need to extract that you need to use string functions.
For instance use inStr to find the position of the full stop and the Left function to return the characters before it. (You can't just return the first character because there could be 2 or 3 characters in it)

Then you convert the 2 which is seen as text to a number using the Val function.

Not that simple when the notes "events" are going to all be in one field of the record. How would you get the next number when a record might already have a 1., 2., 3., 4., 5., 6., 7., 8., etc. in the notes field for just one record. That is why I suggested that he break the four parts of a note out into a new field and store the data in another table.

 

[MaliciousMike]

i want the first character in the last line of the string.

once i have that number i can add a button in my form to add a new note on a new line.

 

[MaliciousMike]

I've been looking more into this and i found the Right([field], length) function.

As the memo field varies in length significantly, it might not be the best function to use, but is there code i could put in the function to get only the top line of the memo?

Perhaps if i have less to work with it can be done easier.