Date Formula Help Please

[TJVFree]

Hi everyone again.

I’m working on making my database roll with each month that changes.
Below is what I have so far, and was hoping someone could help me with the rest.

The error message I’m getting is:
The expression you entered has a function containing the wrong number of arguments.

Jan_Loan_Amount: Sum(IIf([CORRESPONDENT_PURCHASE_DATE] Between DateSerial(Year(Date()),Month(Date())-13,1) And DateSerial(Year(Date()),Month(Date())-12,0),[Amount_Actual_Loan])And Trim([OMNI_Status1])>"?9?",1,0)))

Feb_Loan_Amount: Sum(IIf([CORRESPONDENT_PURCHASE_DATE] Between DateSerial(Year(Date()),Month(Date())-12,1) And DateSerial(Year(Date()),Month(Date())-13,0),[Amount_Actual_Loan])And Trim([OMNI_Status1])>"?9?",1,0)))

Thanks for your help

 

[TJVFree]

I want access to look in this field ([CORRESPONDENT_PURCHASE_DATE] and have it just pull the dates for January 1st to January 29th or the last day of the month
Then
Trim field ([OMNI_Status])
If in ([OMNI_Status]) if there is a 9 make it a 1 and count them and if not make it a 0

I will need to repeat this step for February, March, ect

Let me know if I should explain better

Thank you

 

[dhookom]

CoreyVI,

Can you just type about 5-10 records into a reply with the correct field names (significant fields only) and tell us what total you would expect.
[tt][blue]
[CORRESPONDENT_PURCHASE_DATE] [Amount_Actual_Loan] [OMNI_Status1]
12/12/2010 100.00 9
01/12/2011 101.00 5
01/12/2011 110.00 4
... more records here that illustrate your needs ...
[/blue][/tt]
Then the desired values in the output.

A properly worded question with sample records and desired output might help reduce the confusion I typically experience with many of your questions.

Is the field OMNI_STATUS or OMNI_Status1? Is this field text or numeric? Does the field actually have the "?"s?


Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

Hi Duane, Thanks for looking in to see if you could help, which I'm sure you would have been able to. I was about to get this formula to do what I needed to do

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

Thanks again Duane

TCB

 

[dhookom]

Did you get it or are you still needing assistance? IIf() expect 3 arguments. Do you have three arguments or does this error message still hold true:

The expression you entered has a function containing the wrong number of arguments.

Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

Hi Duane - Thanks for checking in on me. we were able to get one of my formulas to work. The formula below is getting the total amount per app during December. Works great.

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]))

Now I'm trying to do a similar formulas that instead of getting the ([Amount_Actual_Loan]) I need to get the [OMNI_Status1]) which at this point I need to count the "9" during December.

Within the OMNI_Status1 there are 5's, 1's 3's and 9's. But there is a space inforent of each number so I need to do a trim. I will also be doing a count of all the numbers that are not a 9 but that should be a simple = to <>

Thanks again Duane always a huge help

 

[TJVFree]

Thought I could just add this, but didnt work

Trim([OMNI_Status1])>"?9?",1,0)))

 

[dhookom]

Your expression looks like it is still missing the final argument in the IIF()

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]))

Check your other thread for your other question.

PLEASE don't start multiple threads in multiple forums for the same basic question. You'll land on my (and possibly others') ignore list.

Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

sorry about that and thanks Duane