Solved

acccess query split text to ...

Posted on 2014-09-29
11
489 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 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