How to split a field into two columns in a Query

I want to be able to via a query parse a single column  Claim No (123456789-001) into two separate fields in the resulting worksheet.

So the first 9 characters and then the number to the right of the "-"
seamus9909Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ferruccio AccalaiConnect With a Mentor Senior developer, analyst and customer assistance Commented:
In Query Design, write into the Field Option
LeftField: Left([Claim No],9)

Open in new window

This is the first field that will be selected with Alias LeftField (of course you can use your preferred name)
Then the other field will be
RightField: Right([Claim No],3)

Open in new window

Example
0
 
PatHartmanCommented:
You need to use the left() and right() functions

Select Left([Claim No], 9) as ClaimNo, Right([Claim No], 3) as ClaimSuffix From YourTable
0
 
seamus9909Author Commented:
Its telling syntax is not correct.  I am using Query Design and not the SQL itself.

am I missing the quote or bracket?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rey Obrero (Capricorn1)Commented:
<I am using Query Design and not the SQL itself.>

post an image of your query
0
 
Pawan KumarDatabase ExpertCommented:
Pls try.. Also check the name of the column - [Claim No].

1.
left(x,instr([Claim No],"-")-1)
trim(mid(([Claim No],instr(([Claim No],"-")+1))

2.
trim(split(([Claim No],"-")(0))
trim(split(([Claim No],"-")(1))
0
 
seamus9909Author Commented:
Thank you so much!
0
All Courses

From novice to tech pro — start learning today.