Function from a query

[Stoss]

Hello all,

How do I get specific information from a function that I run from a query? I will pass 2 parameters to the function but I want to get some text in return.

I use this function for populating text boxes from other forms but it has the same logic structure so i would like to also use it for the query.

Anyway, how do I get information from the function back to the query (as in returning a certain value)?

Right now, the function looks like this when called from a form (Current Event).

Function Priorities(strPriority As String, OpenDate As Date, ctrl As Control)

When calling from the query, the ctrl part will not be used so I believe that I can put the Optional keyword in front of it and just the strPriority and OpenDate will be passed. I then need to return info based on what the function decides to do with that info

Example: strPriority = "Priority 2" and OpenDate = "12-20-2010"

Query runs calling that function and would return "6" (sometimes it might need to say "Immediately").

Stoss

 

[pbaldy]

In design view it might look like:

MadeUpName: Priorities([TextFieldName], [DateFieldName])

Where the two values passed are field names in the data. You could also get the values from a form if appropriate.

 

[Stoss]

I think that I understand calling the function from the query (I think LOL). Where I am confused it how to I extract certain data.

In other words, if the function is called from the form "ctrl" populates the text box by
ctrl = "This Discrepancy is......blah"
but when called from the query "ctrl" won't be used and the text that would be return might be
DaysLeft = "6"

How do I get "6" back to the query?

Stoss

 

[pbaldy]

You're saying the function will return

DaysLeft = "6"

but you only want to display the 6? That would be fairly easy, but you've also implied the function could return other things (like "Immediately"), which would change everything. Do I have it wrong, or can you clarify exactly what the function will return and what you want the query to display? Typically it would display the results of the function.

 

[Stoss]

The function looks like this (this is just a couple but hopefully you will see the structure that I am looking at).

As is right now, called from the form it populates a textbox called Priority that I pass it through the function (ctrl) as me.priority. That works great!

But, now, using the same structure from the query. I want to return to the query "myDays" as seen in the code (right now this is added in this function to show what I want to return to the query (if it is called from the query).

If strPriority = "Priority 1 (Immediately)" Then
        ctrl = "This discrepancy must be fixed IMMEDIATELY!"
        myDays = "Immediately"

    'Sets Priority 2 Priorities
    ElseIf strPriority = "Priority 2 (7-Days)" Then
        If (OpenDate + 7) > Date Then
            ctrl = "This discrepancy is " & DateDiff("d", OpenDate, Date) & " day(s) old and must be closed by " & DateAdd("d", DateDiff("d", Date - 7, OpenDate), Date) & ".  (" & DateDiff("d", Date - 7, OpenDate) & " day(s) remaining)"
            myDays = DateDiff("d", Date - 7, OpenDate)
        ElseIf (OpenDate + 7) = Date Then
            ctrl = "This discrepancy is due today!"
            myDays = "0"
        Else
            ctrl = "This discrepancy is past due by " & DateDiff("d", OpenDate + 7, Date) & " day(s)."
            myDays = "Past Due by: " & DateDiff("d", OpenDate + 7, Date)
        End If

-Stoss

 

[pbaldy]

Ah, I think I see what you mean now. Typically the function itself would return the value, not set a value. You'd change the function line to:

Function Priorities(strPriority As String, OpenDate As Date) As String

dropping the form and adding the bit at the end. Then within the function, instead of setting a form control you'd have this:

Priorities = "This discrepancy must be fixed IMMEDIATELY!"

That will let the function return a value to whatever calls it. You could have it populate a textbox by having a control source of:

=Priorities(Field1, Field2)

 

[Stoss]

Ok, Thanks pbaldy!

I think I understand and as of now makes total sense. I still have the problem of having 2 different sources access this function and then give out different info based on where it is coming from (i.e. the query or the form).

So, in the code above, how do I return the value of "ctrl" when using the form and return the value of "myDays" when using from a query?

Would it make sense to make the function like this
Function Priorities(strPriority As String, OpenDate As Date, fromQuery as boolean) As String
and then have the query call it this way
=Priorities(strPriority, OpenDate, True)
and then have in the function code put something like "
If fromQuery = True Then
Priorities = "Immediately"
else
Priorities = "This discrepancy is due Immediately...."
End if

Not sure if that is a smart way to do it or not....

-Stoss

 

[pbaldy]

I guess it would depend on the big picture, but that would be a viable solution. Another would be to have it return the same thing but call it like this from a form:

="This discrepancy is due " & Priorities(Field1, Field2)

So the function would return "Immediately" no matter what, but the form will add the text. You have the best grasp of all the permutations what it needs to do, so go with your gut.

 

[Stoss]

Thanks so much for your advice.

I think that I will go with the boolean route. I will have to go that route because of all the different type of calculations and I have a lot of extra date manipulations for the form but not for the query.

I really appreciate your time in helping me get on track with this!
-Stoss

I'll let you know how it turns out....

 

[pbaldy]

Happy to help Stoss; post back if you get stuck.

 

[Stoss]

Wow, I can't believe it but it works!!!!!!!!!!!! Almost on the first try too!

Here is the code that I finally settled on

'Sets Priority 2 Priorities
    ElseIf strPriority = "Priority 2 (7-Days)" Then
        If (OpenDate + 7) > Date Then
            If fromQuery = True Then
                Priorities = DateDiff("d", Date - 7, OpenDate)
            Else
                Priorities = "This discrepancy is " & DateDiff("d", OpenDate, Date) & " day(s) old and must be closed by " & DateAdd("d", DateDiff("d", Date - 7, OpenDate), Date) & ".  (" & DateDiff("d", Date - 7, OpenDate) & " day(s) remaining)"
            End If
        ElseIf (OpenDate + 7) = Date Then
            If fromQuery = True Then
                Priorities = "0"
            Else
                Priorities = "This discrepancy is due today!"
            End If
        Else
            If fromQuery = True Then
                Priorities = "Past Due by: " & DateDiff("d", OpenDate + 7, Date)
            Else
                Priorities = "This discrepancy is past due by " & DateDiff("d", OpenDate + 7, Date) & " day(s)."
            End If
        End If

I don't know if it is the cleanest code but it sure does work. It works from the forms and from the query! I am not sure what other way I could have done this to make in more efficient though? At least now, all my code resides in one place.

Thanks so much, you really helped me get going in the right direction!
-Stoss