Type mismatch in expression

R

rinova

Registered User.
Local time
Today, 17:52
Joined
Aug 27, 2012
Messages
74
Hi Everyone,
I need some help with an error I'm receiving when I try to setup a relation between the results of a IIf statement and a table.
The IIf statement produces result codes is here:
Code: 
DISCREPANCY CODE: IIf([STATUS]="I" And [CLAIM STATUS]="PAID CLAIM","1",IIf([STATUS]="I" And [CLAIM STATUS]="PARTIAL PAID","2",IIf([STATUS]="I" And [DOS]>[TERM DATE] And [CLAIM STATUS]="PAID CLAIM","3",IIf([STATUS]="I" And [DOS]>[TERM DATE] And [CLAIM STATUS]="PARTIAL PAID","4",""))))
but when I try to use a table that contains the descriptions to the codes I get a "Type mismatch in expression"
How can I use the codes in the IIf statements to display the description field in the table.

Code that contains the IIf statement:
Code: 
SELECT DISTINCT ALLGHIUTILIZATIONRPTS.Year, ALLGHIUTILIZATIONRPTS.Month, Format([ALLGHIUTILIZATIONRPTS]![CERT-ID],"000000000") AS SSN, ALLGHIUTILIZATIONRPTS.[PATIENT-NAME] AS [MEMBER NAME], codes_coverage.cov_type AS [COV CODE], ALLGHIUTILIZATIONRPTS.[BIRTH DATE], ALLGHIUTILIZATIONRPTS.REL AS RELATION, ALLGHIUTILIZATIONRPTS.INCUR AS DOS, dbo_elg205.ps_welfare_status AS STATUS, dbo_elg205.ps_term_date AS [TERM DATE], ALLGHIUTILIZATIONRPTS.[CLAIM STATUS], IIf([STATUS]="I" And [CLAIM STATUS]="PAID CLAIM","1",IIf([STATUS]="I" And [CLAIM STATUS]="PARTIAL PAID","2",IIf([STATUS]="I" And [DOS]>[TERM DATE] And [CLAIM STATUS]="PAID CLAIM","3",IIf([STATUS]="I" And [DOS]>[TERM DATE] And [CLAIM STATUS]="PARTIAL PAID","4","")))) AS [DISCREPANCY CODE]
FROM codes_coverage INNER JOIN ((ALLGHIUTILIZATIONRPTS INNER JOIN qryMostRecentDate ON (ALLGHIUTILIZATIONRPTS.[CERT-ID] = qryMostRecentDate.[CERT-ID]) AND (ALLGHIUTILIZATIONRPTS.[INCUR] = qryMostRecentDate.[INCUR])) INNER JOIN dbo_elg205 ON (qryMostRecentDate.[CERT-ID] = dbo_elg205.[ps_ss_nbr]) AND (qryMostRecentDate.[MaxVanSTATUS DATE] = dbo_elg205.[ps_date])) ON codes_coverage.[cov_code] = dbo_elg205.[ps_cov_type]
WHERE (((ALLGHIUTILIZATIONRPTS.Year)=[Enter Year]) AND ((ALLGHIUTILIZATIONRPTS.Month)=[Enter Month]) AND ((ALLGHIUTILIZATIONRPTS.REL)="M"));
Here is the code that I used to add the table but it produces the error message:

Code: 
SELECT qryGHIMBRUTILIZATIONRPT.*, tblGHI_DISCREPANCY_CODES.discrepancy_description
FROM tblGHI_DISCREPANCY_CODES INNER JOIN qryGHIMBRUTILIZATIONRPT ON tblGHI_DISCREPANCY_CODES.discrepancy_code = qryGHIMBRUTILIZATIONRPT.[DISCREPANCY CODE];
The result should include the other data as well as the descriptions to the codes.

I created a table with the matching codes and descriptions and it looks like this:

ID, discrepancy_code, discrepancy_description
1, 1, Member inactive. Claim should not be paid.
2, 2, Member inactive. Claim should not be partial paid.
3, 3, Member inactive terminated prior to DOS. Claim should not be paid.
4, 4, Member inactive terminated prior to DOS. Claim should not be partial paid.

Any help would be appreciated.

Thank you,

Rich
 
Get the if statement working by itself in its own query first before you add it to the main query.
 
You can convert this to a function instead.
rinova said:
Code: 
DISCREPANCY CODE: IIf([STATUS]="I" And [CLAIM STATUS]="PAID CLAIM","1",IIf([STATUS]="I" And [CLAIM STATUS]="PARTIAL PAID","2",IIf([STATUS]="I" And [DOS]>[TERM DATE] And [CLAIM STATUS]="PAID CLAIM","3",IIf([STATUS]="I" And [DOS]>[TERM DATE] And [CLAIM STATUS]="PARTIAL PAID","4",""))))
Click to expand...
 
@Uncle Gizmo
The iif statement is working by itself. When the expression is true it produces a number code that I am trying to use with a table.
Example:

