Data Type Mismatch in Criteria Expression - query

[sellis]

Ok. I thought what I was doing was really basic. Stupid thing runs too. The information all sits there nice and happy for about two seconds and then the dreaded message pops up and jabs me. I took a basic employee roster with dates of births for employee's and their spouses (where given), and I ran a query to calculate age of the employee and of the spouse. Then off of this query I am running a second query that takes that age and compares it to a table that has the age and a rate that we use to calculate life insurance rates on. One for EE and one for Spouse. Both Ages on both reports are in General number format (I have checked like 6 times). I am lost as to why it is giving me this error except for the fact that I do have null values on the spouse side, but I am going to have null values on the spouse side. Some employee's do not have spouse's. I even removed the spouse information and tried to just compare the EE age to the EE rate, and it would not work - gave me the same error. Below is SQL for the query. Help please.

SELECT [EE Sp Ages].EEID, [EE Sp Ages].[EE AGE], [EE Sp Ages].[SP Age], EE_Life_Rate_Table.Rate, SP_Life_Rate_Table.Rate
FROM SP_Life_Rate_Table RIGHT JOIN (EE_Life_Rate_Table RIGHT JOIN [EE Sp Ages] ON EE_Life_Rate_Table.AGE = [EE Sp Ages].[EE AGE]) ON SP_Life_Rate_Table.AGE = [EE Sp Ages].[SP Age];

 

[xenou]

SELECT 
	[EE Sp Ages].EEID, 
	[EE Sp Ages].[EE AGE], 
	[EE Sp Ages].[SP Age], 
	t1.Rate AS EERate, 
	t2.Rate AS SPRate
FROM 
	(([EE Sp Ages]
	INNER JOIN 
		EE_Life_Rate_Table t1
		ON t1.AGE = [EE Sp Ages].[EE AGE]) 
	INNER JOIN
	SP_Life_Rate_Table t2
		ON t2.AGE = [EE Sp Ages].[SP Age])

BTW I can't recommend Rate as a good field name, since its a the name of an inbuilt vba function to return interest rates.

 

[sellis]

I just bet that is my whole problem right there. I had no idea that was a reserved name. Normally it pops up and tells me, but this time it did not. Thanks I will give this a try and let you know. As always, appreciate the help.

 

[boblarson]

If you do have an Access Reserved Word, you must enclose it in Square Brackets but one that doesn't necessarily work even in square brackets is NAME. Sometimes in square brackets even that one can cause problems. So, in xenou's code you would want to use:

t1.[Rate] AS EERate,
t2.[Rate] AS SPRate
FROM

 

[xenou]

Thanks Bob ... good catch!

 

[sellis]

Ok, so I started over becaused I changed those two titles like you guys suggested, and I simplified the report; however, I am still getting the error. Here is the new sql - I swear the is the easiest query in the world and it is killing me.

SELECT
[EE Sp Ages].EEID,
EE_Life_Rate_Table.[EE LIFE Rate],
SP_Life_Rate_Table.[SP LIFE RATE]
FROM
EE_Life_Rate_Table RIGHT JOIN (SP_Life_Rate_Table RIGHT JOIN [EE Sp Ages]
ON SP_Life_Rate_Table.AGE = [EE Sp Ages].[SP Age])
ON EE_Life_Rate_Table.AGE = [EE Sp Ages].[EE AGE];

Again, any help is appreciated. They do need to be right joins because if they are not then it does not pull the employees with null spouse values. I have discovered this is a problem for me a lot in access 2007.

 

[sellis]

I found it!!! I found another suggestion board that reminded me to sort everything forward and backwards and to look for dodgy stuff, and I found 5 employees without DOBs in their files, so this was causing their ages to error out which was jacking with the entire report. WOOO HOOO! Thanks ya'll.