Populate one field based on data from another... 1

[jojomore]

In Access 2000, I am trying to populate one field based on the input from another. The ORIGINAL field would be in one of the following formats:
64532-3
84-1234
45123
For the REVISED field, I need to strip the "-" out in the case of 64532-3 only, so resulting data looks like:
645323
84-1234
45123

I am trying to use an IIF based on an InStr function, but just get errors.
Iif (InStr(1,[ORIGINAL], "-")<>6,[ORIGINAL], LEFT([ORIGINAL], InStr(1,[ORIGINAL], "-")-1) & RIGHT ([ORIGINAL], InStr(1,[ORIGINAL], "-")+1)

What am I missing? I have tried putting the IIF statement in the expression box of SetValue macro, in the control source of the REVISED field and am not getting anywhere fast. Can anyone help?
Thank you in advance for your time.

 

[TheAceMan1]

How are ya jojomore . . . .

First you IIF statement is just fine, as long the string length of data with 'dashes' is static as shown.

Your going the long way around to set the value with a macro. Just put the statement in the actual fields Control Source. Just make sure you precede it with an equals "=" sign . . .

TheAceMan

 

[jojomore]

I made some minor modifications to my statement, and it works fine in a query, but does not work on my form:

=IIf(InStr(1,[Grote_INTCHG].[GROTE INTCHG],"-")<>6,[Grote_INTCHG].[GROTE INTCHG],Left([Grote_INTCHG].[GROTE INTCHG],InStr(1,[Grote_INTCHG].[GROTE INTCHG],"-")-1) & Right([Grote_INTCHG].[GROTE INTCHG],InStr([Grote_INTCHG].[GROTE INTCHG],"-")-5))

I have the above statement in the control source for the field that I want to use.

Any ideas?

Thanks for any help!

 

[PHV]

Have you tried something like this ?
=IIf(InStr(1,[Control name of GROTE INTCHG],"-")<>6,[Control name of GROTE INTCHG],Left([Control name of GROTE INTCHG],5) & Right([Control name of GROTE INTCHG],1))
Note: in the false part of the IIf function you know that InStr returned 6, so no need to recall it.

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

 

[jojomore]

Thanks PHV, that worked like a charm! Here's a star for ya!