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
> New Field That Ignores Specific Characters, Access 2016    
 
   
davmec93
post Apr 12 2017, 02:08 PM
Post#1



Posts: 488
Joined: 9-May 06
From: Missouri


Here is a question for you all. Thanks in advance for taking the time to try and help me...

I have a field that may look like this
78" OD X 1.00" THK X 41'-9 5/8"

I want to create a new field that will ignore the
"
OD
THK
'
Characters and just return
78 X 1.00 X 41 9 5/8

But (always a but) the filed may or may not contain those characters at all or they may be in different spots in the field.

Can anyone help me achieve this? I don't want to actually update the field and replace those because the actual table isn't mine.
Go to the top of the page
 
theDBguy
post Apr 12 2017, 02:10 PM
Post#2


Access Wiki and Forums Moderator
Posts: 74,178
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Take a look at the Replace() function.

Hope it helps...
Go to the top of the page
 
John Vinson
post Apr 12 2017, 03:46 PM
Post#3


UtterAccess VIP
Posts: 4,277
Joined: 6-January 07
From: Parma, Idaho, US


This is a good example of why fields should be "Atomic" - holding only one piece of information. This field has SEVEN - three values (numbers), two dimensions, and two redundant Xs. If this is how you get the data from an outside source you're stuck having to parse it apart, but if you have control of how the data is entered, I'd strongly suggest storing each dimension in its own field. It's always easier to take separate values and concatenate them for display (with or without dimensions, with or without X, etc. as you choose) that in is to tease a complex string apart.

What's the real-life object being measured? Do you have just OD, THK and the third (length?) dimension, or do some come in other shapes?
Go to the top of the page
 
mklein
post Apr 12 2017, 04:06 PM
Post#4



Posts: 264
Joined: 7-August 12
From: BC, Canada


There are some fairly simple ways to deal with data that contains string delimiters like that. To me you lose some clarity if you edit this <78" OD X 1.00" THK X 41'-9 5/8"> down to this <78 OD X 1.00 THK X 41-9 5/8>.

Consider code that uses a temp querydef to do an insert, like...
CODE
const SQL_INSERT as string = _
   "INSERT INTO tTestTable ( PartDescription ) " & _
   "VALUES ( p0 );"

with currentdb.createquerydef("", SQL_INSERT)
   .parameters(0) = me.PartDescription
   .execute dbFailOnError
   .close
end with
In this case, if the user had entered this string <78" OD X 1.00" THK X 41'-9 5/8"> in the textbox me.PartDescription, the insert would succeed because the querydef handles the embedded delimiters automatically.

You can use the same approach to use string delimiters in the data of a where clause, like...
CODE
const SQL_SELECT as string = _
   "SELECT * FROM tTestTable " & _
   "WHERE PartDescription = p0"
dim rst as dao.recordset

with currentdb.createquerydef("", SQL_SELECT)
   .parameters(0) = me.txtPartDescriptionSearch  'maybe user has entered <10 x 8' 2"x4"> into the control
   set rst = .openrecordset
   .close
end with

So, while you can retroactively strip string delimiters out of your data, there are also some reasonably simple ways to use querydefs to completely solve the problem of embedded string delimiters.
hth
Mark
Go to the top of the page
 
davmec93
post Apr 13 2017, 11:16 AM
Post#5



Posts: 488
Joined: 9-May 06
From: Missouri


Thank you. You just taught me how to fish! I read up on the "replace" function and realized that I can use it and it won't actually change the current data....thats what I was afraid of. It work very well.
Go to the top of the page
 
davmec93
post Apr 13 2017, 11:30 AM
Post#6



Posts: 488
Joined: 9-May 06
From: Missouri


I have no control on how the data is getting entered so I'm stuck with breaking it into pcs. The ultimate goal I have is to have it like you stated...OD, thickness and length in three fields. Which now I have done using the replace function.
Go to the top of the page
 
theDBguy
post Apr 13 2017, 12:34 PM
Post#7


