Another data type mismatch when entering criteria

[mjoytan]

In my real estate access database, I'm trying to create a report that will give me all the properties that are due for a lease renewal this year.

I've been able to use datediff to tell me how many years I have until the renewal date, but for some reason, I cannot select just the ones for this year and next year (where datediff < 1) or even between 0 and 1. I can run the query fine without the criteria, but once I plug any kind of criteria in, I immediately get the data type mismatch.

I'm running Access 2000, on Windows XP SP2

SELECT 
Property.Name, 
Property.Number, 
Property.CurrentLeaseDate,
(DateAdd('yyyy',[Property].[CurrentReviewPattern],[Property].[CurrentLeaseDate])) AS RenewDate,
DateDiff('yyyy',(DateAdd('yyyy',[Property].[CurrentReviewPattern,[Property].[CurrentLeaseDate])),Date()) AS Years
 
FROM 
Property
 
WHERE (((Property.CurrentReviewPattern)>"1") AND ((Property.CurrentLeaseDate) Is Not Null)) 
 
 

Thanks in advance!

Joy

[PEB]

Hi,

So if this field
Property.CurrentReviewPattern is a string /text/ and not number field you will have multiple type conversion errors and your problem wouldn't be in your criteria!

So obviously your field is numeric but you create a mistake in your condition typing >"1"

Try only >1

:)

[mjoytan]

Thank you for the advice, and I actually tried putting 1 without the quotes the first time, but when I run the query, it puts the quotes in for me.

I checked the data type and it is in fact, "number". When I originally wrote the query, I used "is not null" instead of ">1" (sorry about the use of quotes, I'm not actually typing it in the criteria area with the quotes), it gave me a data type mismatch. I was just making sure that the null fields weren't in my report.

I tried to convert the datediff result to an integer, but that didn't seem to do anything.

This is something that was odd, though. When I tried to set a criteria on the resulting field (dateadd("yyyy", [pattern], [leasedate]) , and wanted all records that contained "2006" (like "*2006), I still got a data type mismatch. Do I have to convert the date too? It looks like a date in the resulting dataset. The datediff field seems to come out with the expected result....

[PEB]

It gives sometimes a variant expression!

So

the int() function doesn't convert it is it?

[mjoytan]

I did try the INT() conversion on the Years field, but I still can't use the >1 criteria. I can't use any criteria that I try, > "1", or even "is not null". Nothing.

Should I try the INT() conversion on the renewdate field as well? Or do my Years field calculated differently? Am I using datediff correctly, or do I need to convert int within the expression?

[PEB]

try this:

SELECT 
Property.Name, 
Property.Number, 
Property.CurrentLeaseDate,
(DateAdd('yyyy',[Property].[CurrentReviewPattern],[Property].[CurrentLeaseDate])) AS RenewDate,
DateDiff('yyyy',(DateAdd('yyyy',[Property].[CurrentReviewPattern],[Property].[CurrentLeaseDate])),Date()) AS Years
 
FROM 
Property
 
WHERE (((Property.CurrentReviewPattern)>1) AND ((Property.CurrentLeaseDate) Is Not Null)) ;
 
 

Not there was an omitted bracket ] in your SQL!

:)

[mjoytan]

Hi PEB-

I put the missing bracket back in..must have deleted it when I was copying the query back and forth from textpad.

Still the same error when I try to put criteria in. Is there a different way that I can calculate the # of years from today that the renewal date is coming up? Is datediff the only way to do it?

[PEB]

Hi,

In fact in this cases I delete the functions to see which expression gives me the problem...

So in your case try to delete first the one condition then the second one restoring the previous

And then proceed with the functions...

When you know in which expression is the problem it should be easier to correct it...

Are you sure that it is in the expression with >"1"?

:)

[mjoytan]

I'm sorry, I didn't mean to be confusing. I'm trying run the query where my datediff expression ("Years") is >=1. Access keeps giving me an error, everytime I put it in, which is why it's not in the original statement. It should read:

SELECT 
Property.Name, 
Property.Number, 
Property.CurrentLeaseDate,
Property.CurrentReviewPattern,
(DateAdd('yyyy',[Property].[CurrentReviewPattern],[Property].[CurrentLeaseDate])) AS RenewDate,
DateDiff('yyyy',(DateAdd('yyyy',[Property].[CurrentReviewPattern],[Property].[CurrentLeaseDate])),Date()) AS Years
 
FROM 
Property
 
WHERE (((Property.CurrentReviewPattern)>1) AND 
((Property.CurrentLeaseDate) Is Not Null)) AND 
((Int(DateDiff('yyyy',(DateAdd('yyyy',[Property].[CurrentReviewPattern],[Property].[CurrentLeaseDate])),Date())))>1))
 
 

It is the last part of the where statement that is giving me the problem. Each time I put a criteria in this column, that's when the data type mismatch happens.