Data Not Showing Up Where Data Should

[TBC]

I have a formula that should count all the 9’s and not 9’swithin a certain period

Jan_Loan_Amoun: Sum(IIf([Expiration_date] Between #1/1/2010# And #1/31/2010# And ([OMNI_Status])='9',1,0))

Why cant I count the 9’s for the month of January? My data shows that I have 9’s

I have also attached the database to help better understand what type of data I have and what I’m trying to do.

I really appreciate all the help and advice
TCB

 

[boblarson]

Your OMNI_Status Field has a space before the 9 so you would either need to check using the TRIM function or you would need to use
[OMNI_Status] = ' 9'

in order for it to recognize it. Also, it isn't working in the sample you provided because there are no January 2010 dates in there. 2008 is the latest.

The data in OMNI_Status is bad because some numbers have the space and others do not. So, you would probably want to use the Trim function instead:

Jan_App: Sum(IIf([Expiration_date] Between #1/1/2008# And #1/31/2008# And Trim([OMNI_Status])="9",1,0))

 

[TBC]

That worked. Thanks again

Your OMNI_Status Field has a space before the 9 so you would either need to check using the TRIM function or you would need to use
[OMNI_Status] = ' 9'

in order for it to recognize it. Also, it isn't working in the sample you provided because there are no January 2010 dates in there. 2008 is the latest.

The data in OMNI_Status is bad because some numbers have the space and others do not. So, you would probably want to use the Trim function instead:

Jan_App: Sum(IIf([Expiration_date] Between #1/1/2008# And #1/31/2008# And Trim([OMNI_Status])="9",1,0))