UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Splitting Field Into Three Fields, Access 2016    
 
   
JAchord
post May 22 2019, 10:22 AM
Post#1



Posts: 70
Joined: 11-July 14



I am trying to split a field that contains drawing numbers like YL3-182-003. There may be more or less numbers on each side of the "-" but most have the "-" separating them. Some drawings use a different format but those can be handled differently. I can get the prefix correct without issue. I can usually get the suffix to parse correctly but it likes to drag the "-" and some numbers from the middle over sometimes. So YL3-182-003 would parse out as "YL3","182-003", and "-003". If I have 204-409-D then it gives me "204","409-D", and "09-D"

Here are the expressions:
prefix: Left([drawing-nbr],InStr([drawing-nbr],"-")-1) 'This one works fine
class: (Mid([drawing-nbr],InStr([drawing-nbr],"-")+1)) 'This one counts everything right of the first "-"
Document: Right([drawing-nbr],InStr([drawing-nbr],"-")) 'This one works most of the time but goes past the second "-" sometimes. If i change it to +1 it leaves off some numbers after the "-"

I have tried using trim and len in the class column but it just cuts it down to one number.

Then I need to figure out how to get the criteria straight so that only drawing numbers with the YL3-182-003 format will be chosen. Like "*[!0-9A-Z]*" & "-" & "*[0-9]*" & "-" & "*[0-9]*" gives me to many that do not match this format.
Go to the top of the page
 
theDBguy
post May 22 2019, 10:31 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,582
Joined: 19-June 07
From: SunnySandyEggo


Hi. For your first issue, try using this custom function: GetMember()

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
JAchord
post May 22 2019, 04:47 PM
Post#3



Posts: 70
Joined: 11-July 14



I was going to try that but managed to get it to work by using two queries with the second query looking at the first. I then updated some temp fields in the table. I only needed it long enough to clean up the data to get a better relationship link. Thanks.
Go to the top of the page
 
theDBguy
post May 22 2019, 04:55 PM
Post#4


Access Wiki and Forums Moderator
Posts: 75,582
Joined: 19-June 07
From: SunnySandyEggo


No worries. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th June 2019 - 12:29 PM