Strip Query

[HelsBels]

Hello

I am having problems with a strip query. I am trying to export some data from access into a text file. Operators using the front end screen of access enter the full workphone into the workphone field, however, the text file must separate the std field from the rest of the phone field. There is a spare field in access which operators don't see called workphonestd but this field is not populated.

The query that I am using uses these two functions to strip the phone:

wphonestd: strip([workphonestd])
workphone: strip([workphone])

However, when I run the query, the workphonestd field remains blank and the workphone field is still populated with the full phone number including std.

Basically, can someone suggest a formula that will strip the std field from the workphone field and place it in the workphone std field and which will then also leave the workphone field with the number only (excluding std). I don't know if this makes any difference but the workphone field has got a space in between the std and number (i.e 01923 456123).

Thanks in advance

Helen

 

[Fizzio]

If you are using Access 2k you can use the split function. If not, you can use a simple bit of code.

Have a look at the demo I posted here that uses a function to split up a string and return the results. You can easily modify this to your own means.

 

[HelsBels]

Split Function

Hello

I got a reply to my question from Fizzio about a split function but I'm sorry I don't understand it! I have access 97 so I need the code but I have checked over the reply and just can't see the code that I need to use.

Just to remind you, I have two fields in access. One is called workphone std and one is called workphone. Unfortunately, operators using the front end screen of access don't see the workphone std field, they just see workphone and so they enter the full number into that field. However, when i do an export file I have to separate the std from the rest of the number. The std part needs to go into the workphone std field and the rest of the data remains in the workphone field.

Can someone provide me with a code that will do this. I don't think I can do a function like this: workphonestd: Left([workphone],4) because the std codes are sometimes 4 characters long and sometimes 5. There is also a space in between the std and phone number.

 

[MattS]

What about:

wphonestd: Trim(Left([Workphone],InStr([Workphone]," ")))
wphone: Trim(Right([Workphone],Len([Workphone])-InStr([Workphone]," ")))

 

[HelsBels]

Hello

That seems to work except that when it says it is going to update the records it then gives the error message

Microsoft can’t add all the records in the update or append query.
It sent 10 fields to null due to a type conversion failure. Data in 1 or more fields may not match the field size or data type in the destination table Do you want to ignore .

When I click yes and ask it to update I checked the file and it appears ok - do you know why it would bring back this error. It has definitely split the workphone field into two now and it looks ok. I checked the orginal table and the workstd was 6 characters long (text) and the workphone was 25 characters long. I amended this in my destination table but it still brought back the error message?