Complex Query...Advice needed

[Stoss]

Hello All,

I am in need of some advice....

I am running a search box that a person can select 1 or more items ("Priorities"). I need to run some complex (not hard just lengthy) custom queries but I don't know the best route to take.

As it stands now, I would have to put in a multiple multiple IIF statement...

i.e. Days Remaining: iif([Priority] = "Priority 1", "Due Now", iif([Priority] = "Priority 2", iif([Open Date] + 7) >= Date, ............. etc.

I will show you a little bit of the code that I wrote to set the text boxes (independent of this query) that runs similar logic and that should make the above statement make more sense.

ElseIf strPriority = "Priority 2 (7-Days)" Then
            If ([OPEN_DATE] + 7) >= Date Then
                If ([OPEN_DATE] + 7) <> Date Then
                    Me.txtClosedBy = "This discrepancy is " & DateDiff("d"[OPEN_DATE], Date) & " day(s) old and must be closed by " & DateAdd("d", DateDiff("d", Date - 7, [OPEN_DATE]), Date) & ".  (" & DateDiff("d", Date - 7, [OPEN_DATE]) & " day(s) remaining)"
                Else
                    Me.txtClosedBy = "This discrepancy is due today!"
                End If
            Else
                Me.txtClosedBy = "This discrepancy is past due by " & DateDiff("d", [OPEN_DATE] + 7, Date) & " day(s)."
            End If

I just don't know the best way to handle this as you can see, the nested nested nested iif statements in the query would get rather lengthy and confusing.

Any advice would be great!
Stoss

 

[stopher]

You've unnecessarily introduce an extra nested IF. Your code could be simplified to this (there are only three cases to deal with):

ElseIf strPriority = "Priority 2 (7-Days)" Then
            If ([OPEN_DATE] + 7) > Date Then
                Me.txtClosedBy = "This discrepancy is " & DateDiff("d", [OPEN_DATE], Date) & " day(s) old and must be closed by " & DateAdd("d", DateDiff("d", Date - 7, [OPEN_DATE]), Date) & ".  (" & DateDiff("d", Date - 7, [OPEN_DATE]) & " day(s) remaining)"
            ElseIf ([OPEN_DATE] + 7) = Date Then
                Me.txtClosedBy = "This discrepancy is due today!"
            Else
                Me.txtClosedBy = "This discrepancy is past due by " & DateDiff("d", [OPEN_DATE] + 7, Date) & " day(s)."
            End If

What other priorities are there? Why doesn't Priority 1 have the same responses as Priority 2? Surely the response is either not due, due or overdue.

If it gets too difficult to write in a query I would just write a function for Days Remaining an call it when needed.

hth
Chris

 

[Stoss]

Priority 1 is just set to "Immediately" (no calculations). Then there is a Priority 3 (30 day calcs), Priority 4 (90 day calcs), then Priority 5 is just "As Required".

Thanks for pointing that extra nest in there, after working on the code so long, my eyes get crossed and brain gets fried DOH

I still have a problem with a rather lengthy query statement though. If I use a function, can you pass query parameters to it? i.e. another field in that query ([Open Date])

-Stoss

 

[stopher]

I still have a problem with a rather lengthy query statement though. If I use a function, can you pass query parameters to it? i.e. another field in that query ([Open Date])

Yes, you can pass arguments to the function. Take a look here or google creating functions in VBA. The link I've given is for creating a function in Excel but it's just the same for Access.

hth
Chris