IIF and Statement

[TJVFree]

I'm working on a iif and statement that will turn the numbers in my ([OMNI_Status1]) from "9" to a 1, and if it’s not a "9" make it a "0"

I will also need to Trim the ([OMNI_Status1]) field

Below is what I thought would work but its either telling me that I have then wrong number of arguments, or giving me incorrect data or not working at all

Past_6_Full_Months: (IIf([CORRESPONDENT_PURCHASE_DATE] Between DateSerial(Year(Date()),Month(Date())-0,0) And DateSerial(Year(Date()),Month(Date())-6,1),and trim [OMNI_Status1]="9",1,0))

Thanks for taking the time to help

 

[randy700]

CoreyVI,

This is rather confusing. This appears to be the same question you asked in another thread.
When dhookum requested additional information, you replied you had found a solution.
If you're still having problems, why not provide the information dhookum asked for?


Randy

 

[TJVFree]

Randy, yes it is similar but I'm adding another step to the answer I already received.

The step I'm trying to add is "and trim [OMNI_Status1]="9",1,0))"

Looking for advice if I should be adding a and or a iif to add this part to my formula

DECamount: Sum(IIf([CORRESPONDENT_PURCHASE_DATE] Between DateSerial(Year(Date()),Month(Date())-1,0) And DateSerial(Year(Date()),Month(Date())-2,1),[Amount_Actual_Loan]))

Add to formula above “and trim [OMNI_Status1]="9",1,0))”

 

[TJVFree]

After reviewing the question again, please let me know if there is in fact the same question and that I should have stayed in the first form?

Thanks for taking your time to help
TCB

 

[randy700]

Try removing the comma before [red]and trim[/red].

Past_6_Full_Months: (IIf([CORRESPONDENT_PURCHASE_DATE] Between DateSerial(Year(Date()),Month(Date())-0,0) And DateSerial(Year(Date()),Month(Date())-6,1)and trim [OMNI_Status1]="9",1,0))

Randy

 

[TJVFree]

Thanks you Randy but that one didnt work, do you have any other ideas or advice?

 

[dhookom]

Do you actual attempt to use:

trim [OMNI_Status1]="9"

Did you search Help on the TRIM() function?

Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

Yes, a number of different ways, just couldn’t get it to work.

I think I might be able to get it to work doing this

Past_6_Full_Months: (IIf(([CORRESPONDENT_PURCHASE_DATE] Between DateAdd("m",-6,Date()) And Date()) And Trim([OMNI_Status1])="9",1,0))

just started working with it now. Thanks

 

[TJVFree]

I want able to get it to work!!

so now I'm trying to understand this formula. To be exact I'm trying to make this formula work as rolling months

I see that the DateAdd is looking back 6 months and the and Date()) is looking at today’s date. I need to be able to change this formula so I'm able to pull that date for January/ 13th month, Febuary/12th months and ect

I tried changing the And Date()) to And Date()-3 but couldn’t get it to work.

Past_6_Full_Months: (IIf(([CORRESPONDENT_PURCHASE_DATE] Between DateAdd("m",-6,Date()) And Date()) And Trim([OMNI_Status1])="9",1,0))

Iv been researching on line but cant fine what I’m looking for

Any advice is helpful.
Thanks
TCB

 

[randy700]

I think what Duane is trying to tell you is that TRIM only removes spaces, not other characters. How about this?

Past_6_Full_Months: (IIf([CORRESPONDENT_PURCHASE_DATE] Between DateSerial(Year(Date()),Month(Date())-0,0) And DateSerial(Year(Date()),Month(Date())-6,1)and [b][red]Left[/red][/b]([OMNI_Status1],1) = "9",1,0))

or this?

Past_6_Full_Months: (IIf([CORRESPONDENT_PURCHASE_DATE] Between DateSerial(Year(Date()),Month(Date())-0,0) And DateSerial(Year(Date()),Month(Date())-6,1)and [OMNI_Status1] [b][red]LIKE "*9*"[/red][/b],1,0))

Randy

 

[dhookom]

pull that date for January/ 13th month, Febuary/12th months and ect

This makes no sense to me.

Date()-3 is three days ago. Is this what you want/expect?

Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

Sorry it took me so long to get back to you, I got pulled into another project. I formula that Randy provided me worked great. Thank you both for your help

TCB