Help with Converting an Excel Formual to a MS Formula

[stevedemo]

I need to find and replace text in a query. I am stumped.

The table contains all text fields I need to remove the "cr" that appears at the end. And then add a "1" at the begining of the text field

this is how I do it in excel

=IF(ISERROR(FIND("cr",G2,1)),IF(H2<>"",G2,""),(SUBSTITUTE(G2,"cr","")*-1))


I converted it over to MsAccess

IIf((InStr(1,[field8],"cr",0))>0,("-"+Replace([field8],"cr","")),[Field8])

All I get is [field8] with the "cr" removed and not the "1" added to the text.

What am I doing wrong ?


snippet of Field8
16.09
12.33
10.93cr
12.76
12.42

All I want is the chance to prove money won't make me happy.

 

[jrbarnett]

Iif(Right$([Field8],2)="cr",1 & Replace(Field8,"cr",""),[Field8])

should do it.

 

[stevedemo]

As posted that is pulling an error, I suspect it is most likely how I am trying to use the code.

I thought I could modify the fields at this point but I am beginning to doubt this, is the Query only capable of performing a TRUE / FALSE at this point ?

Here is what I am trying to do:

I am trying to filter a file I am importing, but I am not having much success at the last portion of my filter.

I have uploaded a snippet to my server, if this will help.

http://www.wopto.com/msaccess/report-copy.zip

Thanks !!!

All I want is the chance to prove money won't make me happy.

 

[ProgramError]

The table you have is not correct to start with. You have headers as data in some of the fields. Are you importing this data from somewhere?, if so, from what? excel, a CSV text file?

It also looks as though you may need two queries as you seem to be updating the fields on the fly using replace. Is this the case?

Lastly, your statement seems strange.
IIf(Right([Field8],2)="cr",1 & replace([Field8],"cr",""),[Field8])

assuming field 8 = "hello"
breaking this down 1 & replace([Field8],"cr","") will give you...
field 8 will be replaced with 1cr if field has something in it, otherwise it will be replaced with 1.
Right([Field8],2)="cr" will result in false because...
"lo" does not equal "cr"
and the final outcome of the iif statement is...

Right([Field8],2)="cr" is false so execute the
1 & replace([Field8],"cr","") part resulting in
field8 = "1cr"

Is this what you were expecting?




Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[stevedemo]

I am importing a CSV file.

What I want to do is filter out the junk to make the file useable. The portion I am not sure about is how to remove the "cr" leaving the rest of the text then add a "-" to the begining of the field only if it had a "cr" in the text.

I was making an attempt to convert my excel formula to MS access.

=IF(ISERROR(FIND("cr",G2,1)),IF(H2<>"",G2,""),(SUBSTITUTE(G2,"cr","")*-1))

This is what I was using and it didn't give the expected reslult:

IIf((InStr(1,[field8],"cr",0))>0,("-"+Replace([field8],"cr","")),[Field8])

All I want is the chance to prove money won't make me happy.

 

[PHV]

What about this ?
IIf(InStr([field8],"cr")>0,-Val(Replace([field8],"cr","")),[Field8])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886