Solved

acccess query split text to ...

Posted on 2014-09-29
11
468 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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article will show you how to use shortcut menus in the Access run-time environment.
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…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

743 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

12 Experts available now in Live!

Get 1:1 Help Now