Solved

acccess query split text to ...

Posted on 2014-09-29
11
483 Views
1 Endorsement
Last Modified: 2014-10-31
Hello all,

There is column like this in a table :

Item |Type
I1    | 78 lbs

Now I want to do query that splits the above type to two display  columns like this:
Item | size | size denomination
I1  |  78    | lbs
1
Comment
Question by:Rayne
  • 7
  • 3
11 Comments
 

Author Comment

by:Rayne
ID: 40351159
thank you
0
 
LVL 12

Accepted Solution

by:
danishani earned 500 total points
ID: 40351177
Try this in a Query:
size: Left([YourFieldName],InStr(1,[YourFieldName]," ")-1)
size denomination: Right(Trim([YourFieldName]),Len(Trim([YourFieldName]))-InStr(1, _
 [YourFieldName]," "))

Make sure you change [YourFieldName] into the actual column name you want to change, for example [Type] if that's the column name.
0
 

Author Comment

by:Rayne
ID: 40351276
This piece is not working
InStr(1,[YourFieldName]," ")-1)

returning function error
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:Rayne
ID: 40351294
it works :)
thank you
0
 

Author Comment

by:Rayne
ID: 40351335
I am getting #Func! as error for some rows for size. How to check it> do you know...
0
 

Author Comment

by:Rayne
ID: 40351482
ding dong - any solution? do i need to re-open this question? for this
0
 
LVL 12

Expert Comment

by:danishani
ID: 40351501
Hi Rayne,
Please post the value which give you the error.
I guess some of the content does not match the logic to split the fields correctly.

Thanks,
Daniel
0
 

Author Comment

by:Rayne
ID: 40351514
thank danishani, I sorted it. Used iif
Cheers :)
0
 
LVL 12

Expert Comment

by:danishani
ID: 40351524
Perfect glad you got it working! :)
0
 

Expert Comment

by:Love Chopra
ID: 40415201
Its working for me too. Thx
0
 

Author Comment

by:Rayne
ID: 40415405
@love Chopra
this is my fevorite life saver forum. Saved me several nights of coffee several times
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

832 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