Splitting data within a query 1

[platypus2004]

I've been playing with this to no avail.
I have a table field that contains data like the following:

C-8GB;D-2GB;E-59GB
C-8GB;D-9GB;E-17GB;F-69GB
C-8GB;D-2GB;E-59GB
C-8GB;D-11GB

This is one field and remains as such due to the inconsistency of the field and the fact that it gets imported frequently.

What I need is, in my query, to ONLY show the E drive info.
So from the example above, the results would be:
E-59GB
E-17GB
E-59GB
<null or nothing>

I know it is possible to trim off at the ";", I just haven't figured out how within a query.

Thanks

 

[platypus2004]

I guess what I am looking for is a way to replace *E with E and then GB* with just GB

 

[PHV]

In a standard code module add this:
Public Function getEinfo(s) As String
Dim x As String
If InStr(s, "E-") > 0 Then
x = Mid(s, InStr(s, "E-"))
If InStr(x, ";") > 0 Then x = Left(x, InStr(x, ";") - 1)
End If
getEinfo = x
End Function
Now in the query grid:
EdriveInfo: getEinfo([Name of field])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244