By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,325 Members | 1,213 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,325 IT Pros & Developers. It's quick & easy.

How can I pick up part of a memo field from a Query and append it to the next record

P: n/a
I have a database wherein a lot of fields are updated by my team on a daily basis. When they are working on a lot of rows of same patient and certain fields have to be filled with same data, to save time I have made a Duplicate button. When this button is clicked, certain fields of the next record are auto-filled as duplicate of the last record. I bring the last record using a query "Aging 010109- Query For DLast," which is working fine. I am able to duplicate the fields which are to be duplicated completely; however, in "Remarks" field (which contains remarks for various dates) I just want to append only the most recently worked to the next record's "Remarks" and not the complete "Remarks." The separator can be date (MM-dd-yy). I used the following event procedure.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Duplicate_Click()
  2. 'when click Duplicate record and go to Next record and duplicate the following fields.
  3. DoCmd.GoToRecord , , acNext
  4. If ([DOB] = DLast("DOB", "Aging 010109- Query For DLast"))Then
  5. 'Duplicate the FollowupOn
  6. [FollowupOn] = DLast("FollowupOn", "Aging 010109- Query For DLast")
  7. 'Append recent Remarks to the next record.
  8. [Remarks] = DLast("Remarks", "Aging 010109- Query For DLast")
  9. This duplicates the complete Remarks……….
I would really appreciate if somebody can help me to duplicate just some lines of “Remarks” which are on the left of today’s date (DD-MM-YY). Following is just an example of how we put remarks.
Insurance denied the claim for medical records. Records retrieved and faxed 10-23-10
The claim is under review. Need to followup in 15 days ..pj 10-08-10
Thanks everybody for your help.

Oct 23 '10 #1
Share this Question
Share on Google+
3 Replies

Expert 5K+
P: 8,750
Expand|Select|Wrap|Line Numbers
  1. 'From a MEMO Field, will pick up from the Current Date on assuming the Date is in
  2. 'the Format mm-dd-yy as in 10-23-10.
  3. [Remarks] = Mid$(DLast("Remarks", "Aging 010109- Query For DLast"), _
  4.             InStr(DLast("Remarks", "Aging 010109- Query For DLast"), Format$(Date, "mm-dd-yy")))
Oct 23 '10 #2

P: n/a

Thanks for your post ADezii. I could find the answer. I would really appreciate if you can just see if that's fine. What I did was following:
'Get the last Remarks.
Dim FTempRemarks As String
FTempRemarks = DLast("Remarks", "Aging 010109- Query For DLast")
[Remarks] = Left([FTempRemarks], InStr(1, [FTempRemarks], vbCrLf) - 1) & vbCrLf & vbCrLf & Me.Remarks

I chose a line break instead of a date because that gave me more flexibility.

Thanks for your efforts and have a nice day,

Oct 25 '10 #3

Expert 5K+
P: 8,750
You are quite welcome, and have a nice day yourself.
Oct 25 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.