473,327 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Copy part of data to another field

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
1 1876
jimatqsi
1,271 Expert 1GB
Squires,
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),"::","")
  2.  
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.

Jim
May 16 '14 #2

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

Similar topics

5
by: Paul Lamonby | last post by:
Hi, i want to create a unique serial number to my Db entries. I thought the best way would be to add the auto_increment primary key value to a string, then insert it into a table field...
4
by: Andy Hutchings | last post by:
Hi everybody - hope you can help out here. I have a form in a database, which is a columnar form from one of the tables in the db - there is a sub-form to the form which is a datasheet view of...
1
by: Alienz | last post by:
I'm sure this is easy so if you have a sec pls help me out thx. Let say I have an inventory table and at the end of the month I want to move the info in 1 field (total end of month 1) into...
7
by: jballard | last post by:
Hello, I have a database set-up with a form and two subforms in it. I have one of the subforms (replacement parts) set-up where you can pick part numbers from a drop down box and also pick a...
6
by: geronimo_me | last post by:
Hi, I'm trying to compare two TITLE fields from different tables to locate anomolies - however Table1 TITLE field contains a dot(.) after some of the entries, however i want to remove the dot so...
9
by: geronimo_me | last post by:
Hi, I am atempting to compare part of a field with the whole of another field in access. Is this possible? Basically I have 2 tables with the following info: Table1 Field1 = MR.
7
by: MN | last post by:
I am using a program called IDWorks which is reading and writing to an MS Access format database. The IDWorks program can't adjust the formatting on a displayed field in a way that we need so I...
13
by: blangela | last post by:
I have decided (see earlier post) to paste my Word doc here so that it will be simpler for people to provide feedback (by directly inserting their comments in the post). I will post it in 3 parts...
8
by: ivijayan | last post by:
HI. I have the following query:- How do i create field which updates its contents on the basis of data in another field in same record. especially if I have a Date field, how can I change the...
1
by: vishakha | last post by:
Hi.. I m using sql server standard edition. I want to copy table data of 's1' from 'test1.dbo' into another table 's2' which is exist in another database 'test2.dbo'. I m using import option to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.