Solved

acccess query split text to ...

Posted on 2014-09-29
11
478 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

821 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