"type mismatch in expression" error

[Ashank4vba]

Hello,

I cannot figure out why I keep getting the error "Type mismatch in expression" every time I try to execute/open the following query in design view.

Here is the query

SELECT LRQC_ISSUES_UNION.*, LRQC_ISSUE_STATUS.ISSUE_STATUS
FROM LRQC_ISSUES_UNION INNER JOIN LRQC_ISSUE_STATUS ON LRQC_ISSUES_UNION.ISSUE_NO=LRQC_ISSUE_STATUS.ISSUE_NO
WHERE (((LRQC_ISSUE_STATUS.ISSUE_STATUS)="Open") AND ((LRQC_ISSUES_UNION.PROBLEM_RESOLVED_DT) Is Null Or (LRQC_ISSUES_UNION.PROBLEM_RESOLVED_DT)=""));

The access help files say that the error message is due to mismatch types in the join expression.
Both LRQC_ISSUES_UNION and LRQC_ISSUE_STATUS are queries.
I am able to open both of them in design view without any error.
The ISSUE_NO fields in both queries 'should be strings'.
The 'union' query assigns values to ISSUE_NO as follows - ISSUE_NO:"<some number here>" while the other 'STATUS' query uses this 'ISSUE_NO: CStr([ISSUE_NUMBER])'
So as far as my understanding goes, both are strings rt?
Also, the guy who gave me the query to analyze said this (I cant make sense of this too)

Since I have imported all tables from Oracle, some queries might require you to change the DATA_TYPE....feel free to make appropriate changes to make it work.

Please please help .

-cheers
Ashank

 

[PHV]

If the tested field is a DateTime field you have a problem here:
(LRQC_ISSUES_UNION.PROBLEM_RESOLVED_DT)=""

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ashank4vba]

Thanks PHV!
That indeed was the problem.
Im not sure about the solution though.
I changed that line to

(LRQC_ISSUES_UNION.PROBLEM_RESOLVED_DT) Is Null

but i still got the errror.

So I changed it to,

(LRQC_ISSUES_UNION.PROBLEM_RESOLVED_DT) Is Empty

and now I dont get the error but I dont know if it will work.

 

[Ashank4vba]

I dont think it works