If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using a combination of IIF, INSTR and LEFT
I have a table of about 25 fields. Field19 is a column of Last Names, but
some of the records have extra text in it. This extra text begins with a "/". I need to do an update or make table query that would clean up the Last Names by removing everything after the /. This is what I tried to come up with myself: Expr1: IIf(InStr([Field19],"/")1,Left([Field19],InStr([Field19],"/")-1),([Field19])) but it give me an "Invalid Argument" error. Please help. |
#2
|
|||
|
|||
Using a combination of IIF, INSTR and LEFT
You are on the right track.
Use this as criteria for an update query -- Like "*/*" Use this as the update --- Left([Field19],InStr([Field19],"/")-1) "MB" wrote: I have a table of about 25 fields. Field19 is a column of Last Names, but some of the records have extra text in it. This extra text begins with a "/". I need to do an update or make table query that would clean up the Last Names by removing everything after the /. This is what I tried to come up with myself: Expr1: IIf(InStr([Field19],"/")1,Left([Field19],InStr([Field19],"/")-1),([Field19])) but it give me an "Invalid Argument" error. Please help. |
#3
|
|||
|
|||
Using a combination of IIF, INSTR and LEFT
Karl, this worked like a charm. You guys are truyly the BEST!!!
"KARL DEWEY" wrote: You are on the right track. Use this as criteria for an update query -- Like "*/*" Use this as the update --- Left([Field19],InStr([Field19],"/")-1) "MB" wrote: I have a table of about 25 fields. Field19 is a column of Last Names, but some of the records have extra text in it. This extra text begins with a "/". I need to do an update or make table query that would clean up the Last Names by removing everything after the /. This is what I tried to come up with myself: Expr1: IIf(InStr([Field19],"/")1,Left([Field19],InStr([Field19],"/")-1),([Field19])) but it give me an "Invalid Argument" error. Please help. |
Thread Tools | |
Display Modes | |
|
|