trim function

[pablavo]

Hi there

I saw my question in an online tutorial once but can't find it.

I have about a thousand records and I'm just concerned about one field.
I have to strip away part of the text.

Heres an example:

AFG-006/2/E
AFG-006/E
AFG-008/1
AFG-008/1/E

I have to strip away all of the left part from the ascii character (/)

so all the records would look like this:

AFG-006
AFG-006
AFG-008
AFG-008

I'm sure there's a way to do this with Trim, maybe Ltrim but I'm not sure how this would be done. Does anyone have any ideas. I have over 1000 records which means it wouldn't be practicle to do it manually.

Thanks

 

[Rabbie]

Use the Left Function. see access help for more info

 

[neileg]

Are there always 7 characters before the slash? If there are, then as Rabbie says, just use Left().

 

[pablavo]

Thanks Rabbie, I didn't notice the Left function until now. Do you know if I can use it in a query? I can't find any info on it.

thanks

 

[pablavo]

hey, it's ok, I worked it out

SELECT Left([codes],7) AS codes
FROM Project

Only problem is, some of the codes have spaces oft,

CMB - 001
DRC-001

ah well, thanks for the help folks.

 

[RuralGuy]

If it is important, you could create a function in a standard module that would strip out the spaces from your field before you took the Left() 7 characters.

 

[pablavo]

Thanks Rural guy. I'm not trying to take a short cut here but, could you point me in the right direction of how I would do that?

Thanks

 

[RuralGuy]

Try this function in a standard module:

Function NoSpace(InString As String) As String
'-- Return InString with any spaces removed
Dim i As Integer
If Len(InString) > 0 Then
   For i = 1 To Len(InString)
      If Mid(InString, i, 1) <> " " Then
         NoSpace = NoSpace & Mid(InString, i, 1)
      End If
   Next i
End If
End Function

...use like:
SELECT Left(NoSpace([codes]),7) AS codes

 

[pablavo]

Thanks for your help everyone.

 

[RUDOLFSUN]

there is an easier way

left([fieldname],instr(1,[fieldname],"/")-1)

Put that into the update row, or create it as a new expression just to check first