Say I have this iif statement
Light_Code:If([Field1]="Red","1",IIf([Field1]="Green","2","Caution"))

My table looks like this
ID, CODE, DESCRIPTION
1, 1, STOP
2, 2, GO

I'm trying to use the table to create a new field called Description and insert/display the descriptions the matches the codes the iif statement produces but I keep getting "Type mismatch in expression"

Why? and how can I fix it?

Thanks,
Richard
 
@vbaInet

Thank you for all your help. How will converting this to a function fix my problem?

Regards,

Rich
 
It would be easier to read and error check:
Code: 
IIf([STATUS]="I" And [CLAIM STATUS]="PAID CLAIM","1",
	IIf([STATUS]="I" And [CLAIM STATUS]="PARTIAL PAID","2",
		IIf([STATUS]="I" And [DOS]>[TERM DATE] And [CLAIM STATUS]="PAID CLAIM","3",
			IIf([STATUS]="I" And [DOS]>[TERM DATE] And [CLAIM STATUS]="PARTIAL PAID","4",""))))
If you remove the IIF() expression does it run?

By the way, you're using reserved keywords (i.e. Month, Year) which can be problematic
 
I'm importing an Excel File into Access from a vendor and they use the name MONTH and YEAR in the Excel File. I will change them after or before I import the file going forward. Thanks for the advice.
 
No point changing it if that's what's being used. Just remember to enclose it in square brackets to avoid any mix up.

So what happens if you remove the IIF() expression?
 
I removed the " " surrounding the numbers and it didn't work. I then changed the numbers to letters and it worked but it only seem to use the first two iif statements and didn't recognize the last two.

I then did what you suggested and removed the iif statements. This is the code I used:

Code: 
SELECT qryGHIMBRUTILIZATIONRPT.*
FROM qryGHIMBRUTILIZATIONRPT
WHERE (((qryGHIMBRUTILIZATIONRPT.STATUS)="I") AND ((qryGHIMBRUTILIZATIONRPT.[CLAIM STATUS])="PAID CLAIM")) OR (((qryGHIMBRUTILIZATIONRPT.STATUS)="I") AND ((qryGHIMBRUTILIZATIONRPT.[CLAIM STATUS])="PARTIAL PAID")) OR (((qryGHIMBRUTILIZATIONRPT.STATUS)="I") AND ((qryGHIMBRUTILIZATIONRPT.[CLAIM STATUS])="PAID CLAIM") AND ((qryGHIMBRUTILIZATIONRPT.DOS)>[term date])) OR (((qryGHIMBRUTILIZATIONRPT.STATUS)="I") AND ((qryGHIMBRUTILIZATIONRPT.[CLAIM STATUS])="PARTIAL PAID") AND ((qryGHIMBRUTILIZATIONRPT.DOS)>[term date]));
It looks like it's not acknowledging the last two WHERE statements.

It appears to make a match with the first WHERE statement and disregards the others even though the criteria matches better.

Thanks again,

Rich
 
What I asked you to do was remove IIF() expression completely to test whether you still get the error. If you do then the error lies somewhere else, if you don't then the error is in the IIF() function.
 
Sorry I misunderstood you, I thought I did what you asked by not using the iif statement in the query. I'm obviously not doing this correctly.

Sorry I hate to admit this but I don't know access that well to do what your asking.

How do I remove the iif statement but keep the rest of it and get it to work in the query?

