Access DB Table - Split Field SQL Command

Question

I have an Access 2013 Database Table, dbo_GOV

THE GOAL

I want to take the USSenators field, which contains data like (below) and split it into USSenator1 and USSenator2 fields, respectively:

John Smith;Sarah Levens
Bill Burr;Kevin Nill
George Thomson;Tracy Johnson

THE PROBLEM

I've tried a few different Access SQL queries... both (below) when executed, give the error message

Invalid use of '.', '!', or '()'. in query expression 'Split(USSenators & ";", ';')(0'.

I have verified that there are 0 records, where USSenators is blank. Every row has 2 people listed, separated by a semicolon.

SQL QUERIES

UPDATE dbo_GOV
SET
    USSenator1 = Split(USSenators & ";",';')(0),
    USSenator2 = Split(USSenators & ";",';')(1);

UPDATE dbo_GOV
SET
   USSenator1 = Split(USSenators,';')(0),
   USSenator2 = Split(USSenators,';')(1);

I've tried referencing the Office documentation for Split: here

score 1 · Accepted Answer · answered Mar 11 '14 at 13:59

1

You cannot use Split in a query, use Mid and Instr.

Mid(USSenators,Instr(USSenators,";")+1)
Mid(USSenators,1,Instr(USSenators,";")-1)

Line 2 above returns John Smith for the first record
Line 1 above returns Sarah Levens for the first record

You will need to watch for nulls.

answered Mar 11 '14 at 13:59

Fionnuala

Seems that Split should be a valid command...? http://office.microsoft.com/en-us/access-help/split-function-HA001228911.aspx – adam Mar 11 '14 at 14:06
1

There is a big difference between ACE / Jet (the database, with SQL) and Access (the front end, with VBA) – Fionnuala Mar 11 '14 at 14:08

1 Answers1