Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Split Access Field

Posted on 2009-04-28
Medium Priority
232 Views
i have a field called Field1 the value of field1 is

41115455445151&2/16/2009&123&demo

I need to seperate this field at the &

Results would be

Newfield1 = 41115455445151
Newfield2 = 2/16/2009
NewField3 = 123
NewField4 = demo
0
Question by:wellsuited
• 2

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 24255808
If there are always 3 ampersands...

SELECT Field1, Left(Field1, InStr(1, Field1, "&") - 1) AS Newfield1,
Mid(Field1, InStr(1, Field1, "&") + 1, InStrRev(Field1, "&") - InStr(1, Field1, "&") - 1) AS Newfield2,
Mid(Field1, InStrRev(Field1, "&") + 1) AS Newfield3
FROM SomeTable
0

LVL 43

Expert Comment

ID: 24255841
SELECT
LEFT(Field1, CHARINDEX('&',Field1)-1) AS Newfield1,
SUBSTRING(Field1, CHARINDEX('&',Field1)+1,
CHARINDEX('&',Field1,CHARINDEX('&',Field1)+1)-CHARINDEX('&',Field1)-1) AS Newfield2,
RIGHT(RTRIM(Field1), CHARINDEX('&',REVERSE(RTRIM(Field1)))-1) AS Newfield3
FROM YourTable

Of course, the NewField2 is of char type, to convert it to date you have to use CONVERT function.
0

LVL 43

Expert Comment

ID: 24255856
Sorry, I forgot to tell: My solution works in MS SQL Server :-)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
###### Suggested Courses
Course of the Month10 days, 15 hours left to enroll