Hi,

Need some help for the issue i am facing , I have a table with one of the column containing some special characters like 445$ggy,klmnft#ftt,pprtouv-op .I want to remove these special characters and need only 445,klmnft,pprtouv(want to remove $ggy,#ftt,-op) .I was using excel option "Text to Column" to remove these characters, since the data has become huge i am using access and want to perform the same condition here, but not sure how to design queries for these things , any help on this would be appreciated

Thanks and Regards,
Mysteryboy

Hi,

Need some help for the issue i am facing , I have a table with one of the column containing some special characters like 445$ggy,klmnft#ftt,pprtouv-op .I want to remove these special characters and need only 445,klmnft,pprtouv(want to remove $ggy,#ftt,-op) .I was using excel option "Text to Column" to remove these characters, since the data has become huge i am using access and want to perform the same condition here, but not sure how to design queries for these things , any help on this would be appreciated

Thanks and Regards,
Mysteryboy

As long as you will not be wanting to keep any column values containing "$ggy", "#ftt", or "-op", then one possible solution would be to use a conditional IIf to evaluate the column.

IIf(InStr(1, TheColumn, "$ggy"), Left(TheColumn, InStr(1, TheColumn, "$ggy")-1), TheColumn)
IIf(InStr(1, TheColumn, "#ftt"), Left(TheColumn, InStr(1, TheColumn, "#ftt")-1), TheColumn)
IIf(InStr(1, TheColumn, "-op"), Left(TheColumn, InStr(1, TheColumn, "$-op")-1), TheColumn)

Or perhaps the Replace function???

Ya find "#" and replace with " "

thanks for the quick reply, the data that i have has got more than 100K rows in it and they primarily contain these three special characters #,$ and - followed by other normal characters not fixed one ( means they may have 3 characters 4 or 5 characters after the special characters ) . i have to remove these special characters along with whatever other characters they carry in front ( ex sdfg#pol need to remove entire #pol and want only sdfg) ... replace function is a good option but that would only remove the special characters, pls correct me if iam wrong and is their another way of doing it

Regards,
Mysteryboy

Assuming the format if not the size of your data is constant how about writing a function containing a series of Replace statements to replace the specisl characters with , then use the Split function to create a 6 element array, then concatenate elements 1 3 5 to give you what you want.

Note that might be 0 2 4

Just a thought don't know if it will work.

Brian

This rough code

Public Function con(oldfield As String) As String

nfld1 = Replace(oldfield, "#", ",")
nfld2 = Replace(nfld1, "-", ",")
nfld3 = Replace(nfld2, "$", ",")

myarray = Split(nfld3, ",")
con = myarray(0) & myarray(2) & myarray(4)


End Function



converted your example into


445klmnftpprtouv


Hope this helps

Brian

OK not quite correct you want commas :D so
con = myarray(0) & "," & myarray(2) & "," & myarray(4)

should do the trick

Or perhaps the Replace function???

DOH!



Why do I feel like Homer Simpson right about now?

Hi All,

Thanks for replying with diff ways of doing things,
i would not be able to use any vb based macros in this , i need access quereies or sql based ones apologies for not informing this earlier itslef
looking forward for some inputs from everyone

Regards,
Mysteryboy

Its not a macro its a function which is placed in a module and used like any other Access function in a query or SQL.

Editedfield: con(originalfield)

or

con(originalfield) as editedfield


Brian