Date Formula Help Please

[TBC]

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

 

[AUGuy]

And Trim([OMNI_Status1])>"?9?",1,0)
I think what you're trying to do here is trim the leading/trailing spaces from your data. In that case Trim([OMNI_Status1]) gets the job done as
it does an LTrim and RTrim. If you adjust your query to read:

Sum(IIf([CORRESPONDENT_PURCHASE_DATE] Between DateSerial(Year(Date()),Month(Date())-13,1) And DateSerial(Year(Date()),Month(Date())-12,0),[Amount_Actual_Loan][COLOR=blue][B] And Trim([OMNI_Status1][/B][/COLOR])))

It meets proper syntax for your IF statement and should do what you want.

Hope this helps

Guy

 

[TBC]

Thank you Guy, I tried your formula and this is the data I'm getting? I'm not sure what I need to change to correct it

JanTest
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1
-1

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

 

[AUGuy]

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

Does it need to actually change the data in the table to which it's querying or just display in the query a "1" if contains 9, else display "0"

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

Does this mean the query needs to figure out what the last day of the month passed is and pull all records that fall between the first and last day of that month? Or is this a fixed period from 2010 for each month. The formula works in my test db if it's fixed for 2010's months.

 

[TBC]

Thanks AUguy,

With the formula you provide I was able to work.

Thanks again for your help
TCB

 

[AUGuy]

Glad to help

 

[TBC]

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

 

[boblarson]

Simplicity? Why not have a form which selects the dates you want and use two text boxes for the start and end dates as the criteria?

 

[TBC]

I need to be able to export it to a spread sheet, so I'd like to to it in a query

 

[boblarson]

I need to be able to export it to a spread sheet, so I'd like to to it in a query

Yes, so? You use a form to select the dates and the query uses that criteria. It is a standard method. As long as the form is open, the criteria will continue to be supplied to the query so the query can be exported using that criteria.

 

[TBC]

do you have an example you can show me?