Error with user defined function querying sharepoint list

[LostAgain]

Hi, I have an Access database that has worked well for the last two or three years. My organisation is moving from a network with shared drives to O365 and Sharepoint. The database is a split database with multiple users. I have been trying to move the tables to sharepoint lists (I am sure a SQL server would work better but no chance of getting that). This transition is proving to be a major pain in the .... Too many problems. I have a custom function using Select Case that works OK on the network, it works on table within the front end but gives Error when it is run on the sharepoint list.

Is the syntax different for sharepoint? Or do I have to set permissions in the sharepoint list to run user defined functions? Or is it just not possible?

The function is below, sorry the formatting got lost in the copy and paste.

Function TSOperationalPOD(TSPort As String, TS1 As String, TS2 As String, TS3 As String, ts4 As String, TS5 As String, POD As String)


On Error GoTo Err_Handler

Select Case TSPort

Case TS1

Select Case TS2

Case Is <> ""

TSOperationalPOD = TS2

Case ""

TSOperationalPOD = POD

End Select

Case TS2

Select Case TS3

Case Is <> ""

TSOperationalPOD = TS3

Case ""

TSOperationalPOD = POD

End Select

Case TS3

Select Case ts4

Case Is <> ""

TSOperationalPOD = ts4

Case ""

TSOperationalPOD = POD

End Select

Case ts4

Select Case TS5

Case Is <> ""

TSOperationalPOD = TS5

Case ""

TSOperationalPOD = POD

End Select

Case Else

TSOperationalPOD = POD

End Select

Exit_Handler:

Exit Function

Err_Handler:
MsgBox "There was a problem with the Report you selected"
Resume Exit_Handler

End Function

 

[Minty]

I have simplified your layout and tidied it up a bit.
You don't need to use the second select case at all.

See if this gives you the desired effect.

Function TSOperationalPOD(TSPort As String, TS1 As String, TS2 As String, TS3 As String, ts4 As String, TS5 As String, POD As String)
   
    On Error GoTo Err_Handler

    TSOperationalPOD = POD
   
Select Case TSPort

        Case TS1
            If TS2 <> "" Then TSOperationalPOD = TS2

        Case TS2
            If TS3 <> "" Then TSOperationalPOD = TS3

        Case TS3
            If ts4 <> "" Then TSOperationalPOD = ts4
       
        Case ts4
            If TS5 <> "" Then TSOperationalPOD = TS5

    End Select

Exit_Handler:

    Exit Function

Err_Handler:
    MsgBox "There was a problem with the Report you selected"
    Resume Exit_Handler

End Function

In my head though, I can't help thinking this is a poor design due to the way you are passing in the parameters.

 

[LostAgain]

Hi, thanks for your help. The revised code is simpler and I think I have a slightly better understanding of how to use the Select Case but unfortunately it works the same as my earlier code. That is, it works with data in a table that is in the database but not with data in a sharepoint list. I get the same #error. I do have a work around but it annoys me that I don't understand what he problem is. My work around is to run the function on the data before adding the data to the sharepoint list and add this data as a new field in the list.

 

[Minty]

Is it something to do with the way Sharepoint is presenting the data?

What error do you get and where?

 

[LostAgain]

Hi, I run the function in a query across a selection of data. The data populates in the query but for the OperationalPOD I get #error.

 

[Minty]

Sorry, that hasn't really helped.
You are getting an error IN the query output?

Can you post up the SQL of the query, and possibly a picture of the query in the designer?

 

[LostAgain]

Hi, yes error in the query output.


SELECT TSOperationalPOD([TS Port],[T/S 1],[T/S 2],[T/S 3],[T/S 4],[T/S 5],[POD]) AS [Operational POD], tblTranshipmentReports.[TS Port], tblTranshipmentReports.POL, tblTranshipmentReports.[T/S 1], tblTranshipmentReports.[T/S 2], tblTranshipmentReports.[T/S 3], tblTranshipmentReports.[T/S 4], tblTranshipmentReports.[T/S 5], tblTranshipmentReports.POD

FROM tblTranshipmentReports;

 

[Minty]

I think the problem might be the function values try this version;

Function TSOperationalPOD(TSPort As String, TS1 As String, TS2 As String, TS3 As String, ts4 As String, TS5 As String, POD As String)
  
    On Error GoTo Err_Handler

    TSOperationalPOD = POD
  
Select Case TSPort

        Case "TS1"
            If TS2 <> "" Then TSOperationalPOD = TS2

        Case "TS2"
            If TS3 <> "" Then TSOperationalPOD = TS3

        Case "TS3"
            If ts4 <> "" Then TSOperationalPOD = ts4
      
        Case "ts4"
            If TS5 <> "" Then TSOperationalPOD = TS5

    End Select

