Database Access Table - SQL Split Field Command

Question

I have an Access 2013 database table, dbo_GOV

TARGET

I want to take the USSenators field, which contains data such as (see below) and divide it into USSenator1 and USSenator2 fields, respectively:

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

---

PROBLEM

I tried several different SQL query queries ... as (below) when executed, it gives an error message

Incorrect use of '.', '!' or '()'. in the query expression "Split (USSenators and"; ", ';') (0 '.

I checked that there are 0 entries where USSenators are empty. Each line contains 2 people, 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 tried to refer to the Office documentation for Split: here

Answer 1

Split , Mid Instr.

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

2
1

.