Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

How to pick the code from code and description field?

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
JULIASPHESS
Asked:
JULIASPHESS
  • 6
  • 5
  • 4
  • +1
2 Solutions
 
IrogSintaCommented:
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
 
jerryb30Commented:
Or, Left(codeField, instr(codefield(" ",)-1)
0
 
PortletPaulCommented:
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!

 
JULIASPHESSAuthor Commented:
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
 
PortletPaulCommented:
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
 
JULIASPHESSAuthor Commented:
I also thought memo field could be the problem, but I get the same message with text field as sourcefield.
0
 
jerryb30Commented:
Sometimes, Access does funny things when '-' is in the field name. Try changing the '-' to '_' and see if you get better results.
0
 
JULIASPHESSAuthor Commented:
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
 
jerryb30Commented:
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
 
IrogSintaCommented:
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
 
JULIASPHESSAuthor Commented:
I´ve tried with the comma indicated in the post above but it didn´t work.


Julia
0
 
IrogSintaCommented:
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
 
JULIASPHESSAuthor Commented:
I've requested that this question be deleted for the following reason:

I did not get the answer.
0
 
IrogSintaCommented:
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
 
IrogSintaCommented:
The OP never got back to us on whether the last suggestion worked.
0
 
IrogSintaCommented:
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
 
jerryb30Commented:
Very gracious. I would prefer more input from author.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now