How to get rid of characters in a string

S

SarahP

I have numbers like those below in a column of data I am using in a query. I
need a formula to change it.
1013-14
123-1
1233-5
569
I need to get rid of everything after the - inclusive. So...
1013-14 becomes 1013
123-1 becomes 123
1233-5 becomes 1233
569 stays as 569

Can someone help me do that?
 
P

PC Datasheet

Look up the InStr function in the Help file. You need to use that function
in an update query in two ways:
1. Return only the numbers that contain a "-"
2. Update those numbers to just the number to the left of the "-"
 
M

Marshall Barton

SarahP said:
I have numbers like those below in a column of data I am using in a query. I
need a formula to change it.
1013-14
123-1
1233-5
569
I need to get rid of everything after the - inclusive. So...
1013-14 becomes 1013
123-1 becomes 123
1233-5 becomes 1233
569 stays as 569

Can someone help me do that?
Click to expand...


Use a calculated field in the query:

Expr1: IIf(InStr(thefield, "-") > 0, Left(thefield,
InStr(thefield, "-") - 1, thefield)
 
T

Tom Lake

SarahP said:
I have numbers like those below in a column of data I am using in a query.
I
need a formula to change it.
1013-14
123-1
1233-5
569
I need to get rid of everything after the - inclusive. So...
1013-14 becomes 1013
123-1 becomes 123
1233-5 becomes 1233
569 stays as 569

Can someone help me do that?
Click to expand...

Use Val([fieldname])

Tom Lake
 
Top