Mystery Mismatch in Data Type

[KelleyM]

I've got a frustrating little problem I was hoping to get some help with. I have querry that I want to select either



1. all of an individuals claims if we have never asked for reimbursement or
2. just the claims paid since the begining of the month otherwise.

Here is the query with the critria like I want them.

SELECT tblAllClaims.*, [Adj Worksheet qryDB1.zip].RNAME, tblAllClaims.Paiddt
FROM [Adj Worksheet qry] INNER JOIN tblAllClaims ON ([Adj Worksheet qry].REINS_YEAR = tblAllClaims.RYEAR) AND ([Adj Worksheet qry].MEMBER = tblAllClaims.MEMID) AND ([Adj Worksheet qry].CONVERTED_CONTRACT = tblAllClaims.CONVERTED_CONTRACT)
WHERE ((([Adj Worksheet qry].SumOfCarrierPaid)>=[Stoploss] And ([Adj Worksheet qry].SumOfCarrierPaid) Is Null) AND (([ShockTotal]-Nz([SumOfCarrierPaid]))<>0)) OR ((([ShockTotal]-Nz([SumOfCarrierPaid]))<>0) AND (([Adj Worksheet qry].ShockTotal)>=[Stoploss]) AND ((tblAllClaims.Paiddt)>=20120801)) OR ((([Adj Worksheet qry].SumOfCarrierPaid)>=[Stoploss] And ([Adj Worksheet qry].SumOfCarrierPaid) Is Null) AND (([ShockTotal]-Nz([SumOfCarrierPaid]))<>0)) OR ((([ShockTotal]-Nz([SumOfCarrierPaid]))<>0) AND (([Adj Worksheet qry].ShockTotal)>=[Stoploss]) AND ((tblAllClaims.Paiddt)>=20120801));

This triggers a datatype mismatch error.

So, I tried to narrow down what was triggering that error.

This, with no criteria on PAIDDT, ran fine.

SELECT tblAllClaims.*, [Adj Worksheet qry].RNAME
FROM [Adj Worksheet qry] INNER JOIN tblAllClaims ON ([Adj Worksheet qry].REINS_YEAR = tblAllClaims.RYEAR) AND ([Adj Worksheet qry].MEMBER = tblAllClaims.MEMID) AND ([Adj Worksheet qry].CONVERTED_CONTRACT = tblAllClaims.CONVERTED_CONTRACT)
WHERE ((([ShockTotal]-Nz([SumOfCarrierPaid]))<>0) AND (([Adj Worksheet qry].SumOfCarrierPaid)>=[Stoploss] And ([Adj Worksheet qry].SumOfCarrierPaid) Is Null)) OR ((([ShockTotal]-Nz([SumOfCarrierPaid]))<>0) AND (([Adj Worksheet qry].ShockTotal)>=[Stoploss])) OR ((([ShockTotal]-Nz([SumOfCarrierPaid]))<>0) AND (([Adj Worksheet qry].SumOfCarrierPaid)>=[Stoploss] And ([Adj Worksheet qry].SumOfCarrierPaid) Is Null)) OR ((([ShockTotal]-Nz([SumOfCarrierPaid]))<>0) AND (([Adj Worksheet qry].ShockTotal)>=[Stoploss]));

So, I think PAIDDT must be the problem. But this, with just the criteria on PAIDDT, also runs fine.

SELECT tblAllClaims.*, [Adj Worksheet qry].RNAME, tblAllClaims.PAIDDT
FROM [Adj Worksheet qry] INNER JOIN tblAllClaims ON ([Adj Worksheet qry].REINS_YEAR = tblAllClaims.RYEAR) AND ([Adj Worksheet qry].MEMBER = tblAllClaims.MEMID) AND ([Adj Worksheet qry].CONVERTED_CONTRACT = tblAllClaims.CONVERTED_CONTRACT)
WHERE (((tblAllClaims.PAIDDT)>=20120801));

To add to the general confusion. I made a clean db with just the table structures and relevent querries (DB1.zip) and my querry works fine in there.

AH!

I have no idea what is going on. Please help!

[ssanfu]

My first guess would be the criteria is not delimited correctly. In your WHERE clause you have

WHERE (((tblAllClaims.PAIDDT)>=20120801));

What is the field type of "PAIDDT"?
If it is a Date/Time type, it needs to be delimited with hash marks (#).
If it is a Text type, it needs to be delimited with quotes.


It looks like 20120801 is yyyymmdd (ie 08/01/2012), so I would guess that the field is a text type.

[KelleyM]

PAIDDT is actually not a Data/Time type. It is a number type double.

I was trying to explain, the PAIDDT critiria works by itself but not when combined with the critiria for the Adj Worksheet query.

Thank you for the response.

[ssanfu]

OK,

I re-arranged your query for ease of reading and it looks like you have duplicated the criteria in the WHERE clause. (or Access did ).

SELECT tblAllClaims.*, [Adj Worksheet qryDB1.zip].RNAME, tblAllClaims.Paiddt

FROM 
[Adj Worksheet qry] INNER JOIN tblAllClaims ON 
    ([Adj Worksheet qry].REINS_YEAR = tblAllClaims.RYEAR) 
AND ([Adj Worksheet qry].MEMBER = tblAllClaims.MEMID) 
AND ([Adj Worksheet qry].CONVERTED_CONTRACT = tblAllClaims.CONVERTED_CONTRACT)

WHERE 
   ((([Adj Worksheet qry].SumOfCarrierPaid)>=[Stoploss] And ([Adj Worksheet qry].SumOfCarrierPaid) Is Null) AND (([ShockTotal]-Nz([SumOfCarrierPaid]))<>0))
OR ((([Adj Worksheet qry].SumOfCarrierPaid)>=[Stoploss] And ([Adj Worksheet qry].SumOfCarrierPaid) Is Null) AND (([ShockTotal]-Nz([SumOfCarrierPaid]))<>0)) 

OR ((([ShockTotal]-Nz([SumOfCarrierPaid]))<>0) AND (([Adj Worksheet qry].ShockTotal)>=[Stoploss]) AND ((tblAllClaims.Paiddt)>=20120801)) 
OR ((([ShockTotal]-Nz([SumOfCarrierPaid]))<>0) AND (([Adj Worksheet qry].ShockTotal)>=[Stoploss]) AND ((tblAllClaims.Paiddt)>=20120801));

Any-hoo, I would start out with the basic select

SELECT tblAllClaims.*, [Adj Worksheet qryDB1.zip].RNAME, tblAllClaims.Paiddt

FROM 
[Adj Worksheet qry] INNER JOIN tblAllClaims ON 
    ([Adj Worksheet qry].REINS_YEAR = tblAllClaims.RYEAR) 
AND ([Adj Worksheet qry].MEMBER = tblAllClaims.MEMID) 
AND ([Adj Worksheet qry].CONVERTED_CONTRACT = tblAllClaims.CONVERTED_CONTRACT)

then start adding in the criteria one at a time until you get the error.