MS Access Having Clause

Question

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.

Ok... the type mismatch is caused becuase 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? — xQbert, Jun 05 '17 at 16:24
Thanks for the insight.. I know there are data anomalies within the table so I can check for the nulls and see what happens. This is an inherited project and I have been slowly normalizing the data. — Maxs728, Jun 05 '17 at 17:15
@xQbert asked a very important question at the end of his comment which you do not answer in response. Please do. — Parfait, Jun 05 '17 at 17:47
MTDate is Date/Time and Hours is Short Text. I am now going through correcting the Short Text and changing it to number value. — Maxs728, Jun 06 '17 at 13:18
@xQbert if you could post your response as an answer. The problem was in fact the Hours being a Short Text. Works flawlessly now. — Maxs728, Jun 06 '17 at 19:01

score 1 · Accepted Answer · answered Jun 06 '17 at 19:18

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?

MS Access Having Clause

1 Answers1