[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

VBA Function only gives me one reply

Hi there,

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

My Function:

Function StatusReport(StDate As Date, Exit As Variant) As String
    If IsNull(StDate) Then
            StatusReport = "Rejected"
    Else
        If StDate > Date Then
            StatusReport = "Applying": Exit Function
        Else
            If IsNull(Exit) Then
                StatusReport = "Working": Exit Function
            Else
                StatusReport = "Exit": Exit Function
            End If
        End If
 
    End If
 
End Function

Open in new window


Somehow I only get the status "Working" returned. In my query I have no limitations. How is this possible and how can I fix it?
0
arf73
Asked:
arf73
  • 6
  • 3
  • 2
  • +1
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
Ken ButtersCommented:
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

Open in new window

0
 
arf73Author Commented:
@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

Open in new window

I only get an error.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<
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.
0
 
arf73Author Commented:
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;
0
 
arf73Author Commented:
Ok, I solved it myself. I changed the StDate into a variant as well because it had the NULL value in some cases (just like the xExit). After that I changed the INNER JOIN to a RIGHT JOIN
0
 
arf73Author Commented:
Nice
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Good luck with that.
0
 
arf73Author Commented:
I can hear you being cynical, but thanks anyway :-)
0
 
arf73Author Commented:
Well, I'm not a genius...
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now