Replace excel formula with equivalents in access?

[papasmurfuo9]

=REPLACE(SUBSTITUTE(M2," x ",""),1,FIND(" ",SUBSTITUTE(M2," x ","")),"").

to be

=REPLACE(SUBSTITUTE([MYFIELD]," x ",""),1,FIND(" ",SUBSTITUTE([MYFIELD]," x ","")),"")

hi could someone assist with the above?
thanks in advance

 

[plog]

Sure, but you are going to have to explain with words what you are trying to accomplish. Possibly give me an example, e.g. If MYFIELD="Plog Rules" it should return "Plog Is Awesome"

 

[papasmurfuo9]

Hi

the field contains - 8 examples

1 x 1 x 24 x 250 g ea
1 x 1 x 1 x 2 Kg ea
1 x 1 x 5 x 100 pk
1 x 1 x 2 x 2 ltr
1 x 1 x 2 x 2 ltr
1 x 1 x 12 x 80 g pk
1 x 1 x 1 x 500
1 x 1 x 3 x 5 Ltr con

and on the above, im trying to bring back

g ea
Kg ea
pk
ltr
ltr
g pk

Ltr con


thank you

 

[plog]

Does anyone else have an efficient solution? Mine is going to involve writing a custom function in VBA.

I don't know of a method to use just built-in functions to find the last numeric character in a string, so I would write a function that does that. You would pass the function your string, it would loop through every character to find the position of the last number, once found it would extract the substring from that position to the end of the string to give you your desired results.

The functions you would for this are:

IsNumeric (http://www.techonthenet.com/access/functions/advanced/isnumeric.php) to see if a character is numeric

Mid (http://www.techonthenet.com/access/functions/string/mid.php) to extract a substring from your main string.

 

[papasmurfuo9]

not sure how to combine these for desired result, but will give it a go, thanks

 

[plog]

You create a new module and write VBA code there like so:

Public Function get_Units(in_String) As String
    ' takes a string (in_String) and extracts units at the end of it

ret=in_String    ' return value, default is entire string it was passed


' logic here to extract units


get_Units = ret

End Function

Then in a query you would use it like so:

Units: get_Units([MYFIELD])

 

[papasmurfuo9]

cheers, will try figure the logic
ta!