Splitting data question 1

[WB786]

I have couple of fields in this table that I didn't design and would like to split the data in two seperate fields. Here is what I need help on:

$5,000-$10,000 --> Field1: 5000
Field2: 10000

And I need some fields with phone number data with dashes to be stored in my new field with the property of numeric without the dashes:

123-456-7890 --> 1234567890

This update needs to happen via a query.

Thanks,
WB

 

[JonFer]

Use a combination of the Left(), Mid(), and InStr() functions to do the field splitting. To get the first number, use something like this:

Left([Field],Instr([Field],"-&quot-1)

You should be able to get the second number by modifying the above expression.

As for the phone number, why would you store it in a numeric field - to prevent letters? I would do that with an input mask. I only use numeric fields for values that represent amounts I'll be performing arithmetic calculations on. If you are using Access 2000, look up the Replace() function to remove the dashes.

 

[WB786]

My phone number fields are set to numeric. I was also given the task of importing some Excel data that had dashes so I needed to strip the dashes and store that value in another field before uploading the data to the SQL server. I was able to find the solution for that on Microsoft website which had me make a module and use that in an update query. So that worked.

I was able to use your example above and seperate the values the way I wanted to as well.

Thanks you for your help!!

WB

 

[votegop]

Wabeg, how 'bout a star for JonFer's help?

 

[WB786]

Sorry 'bout that. START IT IS!!!!

WB

WB