By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,453 Members | 3,225 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,453 IT Pros & Developers. It's quick & easy.

Another data type mismatch when entering criteria

P: 5
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

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. Property.Name, 
  3. Property.Number, 
  4. Property.CurrentLeaseDate,
  5. (DateAdd('yyyy',[Property].[CurrentReviewPattern],[Property].[CurrentLeaseDate])) AS RenewDate,
  6. DateDiff('yyyy',(DateAdd('yyyy',[Property].[CurrentReviewPattern,[Property].[CurrentLeaseDate])),Date()) AS Years
  7.  
  8. FROM 
  9. Property
  10.  
  11. WHERE (((Property.CurrentReviewPattern)>"1") AND ((Property.CurrentLeaseDate) Is Not Null)) 
  12.  
  13.  
Thanks in advance!

Joy
Sep 21 '06 #1
Share this Question
Share on Google+
8 Replies


PEB
Expert 100+
P: 1,418
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

:)
Sep 21 '06 #2

P: 5
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....
Sep 21 '06 #3

PEB
Expert 100+
P: 1,418
PEB
It gives sometimes a variant expression!

So

the int() function doesn't convert it is it?
Sep 21 '06 #4

P: 5
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?
Sep 21 '06 #5

PEB
Expert 100+
P: 1,418
PEB
try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. Property.Name, 
  3. Property.Number, 
  4. Property.CurrentLeaseDate,
  5. (DateAdd('yyyy',[Property].[CurrentReviewPattern],[Property].[CurrentLeaseDate])) AS RenewDate,
  6. DateDiff('yyyy',(DateAdd('yyyy',[Property].[CurrentReviewPattern],[Property].[CurrentLeaseDate])),Date()) AS Years
  7.  
  8. FROM 
  9. Property
  10.  
  11. WHERE (((Property.CurrentReviewPattern)>1) AND ((Property.CurrentLeaseDate) Is Not Null)) ;
  12.  
  13.  
Not there was an omitted bracket ] in your SQL!

:)
Sep 21 '06 #6

P: 5
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?
Sep 21 '06 #7

PEB
Expert 100+
P: 1,418
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"?

:)
Sep 22 '06 #8

P: 5
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. Property.Name, 
  3. Property.Number, 
  4. Property.CurrentLeaseDate,
  5. Property.CurrentReviewPattern,
  6. (DateAdd('yyyy',[Property].[CurrentReviewPattern],[Property].[CurrentLeaseDate])) AS RenewDate,
  7. DateDiff('yyyy',(DateAdd('yyyy',[Property].[CurrentReviewPattern],[Property].[CurrentLeaseDate])),Date()) AS Years
  8.  
  9. FROM 
  10. Property
  11.  
  12. WHERE (((Property.CurrentReviewPattern)>1) AND 
  13. ((Property.CurrentLeaseDate) Is Not Null)) AND 
  14. ((Int(DateDiff('yyyy',(DateAdd('yyyy',[Property].[CurrentReviewPattern],[Property].[CurrentLeaseDate])),Date())))>1))
  15.  
  16.  
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.
Sep 22 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.