IIF and Statement

[TBC]

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

 

[CBrighton]

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

What exactly is the output you are expecting?

iif(Trim([OMNI_Status1])="9",1,0)

should do what you have requested but there is a lot more code relating to the purchase date field before it looks at the status field.

Obviously, this assumes the status field is text formated, otherwise there is no need for "s around the 9.

 

[TBC]

I’m trying to find all the “9” in the ([OMNI_Status1]) that fall between the last full month to the past 3 months

02/01/11 4 < - Found 4 9’s
01/11/11 1 <- Found 4 9
12/25/10 2 <- Found 2 9’s
11/14/10 8 <- found 8 9’s

Please let me know if I can provide any additional information
Thanks for you help
TCB

 

[CBrighton]

So you want it to count the 9's rather than display a 1 to signify the field has a 9?

I'd be tempted to create a query which has the date criteria and counts the 9's then add that as another source in the main query, linked to the table via the PK. Adding the count field from that query to the main query should then give me what I want, although it may make the query un-updatable by including an aggregate query as a source.

Tbh it's not something I have done so I'd try a few different things, off the top of my head I don't know what the best way to do it would be.

 

[TBC]

I'm trying to understand this formula. To be exact I'm trying to make this formula work for 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))

Any advice is helpful.
Thanks
TCB