Data Type Mismatch for Date

[jsic1210]

Hello,

I'm trying to create a query based on another query. However, my WHERE statement is causing a data type mismatch for a date. My code is:

SELECT id, status, updated, [previous renewal]
FROM qrynext renewal
WHERE status = "active" and [previous renewal] = date()

If I remove the " and [previous renewal] = date()" it works just fine. But even setting up [previous renewal] to equal #7/17/14# or "07/17/14" or anything else I've tried won't work. The field that it's based on is definitely a date field, but I don't know why it would cause a data type mismatch. Any ideas?

Thanks,
Jeff

 

[pr2-eugin]

Try enclosing the rqynext renewal in square brackets.

SELECT [id], [status], [updated], [previous renewal]
FROM [qrynext renewal]
WHERE status = "active" AND [previous renewal] = Date();

 

[jsic1210]

Sorry, I actually typed that wrong. There was not a space in the query name. It is as follows:

SELECT contractid, status, updated, [previous renewal]
FROM qrynextrenewal
WHERE qrynextrenewal.[status]="active" and [previous renewal] = date()

 

[pr2-eugin]

Go to the design view of the table on which the Query - querynextrenewal is based on. Check what is the data type of the field you claim as [previous renewal].

Am I correct in understanding that you are trying this in the Query Builder and not in VBA?

 

[jsic1210]

Yes, I'm using query builder. The format of the original field was set to mm/dd/yyyy. I changed it to Short Date, but that still doesn't work.

 

[plog]

The field that it's based on is definitely a date field,

How is it definitely a date field? Is [previous renewal] an actual date/time field in an underlying table of qrynextrenewal? Or are you calculating it in qrynextrenewal? My guess is somewhere it is calculated by a function that is actually returning a string.

In either case I think this will work::

DateValue([previous renewal]) = date()

Explicitly convert [previous renewal] to a date value in the WHERE clause.

 

[jsic1210]

plog: Yes, [previous renewal] is calculated in the underlying query. I should have specified that. I tried to the datevalue(), but it did not work. Same error. I even tried wrapping datevalue() around [previous renewal] in the underlying query, but to no avail.

 

[pr2-eugin]

Possible to upload a Stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[jsic1210]

I'm having trouble getting it under 2 KB. I stripped it down to all but 2 tables and 2 queries. I stripped the tables down to 10 and 28 records. I even deleted unnecessary fields. There is no code, no objects, no links.

 

[vbaInet]

plog: Yes, [previous renewal] is calculated in the underlying query. I should have specified that. I tried to the datevalue(), but it did not work.

Show us exactly how [previous renewal] is calculated. I bet it ends up being a String.

 

[pr2-eugin]

I'm having trouble getting it under 2 KB. I stripped it down to all but 2 tables and 2 queries. I stripped the tables down to 10 and 28 records. I even deleted unnecessary fields. There is no code, no objects, no links.

Did you follow all the steps I mentioned? Including Compact & Repair? As vbaInet suggests post the calculation.

 

[jsic1210]

It's a little messy...

tblContract.FirstRenewalDate, tblContract.RenewalType, tblContract.RenewalNotice, 
tblContract.RenewalDate, tblContract.RenewalMonths,
Int(DateDiff("m",tblContract.RenewalDate,Date())) AS [Months Since], 
IIf(nz([Months Since],"")="" Or nz([RenewalMonths],"")="",Null,
Int([Months Since]/[RenewalMonths])) AS [Periods Since],
 IIf(nz([renewalmonths],0)=0,0,[months since]/renewalmonths-[periods since])*12 AS [remainder months],
 iif([remainder months] = 0 and day(renewaldate) > day(date()) and [periods since] 
>= 0,0,1) AS [add to], IIf(nz(RenewalDate,"")="" Or nz(RenewalMonths,"")="" 
Or nz([Periods Since],"")="" Or RenewalType="No Renewal",Null,IIf(RenewalDate>Date(),RenewalDate,
DateAdd("m",([Periods Since] + [add to])*
RenewalMonths,RenewalDate))) AS NextRenewal, tblCustomer.Region,
NextRenewal-RenewalNotice AS NoticeDate, DateAdd("m",-[renewalmonths],[nextrenewal]) AS [previous renewal]

 

[jsic1210]

I don't know if this helps, but [previous renewal] is based directly off of two other fields: nextrenewal (date field) and renewalmonths (number field). When I change the criteria to WHERE nextrenewal = #7/1/15# or when I change it to WHERE renewalmonths = 12, it works fine.

 

[plog]

My guess is you're passing the DateAdd function invalid data for what it is expecting. Either nextrenewal is null or renewalmonths is non-numeric.

Since nextrenewal is itself a calculated field, that means you might be passing its DateAdd function invalid data for what it's expecting. Either Periods Since or add to or RenewalDate are not what DateAdd is expecting.

I didn't chase down this house of cards any further, but if any of those fields are calculated themselves, then the functions you are using to build them might suffer from the same issue.

So long story short, previous renewal isn't always a date and you've got a haystack to search through to find out when that occurs.

 

[jsic1210]

@Plog your last post got me thinking. In the underlying query, I used the same iif statements for [previous renewal] as I did for [nextrenewal] (iif x is null...,null,y) and that fixed the problem. Here is what it looks like:

IIf(nz(RenewalDate,"")="" Or nz(RenewalMonths,"")="" Or nz([Periods Since],"")="" Or RenewalType="No Renewal",Null, DateAdd("m",-[current months],[nextrenewal])) AS [previous renewal]

Thank you all for your help!

Jeff

 

[vbaInet]

@Plog your last post got me thinking. In the underlying query, I used the same iif statements for [previous renewal] as I did for [nextrenewal] (iif x is null...,null,y) and that fixed the problem.

One of the things I try to get people to do is what you just spotted. Try and always match both conditions to the same data type and if one of them should not return anything always use Null. Even if you're doing Count(IIF()), use Null in place of 0. Not that it would matter much for the latter but it's more sensible.

By the way, you should be using the IsDate() function for testing the date fields instead of Nz(,"").