Data Type Mismatch in criteria expression

[Mike Hughes]

When I try running this query I get
"Data Type Mismatch in criteria expression"

This is a query I had originaly written for a program called "Query Reporter"
I'm trying to move it over to Access

Any ideas?


Select
b.worker_id,
iv_d_do_code,
a.id_payor,
b.case_id,
a.dt_batch,
a.cd_source_batch,
a.no_batch,
a.seq_receipt,
cd_reason_status,
sum(a.amt_to_distribute) AS Held_amt

From
noldba_receipt a,
noldba_int_case_status b

Where A.Cd_Status_Receipt = 'H'
And iv_d_do_code IN('KK','LL','MM','NN')
AND a.cd_source_batch<>'I'
And A.Dt_Distribute = '12/31/9999'
And A.Dt_End_Validity = '12/31/9999'
AND Not Exists (Select 1 from noldba_receipt k
where a.dt_batch = k.dt_batch
ANd a.no_batch = k.no_batch
And a.cd_source_batch = k.cd_source_batch
And a.seq_receipt = k.seq_receipt
And k.ind_backout = 'Y'
And k.dt_end_validity = '12/31/9999')
And ((trim(A.Id_Case) IS NOT NULL
AND A.Id_case = b.case_id )
Or
( trim(A.Id_Case) IS NULL
ANd b.case_id = (Select Min(y.case_id) from noldba_int_case_status Y, noldba_int_case_member Z
Where A.Id_Payor = z.member_id
And z.relation_code IN ('A','P')
And z.case_id = y.case_id )
))
group by b.worker_id,iv_d_do_code,a.id_payor,b.case_id,a.dt_batch,a.cd_source_batch,
a.no_batch,a.seq_receipt,cd_reason_status
order by iv_d_do_code, b.worker_id

 

[Galaxiom]

Access SQL requires dates as #mm/dd/yyyy#. (Single quotes are used in MS SQL Server)

The Date 12/31/9999 is presumably a placeholder for no date in the original tool. This concept is not supported in Access which would treat it as a real date.

 

[Mike Hughes]

I tried this and it ran but I got no results and I know I should have them. (When I just ran it in Query Reporter I got results)

SELECT b.worker_id, iv_d_do_code, a.id_payor, b.case_id, a.dt_batch, a.cd_source_batch, a.no_batch, a.seq_receipt, cd_reason_status, sum(a.amt_to_distribute) AS Held_amt
FROM noldba_receipt AS a, noldba_int_case_status AS b
WHERE A.Cd_Status_Receipt = 'H'
And iv_d_do_code IN('KK','LL','MM','NN')
AND a.cd_source_batch<>'I'
And A.Dt_Distribute = #12/31/9999#
And A.Dt_End_Validity = #12/31/9999#
AND Not Exists (Select 1 from noldba_receipt k
where a.dt_batch = k.dt_batch
ANd a.no_batch = k.no_batch
And a.cd_source_batch = k.cd_source_batch
And a.seq_receipt = k.seq_receipt
And k.ind_backout = 'Y'
And k.dt_end_validity = #12/31/9999#)
And ((trim(A.Id_Case) IS NOT NULL
AND A.Id_case = b.case_id )
Or
( trim(A.Id_Case) IS NULL
ANd b.case_id = (Select Min(y.case_id) from noldba_int_case_status Y, noldba_int_case_member Z
Where A.Id_Payor = z.member_id
And z.relation_code IN ('A','P')
And z.case_id = y.case_id )
))
GROUP BY b.worker_id, iv_d_do_code, a.id_payor, b.case_id, a.dt_batch, a.cd_source_batch, a.no_batch, a.seq_receipt, cd_reason_status
ORDER BY iv_d_do_code, b.worker_id;

 

[Galaxiom]

This line is could be an issue:
( trim(A.Id_Case) IS NULL

It would only be Null is the field is Null. Trimming a string of spaces would return a Zero Length String which is not a Null.

BTW you are using s Cartesian product of the tables with conditions. The query would work better with the inter-table conditions expressed as Joins.