Equivalent Formula For Trimming Data

[molsen]

Hi All

I am using the following formula to strip out certain characters from a field using MS Excel, containing similar to the following data:

Miss M A Budgett W/T *094169/2

In the next field to the right, I want everying after the * to appear:

=TRIM(REPLACE(D15,1,FIND("*",D15)," "))

Does anyone know what the equivalent code would be so that I can replicate this action in an Access 2010 query?

Many thanks

 

[plog]

Here's a list of common Access functions: http://www.techonthenet.com/access/functions/

Trim is still good
For Replace you would use Mid(http://www.techonthenet.com/access/functions/string/mid.php)
For Find you would use InStr (http://www.techonthenet.com/access/functions/string/instr.php)

Give it a shot, check out the link I provided and post back here any issues you have.

 

[molsen]

Here's a list of common Access functions: http://www.techonthenet.com/access/functions/

Trim is still good
For Replace you would use Mid(http://www.techonthenet.com/access/functions/string/mid.php)
For Find you would use InStr (http://www.techonthenet.com/access/functions/string/instr.php)

Give it a shot, check out the link I provided and post back here any issues you have.

Thanks Plog, appreciate your help. I am struggling with the syntax. Would you be kind enough to give me the equivalent code to find everything after the * in the text below? It would be better if I could actually cut that reference from the original field and place it in a new one in the query:

Miss M A Budgett W/T *094169/2

I've had a go at it, but keep getting an error message!

 

[Mile-O]

=Mid([YourText], InStr(1, [YourText], "*")+1, Len([YourText]))

 

[vbaInet]

If it's everything after the "*" then I suppose the Len() part can be dropped:

=Mid([YourText], InStr(1, [YourText], "*")+1)

 

[Mile-O]

If it's everything after the "*" then I suppose the Len() part can be dropped:

Oh, yes. That's my habit of using Excel a lot, where the parameter is not optional.

 

[molsen]

If it's everything after the "*" then I suppose the Len() part can be dropped:

=Mid([YourText], InStr(1, [YourText], "*")+1)

Thank you! Works a treat...

And if I only want it to show the characters after the *, and to ignore any characters preceeding it?

 

[vbaInet]

And if I only want it to show the characters after the *, and to ignore any characters preceeding it?

Isn't that what that what the code is doing?

 

[vbaInet]

Oh, yes. That's my habit of using Excel a lot, where the parameter is not optional.

Yes!

 

[molsen]

Yes!

Sorry vba, I'm being a numpty plonker. What I really meant was that if there are any fields which dont contain a * at all, then it shouldnt copy anything across .

There are a lot of fields where just the name is displayed, I am only looking to copy anything with a reference after the *, otherwise it should be left blank.

 

[JackKaptijn]

Another option is the split function.

Split([Yourtext],"*")(0) gives all before the *
Split([Yourtext],"*")(1) gives all behind the *

 

[Mile-O]

Sorry vba, I'm being a numpty plonker. What I really meant was that if there are any fields which dont contain a * at all, then it shouldnt copy anything across .

There are a lot of fields where just the name is displayed, I am only looking to copy anything with a reference after the *, otherwise it should be left blank.

Something like:

IIf(Instr(1, [YourText], "*"), Mid([YourText], InStr(1, [YourText], "*")+1),"")

 

[vbaInet]

Mile-O is on it.

I think it would be better for the empty string "" to be Null.

 

[molsen]

My deep and unconditional respect to you all for your help!

 

[molsen]

Another option is the split function.

Split([Yourtext],"*")(0) gives all before the *
Split([Yourtext],"*")(1) gives all behind the *

Hi All

When I try to insert this into a field in a query whilst in build,

Payee Name: Split([DE_ppayee],"*")(1)

I get a message telling me "The expression you entered has an invalid
.(dot) or ! operator or invalid parentheses.

Can anyone help?

Tks

 

[Mile-O]

Would have thought Split() worked purely in VBA. Certainly if I try to replicate it I get the same error as yourself. However, a search of the form highlights othe threads where use of the Split() function in this way has been offered as a solution. Maybe it worked in prior versions.

Anyway, create a module and put this code in:

Public Function GetPart(ByRef SplitText As String, ByRef Delimiter As String, _
    ByRef SplitPart As Long) As String
    On Error Resume Next
    Dim strText() As String
    strText() = Split(SplitText, Delimiter)
    GetPart = strText(SplitPart)
End Function

In your query:

Payee Name: GetPart([DE_ppayee],"*",1)

 

[vbaInet]

Eval() and anything that returns an array doesn't work in queries.

I'm still in favour of Mile-O's original solution.

Something like:

IIf(Instr(1, [YourText], "*"), Mid([YourText], InStr(1, [YourText], "*")+1),[COLOR="Blue"]Null[/COLOR])