First 12 digits from one field into another

[jomuir]

I am having massive problems with data – I have imported data only to find out that Access has not imported all of it as there is a max of 255 fields, and I have loads more!!

I think I can get round this by importing without the delimited or fixed widths into a memo field but this gives me another problem, the the first 12 digits are the ‘Ref_No’

So I have a table called tblCustMemo that I have imported, and I have 3 fields, ID (Primary Key), Ref_No (new and totally empty) and Memo.

How can I put the first 12 characters from the memo field into the Ref_No field?
Then remove the first 13 characters from the memo field (there is a space after the Customers reference number)

I presume I would do this by update queries, but I am currently at a loss!!


I tried this, just to get the first 13 characters then was going to try update query, but this only returned 7 records out of 8687!!

SELECT Custmemo_TEST.ID, Custmemo_TEST.Ref_No, Custmemo_TEST.Memo
FROM Custmemo_TEST
WHERE (((Custmemo_TEST.Memo)=Left([Custmemo_TEST]![Memo],13)));

 

[Darren Bartrup]

Hi Jomuir,

You'll need two queries to accomplish this.

The first query will update your Ref_No field:

UPDATE tblCustMemo SET tblCustMemo.Ref_No = Left([Memo],13);

And the second will remove the characters from your memo field:

UPDATE tblCustMemo SET tblCustMemo.[Memo] = IIf(Len([memo])<=12,[memo],Right([memo],Len([memo])-12));

I've put the Iif statement in incase a memo field is less than 12 characters long - in which case it leaves the field as it is.

Your SELECT query failed as the

WHERE (((Custmemo_TEST.Memo)=Left([Custmemo_TEST]![Memo],13)));

told the query to bring back only the records where the entire field was equal to the first 13 characters, so in your 8687 records there were 7 records that were 13 characters long (or less) in total.

 

[ajetrumpet]

How can I put the first 12 characters from the memo field into the Ref_No field?
Then remove the first 13 characters from the memo field

To expand a bit on this...you can do it all in one query if you can read the nested code for it...

UPDATE table SET table.refnumber = Left ([memofield], 12), 

table.memofield = Right ([memofield], Len ([memofield] - 13)