TRIM

[Guest]

I need to find a way to find a "-" and display only the information to the
left. So I used the following "CODE:
Left([BIN_PRIM],InStr(1,[BIN_PRIM],"-",0)-1)". It works except for when the
field does not contain a "-" char. How should I type the string to allow for
the fields that do not have a "-". The return data shows an "error" response
for those non "-" fields.

 

[Dirk Goldgar]

"(e-mail address removed)"

I need to find a way to find a "-" and display only the information
to the left. So I used the following "CODE:
Left([BIN_PRIM],InStr(1,[BIN_PRIM],"-",0)-1)". It works except for
when the field does not contain a "-" char. How should I type the
string to allow for the fields that do not have a "-". The return
data shows an "error" response for those non "-" fields.

If you're doing this in a query, you should be able to define this
calculated field:

CODE: IIf(InStr(1,[BIN_PRIM],"-",0) > 0, Left([BIN_PRIM],
InStr(1,[BIN_PRIM],"-",0)-1), [BIN_PRIM])

That won't work in VBA, because the VBA version of the IIf(0 function
evaluates both alternative return values. However, the Jet version as
evaluated in a query does not.

 

[Van T. Dinh]

Try:

IIF( InStr( 1, [BIN_PRIM],"-" )> 0,
Left([BIN_PRIM],InStr(1,[BIN_PRIM],"-")-1),
[BIN_PRIM]
)



(type as one line)


--
HTH
Van T. Dinh
MVP (Access)



"(e-mail address removed)"