Solved

How to pick the code from code and description field?

Posted on 2013-07-01
19
452 Views
Last Modified: 2013-07-24
I have one access table with

I have on description field for products whose contents look like this:

91255008 - MOTOR DE CORRENTE CONTÍNUA COM PASSO DE 0,065 GRAUS E POTÊNCIA

I need to pick the code "91255008" and insert in a CODE field that I already created in my access table. I want to handle code as text field.

The code length is not fixed but there is always a space and the "-" after it.

Please no programming on answer. I´m only familiar with query (you can post here the SQL version of the query) or maybe the picture of design mode of the query.

Thanks!

Julia
0
Comment
Question by:JULIASPHESS
  • 6
  • 5
  • 4
  • +1
19 Comments
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
The Val function can pull out the number at the beginning of a field so your update query should look something like this:
Update TableName Set [CODE] = VAL([DescriptionField])

Open in new window

0
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
Or, Left(codeField, instr(codefield(" ",)-1)
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
nb: You haven't indicated what type of field Code is.
if any of those codes commence with a zero, you may have to treat it as a string rather than a numeric.

e.g. "006789 - whatever", would become 6789 as a number

>>there is always a space and the "-" after it.
a small adjustment to the suggestion immediately above would be to look for both that space and the minus:

Left(sourceField, instr(sourcefield(" -",)-1)

the value provided by Left will be a string, not a number, so leading zeros will survive.

Update TableName Set CODE = Left(sourceField, instr(sourcefield(" -",)-1)

see:
InStr http://msdn.microsoft.com/en-us/library/office/gg264811.aspx
Left http://msdn.microsoft.com/en-us/library/office/gg251556.aspx
0
 

Author Comment

by:JULIASPHESS
Comment Utility
PortletPaul and others,

I do understand the solution you proposed. I tried to use it but I keep receiving a syntax error message. So I tried to reduce the syntax only to see if it could pick the first 5 characters of my string. The field I want to search is a memo field, but I also get the same error message when I use the left function in a text field.

Please see the message in the picture attached. The translation to english is:

"You ommited one operator, inserted a comma or an invalid character or inserted text without quotes to delimit it. "

Code:left([1-Relatório DI - Continental]![Descrição] ,5) I tried this simple one but get the same message

I guess that would be my complete command:

Code:left([1-Relatório DI - Continental]![Descrição],instr([1-Relatório DI - Continental]![Descrição] (" -")-1)
imagem.JPG
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
being a memo field is the likely to be the cause of your issue, will investigate - others may have the answer in their head (I don't)
0
 

Author Comment

by:JULIASPHESS
Comment Utility
I also thought memo field could be the problem, but I get the same message with text field as sourcefield.
0
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
Sometimes, Access does funny things when '-' is in the field name. Try changing the '-' to '_' and see if you get better results.
0
 

Author Comment

by:JULIASPHESS
Comment Utility
jerryb30,

I don´t get it, if I change the "-" for the "_" in the search, access won´t find the end of the CODE within description field. Besides I get the error message even with this command

left(sourcefield,5)

Please refer to my message above,

thanks,

Julia
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Accepted Solution

by:
jerryb30 earned 350 total points
Comment Utility
I mis-read. I thought you had a hyphen in the field name, not the table name.
Left([1-Relatório DI - Continental]![Descrição],InStr([1-Relatório DI - Continental]![Descrição]," -")-1)
works for me, as far as I can re-create it.
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Looks like you're missing a comma with your syntax where pointed out below:
Code:left([1-Relatório DI - Continental]![Descrição],instr([1-Relatório DI - Continental]![Descrição] (" -")-1)                             
                                                                                                     ^
                                                                                                     Need comma here

Open in new window

0
 

Author Comment

by:JULIASPHESS
Comment Utility
I´ve tried with the comma indicated in the post above but it didn´t work.


Julia
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Sorry, I also forgot to point out the extra open parenthesis you had right where you put the comma.  It should end up looking like jerryb30's last response.
0
 

Author Comment

by:JULIASPHESS
Comment Utility
I've requested that this question be deleted for the following reason:

I did not get the answer.
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 150 total points
Comment Utility
You never responded about whether removing the extra open parenthesis did not work.  It would have looked liked this:

Code:left([1-Relatório DI - Continental]![Descrição],instr([1-Relatório DI - Continental]![Descrição], " -")-1)

Open in new window

0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
The OP never got back to us on whether the last suggestion worked.
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
I believe jerryb30 gave the right answer on http:#a39294314 and should get the majority, if not all the points.  My answers http:#a39295115 and http:#a39295141 just pointed out the syntax problems (namely, the missing comma and the extra parenthesis).
0
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
Very gracious. I would prefer more input from author.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

763 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

8 Experts available now in Live!

Get 1:1 Help Now