Exit_Handler:

    Exit Function

Err_Handler:
    MsgBox "There was a problem with the Report you selected"
    Resume Exit_Handler

End Function

I'm assuming that the TSPort is a string value either "TS1" to "TS2" etc?

 

[LostAgain]

Hi, Thanks for the suggestion and the help. That didn't solve the problem but I think I am 90% there now. For some reason when moving the data from an Access table in a network drive to a sharepoint list these fields are no longer "empty". I have tried entering different expressions into a query, rather than via the function, to test. I get incorrect results when using ="" or iif(isEmpty), but I get the answer I expect when I use is null. However now I am struggling with the right way to use is null in a select case function.

 

[Minty]

Ah ha - so your empty string or ZLS (Zero length strings) have been converted to nulls.
That explains your problem.

Your TS1, TS2 etc strings will be null values, I'm surprised you don't get a VBA error when the function is called, as a string variable can't be assigned a null value.
In your function call try using Nz()

TSOperationalPOD([TS Port],Nz([T/S 1],""),Nz([T/S 2],""), etc etc

 

[LostAgain]

Yes, that sorted it. I did not expect adding the data to a sharepoint list would make that conversion. Something to store away for later. I wonder, instead of Nz() in the function call is it possible to amend the function? I had a go but manage to get no where.

Thanks for your help and sticking with me on this one.

 

[Minty]

Yes you could amend the function.
Something like

Function TSOperationalPOD(TSPort As String, TS1 As Variant, TS2 As Variant, TS3 As Variant, ts4 As Variant, TS5 As Variant, POD As String)
  
    On Error GoTo Err_Handler

    TSOperationalPOD = POD
  
Select Case TSPort

        Case TS1
            If Not IsNull(TS2) Then TSOperationalPOD = TS2

        Case TS2
            If Not IsNull(TS3) Then TSOperationalPOD = TS3

        Case TS3
            If Not IsNull(ts4) Then TSOperationalPOD = ts4
      
        Case ts4
            If Not IsNull(TS5) Then TSOperationalPOD = TS5

    End Select

Exit_Handler:

    Exit Function

Err_Handler:
    MsgBox "There was a problem with the Report you selected"
    Resume Exit_Handler

End Function

This assumes that TSPort and POD can't be null.

 

[LostAgain]

Hi, correct TSPort and POD cannot be null. Thank you, that makes sense.

 

[Pat Hartman]

Yes, that sorted it. I did not expect adding the data to a sharepoint list would make that conversion.

Finally, something about SharePoint that I like.

ZLS are actual characters whereas Null is nothing. They are also allowed ONLY in text data types. Allowing text fields to hold ZLS leads to nothing but trouble. MS made this the default to "help" novices. But ZLS look just like Null so confusion still exists. Anyway allowing ZLS in fields that you define as required means that a ZLS can be stored instead of CustomerName or some other field which should ALWAYS contain data so think of this as a good change and fix the code to accept nulls rather than masking them on the way in.

You will probably also need to change other code and queries to account for this difference also. Spend some time reviewing everything to see where you are using "" and change that in code to IsNull(somefield) or in queries to "somefield Is Null"

 

[LostAgain]

Thanks,

First - ZLS - Zero Length Stuff?
The source data is coming from an excel report that is imported into a table file. The data is not altered at all at this stage. Yes there was another query with a problem, for data from a different excel report. So something new I have learnt to look for. I am frustrated by this move to sharepoint, seems like I have to learn a whole lot more.

I see plenty of stuff on line about deleting large files, well not so large really, just a few hundred lines. I cheated to start and just deleted the entire sharepoint as it was a test site but will have to develop new strategies as the databases move across. What I do not see is an easy, accurate, and foolproof way to move largish existing access tables to sharepoint. For example I am trying to copy across the unLocodes. Just one of many new things I am having to learn to work with.

Appreciate your help with the above. Very helpful, thank you.

 

[Minty]

ZLS = Zero Length String which is ""
Which as you have discovered isn't the same as a NULL value.

 

[Pat Hartman]

Did you determine if the foreign keys in the child tables changed?

Did you determine if you need to change your validation rules/forms/queries to account for them now being null rather than ZLS?

 

[LostAgain]

Hi

I have changed the queries that needed to be changed and the database has been extensively tested and is working OK. I don't think I have missed any. Why would the foreign keys change? Sorry I am not understanding where you are trying to guide me.

 

[Pat Hartman]

You said that a new PK was added when the tables were uploaded. That implies the need for new foreign keys. Did that issue get resolved?