Error with user defined function querying sharepoint list (1 Viewer)

LostAgain

New member
Local time
Tomorrow, 00:58
Joined
Aug 23, 2021
Messages
10
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

AWF VIP
Local time
Today, 11:58
Joined
Jul 26, 2013
Messages
10,355
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.
Code:
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

New member
Local time
Tomorrow, 00:58
Joined
Aug 23, 2021
Messages
10
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

AWF VIP
Local time
Today, 11:58
Joined
Jul 26, 2013
Messages
10,355
Is it something to do with the way Sharepoint is presenting the data?

What error do you get and where?
 

LostAgain

New member
Local time
Tomorrow, 00:58
Joined
Aug 23, 2021
Messages
10
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

AWF VIP
Local time
Today, 11:58
Joined
Jul 26, 2013
Messages
10,355
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

New member
Local time
Tomorrow, 00:58
Joined
Aug 23, 2021
Messages
10
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;

image001.png
 

Minty

AWF VIP
Local time
Today, 11:58
Joined
Jul 26, 2013
Messages
10,355
I think the problem might be the function values try this version;
Code:
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

New member
Local time
Tomorrow, 00:58
Joined
Aug 23, 2021
Messages
10
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

AWF VIP
Local time
Today, 11:58
Joined
Jul 26, 2013
Messages
10,355
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

New member
Local time
Tomorrow, 00:58
Joined
Aug 23, 2021
Messages
10
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

AWF VIP
Local time
Today, 11:58
Joined
Jul 26, 2013
Messages
10,355
Yes you could amend the function.
Something like
Code:
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

New member
Local time
Tomorrow, 00:58
Joined
Aug 23, 2021
Messages
10
Hi, correct TSPort and POD cannot be null. Thank you, that makes sense.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:58
Joined
Feb 19, 2002
Messages
42,981
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

New member
Local time
Tomorrow, 00:58
Joined
Aug 23, 2021
Messages
10
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

AWF VIP
Local time
Today, 11:58
Joined
Jul 26, 2013
Messages
10,355
ZLS = Zero Length String which is ""
Which as you have discovered isn't the same as a NULL value.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:58
Joined
Feb 19, 2002
Messages
42,981
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

New member
Local time
Tomorrow, 00:58
Joined
Aug 23, 2021
Messages
10
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

Super Moderator
Staff member
Local time
Today, 07:58
Joined
Feb 19, 2002
Messages
42,981
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?
 

Users who are viewing this thread

Top Bottom