Split a text string using a query and depending on its content

[JamesBond]

Hi, I am relatively new to Access. I imported a very large csv file into a table. One field relates to phone numbers. Some records have 2 phone numbers others have only 1 phone number. Where there are 2 numbers they are split by a ":" Here is an example

PhoneNos
089 4070198
087 3983530
089 4843365
086 1027601
083 1987275
087 3377219:+44 7909 947901
087 3899103
086 1047498
089 9616322
085 7799250:+44 7563 076923
00894 962918:00894 962918

I want to split the phone field into 2 fields. I tried using Left() and Mid(). However if the record has only 1 phone number I get an error message. Here is an example of the result

FirstPhone	MidPhone
#Error	089 4070198
#Error	087 3983530
#Error	089 4843365
#Error	086 1027601
#Error	083 1987275
087 3377219	+44 7909 947901
#Error	087 3899103
#Error	086 1047498
#Error	089 9616322
085 7799250	+44 7563 076923
00894 962918	00894 962918

This is the way I would like to see the data returned

FirstPhone	MidPhone
089 4070198
087 3983530
089 4843365
086 1027601
083 1987275
087 3377219	+44 7909 947901
087 3899103
086 1047498
089 9616322
085 7799250	+44 7563 076923
00894 962918

I would appreciate any help thank you in advance

[Bob Fitz]

Can you tell us the name of the table and the name of the field

[JamesBond]

Sorry I should have included those details.

Table is: tbMaster
Field is: Phones

[Bob Fitz]

Try the following as the SQL of your query:
SELECT tbMaster.Phones, IIf(InStr(1,[Phones],":")-1=-1,[Phones]) AS FirstPhone, IIf(IsNull([fp]),Right([Phones],InStrRev([Phones],":")),"") AS MidPhone FROM tbMaster;

[JamesBond]

Hi Bob
Thank you for that, it is almost there but not quite. As you can see below if there is just 1 number in the PhoneNos field it puts it in the FirstPhone field. However if there are 2 numbers in PhoneNos it does not put the first number in FirstPhone and it does not copy the full text of the second number into SecondPhone and in some cases it copies the ":" as part of the SecondPhone. Thank you for your help with this.

PhoneNos	FirstPhone	SecondPhone
089 9616322	089 9616322
085 7799250:+44 7563 076923		7563 076923
00894 962918:00894 962918		:00894 962918

[Bob Fitz]

Try:
SELECT tbMaster.Phones, IIf(InStr(1,[Phones],":")-1=-1,[Phones],Left([phones],InStr(1,[Phones],":")-1)) AS FirstPhone, IIf(InStr(1,Phones],":")-1=-1,"",Right([Phones],Len([phones])-InStrRev([Phones],":",-1)-1)) AS SecondPhone FROM tbMaster;

[JamesBond]

Bob that works perfectly now thank you for all your help much appreciated

[Bob Fitz]

Bob that works perfectly now thank you for all your help much appreciated

You're welcome. Glad to be of help