Solved

How to pick the code from code and description field?

Posted on 2013-07-01
19
464 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
 

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

911 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

22 Experts available now in Live!

Get 1:1 Help Now