Variable Datediff?

[jstroh]

It's hard to explain so I'll just ask. I have a datediff field on my report giving me the number of months an employee has been with the company. Now can I add a field that will allow me to show what level they should be at based on the result of the date diff. Example, 0-3 is level 2 4-8 is level 3 9-12 is level 4 and 12+ is level 5. Now can the report learn what the datediff is then depending on that result assign it one of the 3 levels? Or, I have each level's date recorded and printing on the report, can I shade each level that they qualify for based on the datediff information listed above? Can either of these be accomplished?

 

[simongallop]

First part use an Iif statement ie Iif(DateX<4,2,iif(DateX<9,3,iif(DateX...etc.

 

[jstroh]

The following is what I used, but it brings up a comma use error. What do you recommend?

Iif([text40]<4,2,iif([text40]<9,3,iif([text40]<12,4,iif([text40]>12,5)

 

[David R]

Wouldn't Select Case work better in this instance? It will evaluate your expression only once and then search out the corresponding value.

An example based on your data would be something like:
Function OptimumLevelLookup(QueryFieldDateDiff, OptLevel)
Select Case QueryFieldDateDiff
Case 0 To 3
OptLevel = 2
Case 4 To 8
OptLevel = 3
Case 9 To 12
OptLevel = 4
Case Is > 12
OptLevel = 5
Case Else
'Shouldn't occur - put error checking here
End Select
End Function

Lookup 'Case' in the help for more information.

[This message has been edited by David R (edited 12-12-2001).]

[This message has been edited by David R (edited 12-12-2001).]

 

[jstroh]

David,
I'd like to try that but I don't know where to put it. Can you help?

 

[David R]

To be honest, jstroh, I don't know. I've worked with Case before in other languages and I know it's very powerful, but as to how to use it in this case (no pun intended), I'm not really sure.

I'd rather give you no advice than bad advice.

 

[KKilfoil]

If you want to change the value of a control on a report for each separate detail record using VBA code, then I would put the code in the 'On Format' event of the detail section.

You should also be able to so this as a caclulated field in the report's source query, or in an unbound text control like you started with. When you are nesting iif() statements, you have to be careful with your parentheses!

Try:

=iif(text40<4,2,iif(text40<9,3,iif(text40<12,4,5)))

You don't need the last iif() that you were using, and it would have returned 0 if text40=12!

The shading is a bit more complicated. You do need some code in the OnFormat event. You also nned to create a text control for each level (2,3,4, and 5?) with distinct names, like txtShade2, txtShade3, etc. Position them appropriately on the detail section of your report. Add the following code to your On Format event:

Me!txtShade2.Visible = False
Me!txtShade3.Visible = False
Me!txtShade4.Visible = False
Me!txtShade5.Visible = False
iif text40 > 1 then Me!txtShade2.Visible = True
iif text40 > 2 then Me!txtShade3.Visible = True
iif text40 > 3 then Me!txtShade4.Visible = True
iif text40 > 4 then Me!txtShade5.Visible = True


Note that this is untested code and I might have some typos.