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

Copy part of data to another field

P: 1
I have a database with a memo field that has the name of a question then a {return} and then the question.
Belatedly I decided that I need this "coded" name in a separate file as well.
The name is formatted for Moodle so it looks like
::AAn nn nna:: and then a hard return.
What I want is only the information between the "::" The length of the information between these marks is not consistent, it may be shorter or longer. I plan to use and Update query but need a formula that will work for this.
Call the from field "Question" and the to field "QName"
Thanks for any help out there
May 16 '14 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 1,240
Welcome to Bytes. Glad to see another new poster.

You can use a bunch of functions to get what you want. Instr gives the location of a string in another string, Replace changes one string to another string, Left returns x number of characters from the left, and Mid returns a string starting at a position in another string.

And you'll need Cstr because some of these functions don't work with Memo fields. And I believe some memo fields may be too large for this to work.

The first part (Qname?) will equal
Expand|Select|Wrap|Line Numbers
  1. Replace(Left(Cstr(memofieldname),instr(cstr(memofieldname),chr(13))-1),"::","")
The the remainder can be gotten with
Expand|Select|Wrap|Line Numbers
  1. Mid(Cstr(memofieldname),instr(Cstr(memofieldname),chr(10)+1))
One key to this is knowing that the hard return in your memo field is made up of ascii codes 10 and 13. The only way to reference them is to use their ascii codes in a Chr() instruction.

I hope I got all the () matched up, I haven't tried that code.

May 16 '14 #2

Post your reply

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