UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Intermittent Query Error: Mismatch Type, Access 2013    
 
   
KDavidP1987
post Oct 4 2018, 12:44 PM
Post#1



Posts: 67
Joined: 12-June 18



Hello all,

Dealing with a frustrating little error, and I just need to know if it is fixable or a common issue in Access.

Context:
I was given access to an read-only ODBC connection to our companies ticketing system, to generate reports directly through the database to replace reports we currently generate using a front-end app. So far things have been going well, though there have been numerous challenges in replicating metrics and reports we currently use.

One such report is a simple OPEN Incidents Report which goes out to the manager of a certain department (BASIS). I developed the queries to generate this table of OPEN Incidents currently assigned to their queue, and it was running fine for the longest time.

CODE
SELECT dbo_MASTER3_TIMETRACKING.mrID AS [Ticket Number], Replace(Replace([mrSTATUS],"_b"," "),"_","") AS [Ticket Status], Min(dbo_MASTER3.mrSUBMITDATE) AS [Submission Date], DateDiff("d",[mrSUBMITDATE],Now()) AS AgeDays, Max(dbo_MASTER3_TIMETRACKING.mrTIMEDATE) AS [Last Update], Count([qry_BASIS-Team].EmployeeID) AS [BASIS Agents], Replace(Replace([mrASSIGNEES],"_b"," "),"_","") AS Assignee, PlainText(RemoveHTML([mrDESCRIPTION])) AS DESCRIPTION

FROM (dbo_MASTER3 INNER JOIN dbo_MASTER3_TIMETRACKING ON dbo_MASTER3.mrID = dbo_MASTER3_TIMETRACKING.mrID) LEFT JOIN [qry_BASIS-Team] ON dbo_MASTER3_TIMETRACKING.mrTIMEUSER = [qry_BASIS-Team].EmployeeID

GROUP BY dbo_MASTER3_TIMETRACKING.mrID, Replace(Replace([mrSTATUS],"_b"," "),"_",""), DateDiff("d",[mrSUBMITDATE],Now()), Replace(Replace([mrASSIGNEES],"_b"," "),"_",""), PlainText(RemoveHTML([mrDESCRIPTION])), DateDiff("h",[mrSUBMITDATE],Now())

HAVING (((Replace(Replace([mrSTATUS],"_b"," "),"_",""))<>"closed" And (Replace(Replace([mrSTATUS],"_b"," "),"_",""))<>"DELETED") AND ((Min(dbo_MASTER3.mrSUBMITDATE)) Between #1/1/2018# And Now()) AND ((Replace(Replace([mrASSIGNEES],"_b"," "),"_","")) Like "*Basis Admin*") AND ((DateDiff("h",[mrSUBMITDATE],Now()))>47))

ORDER BY Min(dbo_MASTER3.mrSUBMITDATE);


Question:

Lately, however, I have begun receiving a pop-up error when I try to run my query that says Data Type Mismatch occurred. It goes through the process of showing "Running Query" at the bottom of the window, and then the error pops up. The funny thing is that nothing in the query has changed, and when I reopen the database and rerun the report sometimes it goes through just fine. The error is seemingly random and intermittent, but seems to be occurring more frequently.

pullhair.gif

I have read through the articles mentioning an issue with data types mismatching within the tables being joined. However, I haven't found any such issues in the design so far, and if this was true then wouldn't the result be consistent. iconfused.gif Instead, sometimes it runs just fine and sometimes it pops the error.


Anyone have any ideas?

Sincerely,
Kristopher

Attached File(s)
Attached File  Capture20.PNG ( 39.12K )Number of downloads: 6
Attached File  Capture21.PNG ( 26.4K )Number of downloads: 1
 
Go to the top of the page
 
GroverParkGeorge
post Oct 4 2018, 12:59 PM
Post#2


UA Admin
Posts: 33,778
Joined: 20-June 02
From: Newcastle, WA


The first place I always look when something ran fine for a while, but starts to fail, is DATA. Did some data point get into one of the tables in a way that your query filters can process?

Dates, numbers, strings?

Sorting on an invalid data type can cause a similar error.

If you are sure all of your data is proper, then look for something in the query. They can go bad, but much more likely is the data problem.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
KDavidP1987
post Oct 16 2018, 08:33 AM
Post#3



Posts: 67
Joined: 12-June 18



Hey Grover,

I did eventually find out which field was causing the error, but it makes no sense. It is the assignee field, which simply shows the assignee of the ticket (Shown in image). When I remove the filter: Like "*BASIS ADMIN*" from it the query runs. When I try to search for those with BASIS ADMIN listed in the field it pops the data mismatch. This field is not a joined field, I find it a bit off that a text string field being searched for text is causing the error.

So essentially, in the screenshots attached, the only difference is that the assignee field was being filtered to look for open tickets within the BASIS assignee (their queue). Currently, no results exist, but the query should still run and just show blank.

confused.gif
iconfused.gif

Note: (in image) The Replace expression is used because Assignees in the field show up with __b replacing all of the spaces in strings. Had do use replace to get rid of them.

Sincerely,
Kristopher



Attached File(s)
Attached File  Capture20.PNG ( 13.47K )Number of downloads: 0
Attached File  Capture21.PNG ( 22.39K )Number of downloads: 0
Attached File  Capture22.PNG ( 54.56K )Number of downloads: 4
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th October 2018 - 09:03 PM