Solved

How to pick the code from code and description field?

Posted on 2013-07-01
19
494 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
ID: 39291983
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
ID: 39292022
Or, Left(codeField, instr(codefield(" ",)-1)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39292115
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
Industry Leaders: 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:JULIASPHESS
ID: 39293377
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
ID: 39293465
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
ID: 39293515
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
ID: 39293847
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
ID: 39294307
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
 
LVL 26

Accepted Solution

by:
jerryb30 earned 350 total points
ID: 39294314
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
ID: 39295115
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
ID: 39295124
I´ve tried with the comma indicated in the post above but it didn´t work.


Julia
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39295141
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
ID: 39340999
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
ID: 39335144
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
ID: 39341000
The OP never got back to us on whether the last suggestion worked.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39343709
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
ID: 39343805
Very gracious. I would prefer more input from author.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

680 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