Access Wiki and Forums Moderator
Posts: 74,178
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Congratulations! Glad to hear you got it sorted out. Good luck with your project.
Go to the top of the page
 
NimishParikh
post Apr 13 2017, 02:08 PM
Post#8



Posts: 214
Joined: 30-November 10



Just curious. By any chance, are you in a piping engineering field?

Nimish
Go to the top of the page
 
davmec93
post Apr 13 2017, 03:16 PM
Post#9



Posts: 488
Joined: 9-May 06
From: Missouri


We are a spiral pipe manufacturing and fabrication company.
Go to the top of the page
 
davmec93
post Apr 13 2017, 03:20 PM
Post#10



Posts: 488
Joined: 9-May 06
From: Missouri


Let me ask this, and please let me know if I should start a new topic.
Using the replace function from the recommendations above, I know have the string the way I want it. The one thing that is driving me nuts now is that I need to return the string after the last "X" in the field.
If my field is
97.5 X .750 X 90
My result would be
90

using the InStr function I'm able to determine where that "X" is (in this case it's 13) but I can't figure how to start at 13 and return everything after that.....
Go to the top of the page
 
davmec93
post Apr 13 2017, 03:23 PM
Post#11



Posts: 488
Joined: 9-May 06
From: Missouri


Nevermind on that....I should have been using the Mid function. Looks like I got it now.
Go to the top of the page
 
jwhite
post Apr 13 2017, 03:55 PM
Post#12


UtterAccess VIP
Posts: 5,818
Joined: 31-August 06
From: North Carolina, USA


thumbup.gif Or you could use the InstrRev() function, especially if the last X could 'float' within the string.
Go to the top of the page
 
mklein
post Apr 13 2017, 04:39 PM
Post#13



Posts: 264
Joined: 7-August 12
From: BC, Canada


If the string always has three members delimited by " X " then I would use Split(), which returns a zero-based array, and then provide the subscript for the third member...

CODE
? Split("97.5 X .750 X 90", " X ")(2)
90
hth
Go to the top of the page
 
rony albert
post Apr 14 2017, 07:14 AM
Post#14



Posts: 2
Joined: 1-April 17



I did not get it completely...! iconfused.gif
subchorionichemorrhagehealth care tips
Go to the top of the page
 
John Vinson
post Apr 16 2017, 03:51 PM
Post#15


UtterAccess VIP
Posts: 4,277
Joined: 6-January 07
From: Parma, Idaho, US


What did you try? What result did you get - and expect? What did you not understand? We'll help if we can!
Go to the top of the page
 
davmec93
post Apr 21 2017, 02:05 PM
Post#16



Posts: 488
Joined: 9-May 06
From: Missouri


Just to update everyone that has been so helpful on this, I ended up using the Replace function first. I replace all the "-" and " " " characters with nothing (""). After I got my string cleaned up it left me with my measurements and the "X". Using the Left, Mid and InsSr functions, I pulled out the text before the first "X" which was my OD size, the text between the tow "X's" which was my Wall Thickness and finally the text after the last "X" which was my length. I put them in 3 separate fields and Shazam! I now have OD, Wall Thickness and Length fields. This is how the original designer should have created this (in my opinion) but he didn't so I was lucky to have you all to help me!
Go to the top of the page
 
John Vinson
post Apr 21 2017, 02:26 PM
Post#17


UtterAccess VIP
Posts: 4,277
Joined: 6-January 07
From: Parma, Idaho, US


uarulez2.gif woohoo.gif
Glad you got it working! Well done!
Go to the top of the page
 
jorgepp
post Dec 20 2018, 06:29 AM
Post#18



Posts: 2
Joined: 20-December 18



Great! Thanks for sharing us!
Go to the top of the page
 
jorgepp
post Dec 21 2018, 06:39 AM
Post#19



Posts: 2
Joined: 20-December 18



Well done!

videos xxx
pornofete.com
sexshop online
chat sexo
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th January 2019 - 10:17 PM