CODEWITHSUNDEEP
sqlms-accessvbams-access-2013having-clause
Maxs728
Maxs728

Reputation: 601

MS Access Having Clause

Alright so I understand the point of the HAVING clause. I am having an issue and I am wondering if I can solve this the way I want to.

I want to execute one query using ADODB.Recordset and then use the Filter function to sift through the data set.

The problem is the query at the moment which looks like this:

SELECT tblMT.Folder, tblMT.MTDATE, tblMT.Cust, Sum(tblMT.Hours) 
FROM tblMT
GROUP BY tblMT.Folder, tblMT.MTDATE, tblMT.Cust
HAVING tblMT.Cust LIKE "TEST*" AND Min(tblMT.MTDATE)>=Date()-30 AND MAX(tblMT.MTDATE)<=Date()
ORDER BY tblMT.TheDATE DESC;

So the above works as expected.... however I want to be able to use the tblMT.Cust as the filter without having to keep re querying the database. If I remove it I get a:

Data type mismatch in criteria expression.

Is what I am trying to do possible? If someone can point me in the right direction here would be great.

Upvotes: 0

Views: 327

Answers (1)

xQbert
xQbert

Reputation: 35323

Ok... the type mismatch is caused because either tblmt.mtdate isn't a date field or tblmt.hours isn't a number field AND you have data that either isn't a date or isn't a number when the customer isn't like 'TEST*'. Or, for some customers, you have a NULL in mt.date and null can't be compared with >=. you'd still get the error if you said where tblMt.cust not like "TEST*" too.

Problem is likely with the data or your expectation and you need to handle it.

What data types are tblMT.hours and tblMt.MtDate?

Upvotes: 1

Related Questions