VBA Function only gives me one reply

Define 'no limitations'.  Can it paint my house?

Functions by definition return one value, not a set of values.

Also, explain why Exit is a Variant. since you're doing a >0 if a non-numeric value gets passed this could throw an error.

Exit is a reserved word.... you cannot use it as a variable name.

The following returns a value of "informing"

Sub test()
    
    Dim returnvalue As String
    returnvalue = StatusReport(Now, 0)
    
End Sub
Function StatusReport(StDate As Date, xExit As Variant) As String
' if StDate exists then...
 
    If StDate > 1 Then
        ' state is planned
        If StDate > Date Then
            'stdate is in the past
            StatusReport = "Informing": Exit Function
        Else
            'stdate is in the future

            'exit exists
             If xExit > 0 Then
                StatusReport = "Exit": Exit Function
            Else
                StatusReport = "Working": Exit Function
            End If
        End If
    Else
        If xExit > 0 Then
            StatusReport = "Rejected": Exit Function
        Else
            StatusReport = "Incomplete": Exit Function
        End If
    End If
End Function

Select allOpen in new window

@jimhorn Yes, it can paint your house. What I meant is that I did not set any criteria in the query.
Variant: No reason.
This might not be clear, but I don't have a clue what I am doing.

@buttersk Thanks, but it doesn't work yet. I translated 'Exit' from my own language to make it easier to understand for everybody and forgot that it is a reserved word. So in my code that wasn't the problem. I copied your code and checked the results of the query, still only "Working" results. I don't know how to check the 'test' Sub, but it looks like the problem might be in the query.

My field looks like this:

status: StatusReport([Insch]![date];[Sol]![out])

with [Insch]![date] being a date and [Sol]![out] being a string (I think)

[Sol]![out] is a field in the [Sol] table that is linked to another table, but when I change my VBA code to

Function StatusReport(StDate As Date, xExit As Integer) As String

Select allOpen in new window

I only get an error.

<<I built a function in VBA that should give me some kind of statusreport that I want to use in a query.>>

 Also note that when calling from a query, you must pass in a field name so the procedure gets called for each row.

If you pass nothing, the procedure will only get called once.  

Believe your doing that, but it's worth mentioning.

Also be aware that if you have criteria on the column that calls the procedure, it will be called 2x for each row, which can impact performance depending on the procedure.

Jim.

>What I meant is that I did not set any criteria in the query.
Your question makes no reference to a query, so ... are you calling this finction in a query?  If so, please post the SQL of your query into this question.

>Variant: No reason.
Big no-no, as Variant means 'pass me anything:  date, number, object, whatever...', so by doing this the function could throw an error if a date is passed and it was expecting a number.  HIGHLY recommend changing Variant to date, or integer, or whatever this value is.

>StDate As Date, xExit As Variant
I also recommend prefixing these values so it's real obvious what data type they are.
dtStartDate - the dt means date
dtExitDate - the dt means exit date  
sName - the s means string
iCustomerNumber - the i means integer
you get the idea...

>>[Sol]![out] being a string (I think)
Uhh ... if you're passing a string, but in your function it does a Exit > 0, strings can't be evaluated as being greater than zero, so you'll have to figure out what to do here.

<<
Uhh ... if you're passing a string, but in your function it does a Exit > 0, strings can't be evaluated as being greater than zero, so you'll have to figure out what to do here.
>>

Actually, that will work.  Really sloppy yes, but it will work.  Try:

If "1" > 0 then ? "Yes"

in the debug window vs

If "-1" > 0 then ? "Yes"

 I'd never do it like that though.

Jim.

The SQL of the query is

SELECT Soll.vrnm, Soll.acnm, StatusReport([Inschr]![date],[Soll]![out]) AS status
FROM Inschr INNER JOIN Soll ON Inschr.id = Soll.inschr;

Nice

Good luck with that.

I can hear you being cynical, but thanks anyway :-)

Well, I'm not a genius...