Data Type mismatch in criteria (1 Viewer)

[JaedenRuiner]

Okay,

I sort of need this very quick here, i'm on a deadline, and when there was data in the tables, it all worked fine. However, that data was 'fictional' for the purposes of testing. The client will be entering the real data once the product is delivered.

So i have a report that is based on a query, and on the form there is a button that will display that report, however, if there is nothign to display i want the button to be disabled until there is something to display. NOt too difficult overall.

However, my query uses dates and DateDiff().

Column1: tbl_Asset.PurchaseDate (criteria is not null)
Column2: ReplaceDate: DateValue(Year(Date), Month(tbl_Asset.PurchaseDate), Day(tbl_Asset.PurchaseDate))
Column3: Difference: CInt(DateDiff("yyyy",[PurchaseDate],[ReplaceDate]))

Now, my enabled script is simply:

dim r as boolean
r = (DCount("*", "qry_ReplaceDate") > 0)
if r then
   btn.enabled = (DCount("*", "qry_ReplaceDate", "Difference >= 10") > 0)
else: btn.enabled = r
end if

But the Second test, the one with the "Difference" query doesn't work, it comes up with some Data Type Mismatch in Criteria. What mismatch? If there are no rows in the query which will happen if none of the assets have a purchasedate set, it rusn fine, the initial test comes back 0 and the button is disabled. but if there are rows, i want it to double check that there are assets that are older than 10 years, which is what that datediff() function is supposed to do. So why am i getting a "Type Mismatch" between an Integer and an Integer?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[boblarson]

I think you need to provide the SQL which creates the field DIFFERENCE.

 

[JaedenRuiner]

Meaning what exactly? I mean this is a QUERY, not a table. Can't i do evaluations on calculated columns in a QUERY?

Can you propose an example, because i'm finding myself wishing this was done in SQL server at the moment, because i know how to manipulate that, but the MSSQL is being a little pesky.

Thanks
J"SD"a'RR

 

[boblarson]

Meaning what exactly? I mean this is a QUERY, not a table. Can't i do evaluations on calculated columns in a QUERY?

Can you propose an example, because i'm finding myself wishing this was done in SQL server at the moment, because i know how to manipulate that, but the MSSQL is being a little pesky.

Thanks
J"SD"a'RR

Instead of DateValue try using DateSerial

DateSerial(Year(Date), Month(tbl_Asset.PurchaseDate), Day(tbl_Asset.PurchaseDate))

 

[JaedenRuiner]

yea, my bad in the typing. It is DateSerial, not DateValue.

The issue seems to be that it's attempting to compare the datediff() and DateSerial() on rows that have a NULL PurchaseDate. When NONE of the PurchaseDates are NULL the criteria works fine. But when PurchaseDate is NULL, it comes up with this error. Do I have to IIF() each of the computations that use PUrchaseDate?
J"SD"a'RR

 

[boblarson]

Do I have to IIF() each of the computations that use PUrchaseDate?
J"SD"a'RR

Yep, that would do it.