Solved

How to split a field into two columns in a Query

Posted on 2016-10-12
6
34 Views
Last Modified: 2016-10-13
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 "-"
0
Comment
Question by:seamus9909
6 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:seamus9909
Comment Utility
Its telling syntax is not correct.  I am using Query Design and not the SQL itself.

am I missing the quote or bracket?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<I am using Query Design and not the SQL itself.>

post an image of your query
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
 
LVL 22

Accepted Solution

by:
Ferruccio Accalai earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:seamus9909
Comment Utility
Thank you so much!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now