Help with expression builder

[Caddie]

Hi -

I tested the following code and it works great.

Original Term: IIf([Contract Not Expired]=0 And [Contract Details]![Renewal Term]="Original Term" And DateDiff("d",Date(),DateAdd("m",[Contract Details]![Term],[Service Details]![End Date]))>0,DateDiff("d",Date(),DateAdd("m",[Contract Details]![Term],[Service Details]![End Date])),IIf([Contract Not Expired]=0 And [Contract Details]![Renewal Term]="Original Term" And DateDiff("d",Date(),DateAdd("m",[Contract Details]![Term]*2,[Service Details]![End Date]))>0,DateDiff("d",Date(),DateAdd("m",[Contract Details]![Term]*2,[Service Details]![End Date])),IIf([Contract Not Expired]=0 And [Contract Details]![Renewal Term]="Original Term","ERROR",0)))


However I need to expand this querry to be able to handle up to 7 renewals, I've now created this code in notepad, see attached. I didn't format the attached code to make it easier to read because I wanted you to see it exactly as it is in the code builder.

When I paste the code into the expression builder and say ok, I don't get an error, but the new code isn't being saved. When I go back into the builder the original code is still there.

Any thoughts on why this is happening?

Thanks.

 

[DCrake]

Are you saving the query at any point?

 

[Caddie]

No, I go into the expression builder of the field I want to edit. Past the code. Hit Ok. That brings me back to the query builder. I then go back into the expression builder of the field I just changed and the new code isn't there, it still has the old code in it.

 

[DCrake]

I assume that this unbound field is on a form. just go to the properties of the control and set its control source to the sql by pasting the string in there. Also I think that this could be done alot simpler but cannot offer a suggestion at this stage.

 

[Caddie]

Here is the purpose of the code. If a contract expires on Dec 31, 2008 and the original term was 1 yr and the renewal terms of the agreement are that the contract will auto renew for the same term as the original contract (in this case 1 year, but it could be 3 yrs or 6 months).

So my report is calculating the remaining value of contracts, so it calculates the "datediff" between todays date and the expiry date of the contract.

Therefore since the contract in my example above is expired, I need the code to take the original expiry date Dec 31/08 and add on 1 year since this is the renewal term. Then look at this revised expiry date which is now December 31, 2009. If the datediff between todays date and that date is a positive, then whats the datediff in days, if it's a negative, then I need take the original expiry date (Dec 31/08) and add 2 years (2 renewal terms of 1 year) which gives me Dec 31/10. Now it does a datediff on todays date compared to this new date, if it's positive what is the datediff in days, if it's negative repeat the above using 3 renewal terms, then 4 and so on until the datdiff compared to todays date is a positive number.

Make sense? This is what I'm trying to do. Can you offer an easier solution then the code I am trying to use?

 

[SOS]

I would say -

Get rid of the IIF and create a function to handle it. Once you get past two or three IIF's it is time to be practical and build a function that handles it.

 

[Caddie]

I presume you mean using VBA on the report side? I tried taht but couldn't figure out how to place the vba code onto the texbox on the report. Where do I enter it, on an event? If so which one?

 

[vbaInet]

If your function is returning a value, to call it in the Control Source, you do this:

=MyFunction()

 

[Caddie]

Okay, I did some "googling" and I think I understand what you mean by making my own function. You mean in VBA I create a new module? If so, how do I reference my database fields?

 

[DCrake]

Think about what fields in your table/query are used in your decision making logic and pass them to the function as arguments.

Outcome:NewFunction(Field1, Field2, Field3)

Now when you create your function you define the arguments

Public Function NewFunction(Arg1 As Date, Arg2 As String, Arg3 As Long) As String

Your Business logic here

NewFunction = Answer

End Function

Now you can apply all the business logic based on the data passed from the query to your function and at the end the answer is passed back to the query as the result.

 

[Caddie]

Got it:

Public Function Original_Term_Renewal(Renewal_Type As String, End_Date As Date, Term As Integer)
Dim Multiplier As Integer
Dim Offset As Integer
Dim Expiry_Date_Temp As Date
Multiplier = 0
Expiry_Date_Temp = End_Date
While (DateDiff("d", Date, Expiry_Date_Temp) < 0)
Multiplier = Multiplier + 1
Offset = Term * Multiplier
Expiry_Date_Temp = DateAdd("M", Offset, Expiry_Date_Temp)
Wend
Original_Term_Renewal = DateDiff("d", Date, Expiry_Date_Temp)
End Function


Thanks for your help guys!

 

[JANR]

FYI, If there is any chance that Offest can get higher than 32000 then you will get an overflow error. Err on the right side and declare it as LONG and not Integer.

That goes for all multiplication, it dosen't take much to exeede this limit for integers

JR

 

[Caddie]

Ok, thanks for the tip.