Thanks,
Rich :(
 
It's an expression you wrote in the query right? Remove it from the query itself and re-open the query in datasheet to see if it errors. Do this on the query you initially posted.
 
I removed the iif statements from the query and it ran without errors

Here is the code after I removed the iif statements

Code: 
SELECT DISTINCT ALLGHIUTILIZATIONRPTS.Year, ALLGHIUTILIZATIONRPTS.Month, Format([ALLGHIUTILIZATIONRPTS]![CERT-ID],"000000000") AS SSN, ALLGHIUTILIZATIONRPTS.[PATIENT-NAME] AS [MEMBER NAME], codes_coverage.cov_type AS [COV CODE], ALLGHIUTILIZATIONRPTS.REL AS RELATION, ALLGHIUTILIZATIONRPTS.INCUR AS DOS, DateDiff("yyyy",[ALLGHIUTILIZATIONRPTS]![BIRTH DATE],[DOS])+Int(Format([DOS],"mmdd")<Format([ALLGHIUTILIZATIONRPTS]![BIRTH DATE],"mmdd")) AS [Age at DOS], dbo_elg205.ps_welfare_status AS STATUS, dbo_elg205.ps_term_date AS [TERM DATE], ALLGHIUTILIZATIONRPTS.[BIRTH DATE], ALLGHIUTILIZATIONRPTS.[CLAIM STATUS]
FROM codes_coverage INNER JOIN ((ALLGHIUTILIZATIONRPTS INNER JOIN qryMostRecentDate ON (ALLGHIUTILIZATIONRPTS.[CERT-ID] = qryMostRecentDate.[CERT-ID]) AND (ALLGHIUTILIZATIONRPTS.[INCUR] = qryMostRecentDate.[INCUR])) INNER JOIN dbo_elg205 ON (qryMostRecentDate.[CERT-ID] = dbo_elg205.[ps_ss_nbr]) AND (qryMostRecentDate.[MaxVanSTATUS DATE] = dbo_elg205.[ps_date])) ON codes_coverage.[cov_code] = dbo_elg205.[ps_cov_type]
WHERE (((ALLGHIUTILIZATIONRPTS.Year)=[Enter Year]) AND ((ALLGHIUTILIZATIONRPTS.Month)=[Enter Month]) AND ((ALLGHIUTILIZATIONRPTS.REL)="M"));
Should I put back the iif statements one by one and re-test query for errors?

Thank you,
Rich
 
Yes but you can see why I said earlier that you should convert it to a function instead? It would be easier to spot errors.
 
I'll work on learning how to make it a function now. Need to do some reading to learn how. Thanks
 
Ah, I thought you knew how. I've re-written your IIF() function so try these two. Hopefully there are no syntax errors:
Code: 
IIf([STATUS]<>"I", Null,  
	IIf([CLAIM STATUS]="PAID CLAIM" And [DOS]>[TERM DATE], 3, 
		IIf([CLAIM STATUS]="PARTIAL PAID" And [DOS]>[TERM DATE], 4, 
			IIF([CLAIM STATUS]="PAID CLAIM", 1,
				IIf([CLAIM STATUS]="PARTIAL PAID", 2, Null)))))


IIf([STATUS]<>"I", Null,  
	IIf([CLAIM STATUS]="PAID CLAIM" And [DOS]>[TERM DATE], 3, 
		IIf([CLAIM STATUS]="PARTIAL PAID" And [DOS]>[TERM DATE], 4, 
			Switch([CLAIM STATUS]="PAID CLAIM", 1, [CLAIM STATUS]="PARTIAL PAID", 2))))
 
@vbaInet

Thank you for all your help I was able to modify your code to this (See Below) and it works great. I'm not sure it is the best way to do what I'm trying to do but it works for me. Thank you again.

Code: 
Switch(
[DOS]>[dbo_depfile]![dep_term_date] And [CLAIM STATUS]="PARTIAL PAID",2,
[DOS]>[dbo_depfile]![dep_term_date] And [CLAIM STATUS]="PAID CLAIM",2,
[DOS]>[dbo_elg205]![ps_term_date] And [CLAIM STATUS]="PARTIAL PAID" And [dbo_elg205]![ps_welfare_status]<>"E",1,
[DOS]>[dbo_elg205]![ps_term_date] And [CLAIM STATUS]="PAID CLAIM" And [dbo_elg205]![ps_welfare_status]<>"E",1,
[CLAIM STATUS]="DENIED CLAIM" And [MBR TERM DATE] is null And [DEP TERM DATE] is null And [MBR STATUS]="E",8,
[GHIUTILIZATIONRPTS]![REL]="D" And [Age at DOS]>23 And [CLAIM STATUS]="PARTIAL PAID",3,
[GHIUTILIZATIONRPTS]![REL]="D" And [Age at DOS]>23 And [CLAIM STATUS]="PAID CLAIM  ",3,
[COV CODE] ="MAS" And [RELATION] ="D" And [CLAIM STATUS] ="PAID CLAIM",4,
[COV CODE] ="MAC" And [RELATION] ="S" And [CLAIM STATUS] ="PARTIAL PAID",5,
[COV CODE] ="SGL" And [RELATION] ="D" And [CLAIM STATUS] ="PAID CLAIM",6,
[COV CODE] ="SGL" And [RELATION] ="S" And [CLAIM STATUS] ="PAID CLAIM",6,
[COV CODE] ="SGL" And [RELATION] ="D" And [CLAIM STATUS] ="PARTIAL PAID",6,
[COV CODE] ="SGL" And [RELATION] ="S" And [CLAIM STATUS] ="PARTIAL PAID",6,) AS DISCREPANCY_CODE
 
Good job but that's a pretty intense SWITCH statement. It's time for you to do it in a user-defined function and it will execute faster.
 
Last post on this item:

Do you know any sites that I can learn how to create a user-defined function?

Thanks,

Rich
 
You must log in or register to reply here.

Similar threads

N
Chart Error: Expression is typed incorrectly, or is too complex to be evaluated
Replies
8
Views
1,339
Pat Hartman
P
R
Solved Type Mismatch in Expression / Invalid Procedure Call
Replies
7
Views
958
rodski
R
adhoustonj
Date between in query is causing error "expression is typed incorrectly, or it is too complex to be evaluated"
Replies
3
Views
754
The_Doc_Man
The_Doc_Man
R
Dont prompt if only one shipping adress
Replies
1
Views
579
Gasman
Gasman
pbuethe
Solved Data Type Mismatch in Criteria Expression
2
Replies
20
Views
3,321
pbuethe
pbuethe

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom