Solved Type Mismatch in Query sort - Sharepoint data

[HalloweenWeed]

Hello,
I am trying to manipulate data from an Access table linked to Sharepoint, in a new database in Access 2016 or Office 365 Access. I have the link working successfully. In particular, one field that I have to do some conversion on to convert it to a numeric value. Unfortunately, it is a text format field, when it should be a numeric (I have no control over it). Since the data entry person sometimes puts a "MR" prefixing the number, I have to to some checking of that, remove it, and convert to a number (integer, no decimals). That works fine. FYI Sharepoint often transfers a field of type "calculated value," but this is not the case here, as I have done checking with "VarType" (within the Query), and they come in as type 1 and 8 only. So I do subqueries to work only with type 3 (Long Int). Full disclosure, here is the Field Query algorithm:

MR: IIf(IsNull([MR #]),CLng(0),IIf(Len([MR #])>2,IIf(Left([MR #],2) Like "MR",CLng(Val(Right([MR #],Len([MR #])-2))),CLng(Val([MR #]))),IIf(Val([MR #])>0,CLng(Val([MR #])),CLng(0))))

It works fine. It also filters out the Nulls and zeros. I then created a subquery to make certain that the data is converted to Long Int:

MR: CLng([RawData]![MR])

SQL:

SELECT RawData.ID, RawData.[Incident #: ID], RawData.[Unit:], CLng([RawData]![MR]) AS MR, VarType([MR]) AS MR_VarTyp, RawData.[Last Name, First Name], RawData.[Date of Incident:], VarType([Date of Incident:]) AS Date_VarTyp, RawData.Seclusion, RawData.[Total Seclusion Time], VarType([Total Seclusion Time]) AS Time_VarTyp
FROM RawData
WHERE (((CLng([RawData]![MR]))<>0) AND ((RawData.Seclusion)=True) AND ((RawData.[Total Seclusion Time])>"0") AND (([RawData]![MR]) Is Not Null));

And I checked the vartype with another field in the query:

MR_VarTyp: VarType([MR])

It reports nothing but 3's (Long Int, no Nulls). But the problem I get, is when I set any sort on the "MR" field, I get "Type Mismatch" error when I try to run it. I even tried in desperation making a sub-subquery, to make certain that it was only looking at Long Int values, but still I get the same error trying to sort the "MR" field.


I also get the same "Type mismatch" error when I try to sort from the dates (within the query), and they all show as type 7: Date value. This one is crucial to my end goal.

Any ideas? I can sort all the other fields (one text, one text numbers with a hyphen, one autonumbered by Sharepoint). I have searched the internet for this problem, but I can find no applicable answers, and none at all involving Sharepoint data (could it be a Sharepoint-related problem?).

 

[Ranman256]

you have too big a string, too many things to go wrong.
clean these fields up in the query 1 at a time, THEN run the main query.

 

[theDBguy]

Hi. Since you can't convert nulls to a number anyway, maybe try starting out with a query that eliminates nulls first. Then, use a query to convert the result of that into numbers, and then apply sort to that. Just a thought...

 

[HalloweenWeed]

Well long story short: it was because I am working remotely, and I was starting Access with the remote laptop. I tried it by remote Desktop from my office computer, and it sorted without problems!

I tried remoting in because the little db had gotten so slow, it was taking a minute or two for simple clicks on query design elements, before I could then make the changes I tried. It was reading about 4,000 records from Sharepoint, each with about 160 fields. Unfortunately, if there is any way to reduce the fields that I download, it's beyond my knowledge, I have to download the whole table. Anyway, problem solved. Now for my next step:


I want to make scatter point chart of durations in Excel (Access tables are cr@p), using a table of dates/times and durations (two fields only, x & y axes). I want the chart to dynamically adjust to any number of records, so a minimal operator entry is necessary after more records have been added. I can import a table from Access into Excel and it should (theoretically) adjust to the new table size (hopefully). But the main reason I need to use Access is I need to filter & transform the data first (pre-processing). I need to select a MR#, then a date range, I will use an Access form to do that and then a button to open the Excel file for the chart, for printing, for a report to other staff. Do you know a better way of doing this? Keep in mind I have to take text dates & times, with non-numerical characters (sometimes with colon, sometimes without), add them together for a 'date/time' serial number (needed for the scatter point chart), and then sort that. After filtering out nulls and non-numerics, and only when the "Seclusion" field is 'true' and there is a numeric value for 'duration.' Also, duration is a text field and needs to be converted to an integer (I have my work 'cut out for me').

Like I asked, if you know a better way of doing this I am all ears. TY.

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Cheers!