Access 2010 generated query throwing, "This expression is typed incorrectly" error 1

[Katerine]

Hi,
I have a problem with, I hope, a simple solution that I'm just not seeing.

I have an Access 2010 database that runs a whole bunch of queries that are generated on the fly in VBA. It's throwing an error, and I can't figure out why. When I debug.print the sql statement that's generated and copy it to a query, the error is, "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

The generated SQL that's throwing the error is as follows:

INSERT INTO REPORTS_Data ( ReportRunID, ReportDesignOptionID, Label, OptionCount )
SELECT 28 AS ReportRunID, 1 AS ReportDesignOptionID, Nz([OptionDescription],[FieldOption]) AS Label, Count(qryREPORT_UniversalQuestionsForCurrentReport.ClientID) AS CountOfClientID
FROM LIST_MiscDropDowns LEFT JOIN qryREPORT_UniversalQuestionsForCurrentReport ON LIST_MiscDropDowns.FieldOption = qryREPORT_UniversalQuestionsForCurrentReport.InitialContact
WHERE (((LIST_MiscDropDowns.FieldName)='Initial Contact') AND ((LIST_MiscDropDowns.IsActive)=True))
GROUP BY 28, 1, Nz([OptionDescription],[FieldOption]), LIST_MiscDropDowns.SortOrder
ORDER BY LIST_MiscDropDowns.SortOrder;

I've tried with both WHERE and HAVING. No joy. I've tried removing each individual column in design view, and no single column appears to be the culprit.

LIST_MiscDropDowns is a table. qryREPORT_UniversalQuestionsForCurrentReport is a query (see below). LIST_MiscDropDowns.FieldOption and qryREPORT_UniversalQuestionsForCurrentReport.InitialContact are both Text fields.

Here's where it gets a little complicated. When the underlying form that's running the code (that generates the above query) is open, qryREPORT_UniversalQuestionsForCurrentReport runs just fine. But just in case, here is the SQL for qryREPORT_UniversalQuestionsForCurrentReport:

SELECT ClientUniversalQuestions.*, Clients.SpecificCounty
FROM (((Clients INNER JOIN ClientUniversalQuestions ON Clients.ClientID = ClientUniversalQuestions.ClientID) INNER JOIN META_ReportGrantType ON Clients.GrantType = META_ReportGrantType.GrantType) INNER JOIN qryREPORT_FilterLocationCriteria ON Clients.ClientID = qryREPORT_FilterLocationCriteria.ClientID) INNER JOIN qryREPORT_FilterOfficeLocation ON Clients.ClientID = qryREPORT_FilterOfficeLocation.ClientID
WHERE (((Clients.DateCreated) Between GetBeginDate() And GetEndDate()));

ClientUniversalQuestions, Clients, and META_ReportGrantType are all tables. InitalContact (which is referenced in the generated query) is a column in the ClientUniversalQuestions table. qryREPORT_FilterLocationCriteria and qryREPORT_FilterOfficeLocation are queries.

Here's the SQL for qryREPORT_FilterLocationCriteria:

SELECT Clients.ClientID
FROM Clients
WHERE (((Clients.OverallCounty)="City of Madison") AND (([Forms]![frmMonthlyReportCriteria]![cboLocationCriteria])="Madison")) OR ((([Forms]![frmMonthlyReportCriteria]![cboLocationCriteria]) Is Null Or ([Forms]![frmMonthlyReportCriteria]![cboLocationCriteria])="All"));

...and here's the SQL for qryREPORT_FilterOfficeLocation:

SELECT Clients.ClientID
FROM Clients
WHERE (((Clients.FromOtherDatabase)=[Forms]![frmMonthlyReportCriteria]![cboOfficeLocCriteria]) AND (([Forms]![frmMonthlyReportCriteria]![cboOfficeLocCriteria]) Is Not Null And ([Forms]![frmMonthlyReportCriteria]![cboOfficeLocCriteria])<>"Main")) OR (((Clients.FromOtherDatabase) Is Null) AND (([Forms]![frmMonthlyReportCriteria]![cboOfficeLocCriteria])="Main")) OR ((([Forms]![frmMonthlyReportCriteria]![cboOfficeLocCriteria]) Is Null Or ([Forms]![frmMonthlyReportCriteria]![cboOfficeLocCriteria])="All"));

I've tried adding parameters to those queries, but that actually makes the problem worse, because when the fields are blank, it returns no rows.

Anybody see anything? Many thanks!

Katie

 

[dhookom]

I would try to set the parameter data types in any query that references a control on a form and possibly the getDate functions. Are these functions defined "As Date"?

Also, the constants "28, 1" shouldn't be needed in the GROUP BY clause.

BTW: thanks for using TGML for your SQL. One little hint is to add carriage returns so we don't have to scroll the code boxes to the right.

Duane
Hook'D on Access
MS Access MVP

 

[Katerine]

Thanks, dhookom,
I've managed to get rid of the previous error by replacing all references to form controls with user-defined functions that are defined as "As String" (to answer your earlier question, the GetBeginDate() and GetEndDate() functions were both defined as "As Date.") However, I'm now running into another issue. I've pinpointed the specific problem, but have no idea how to fix it. Here's the problem, in its simplest form:

The following query works:

SELECT qryREPORT_UniversalQuestionsForCurrentReport.InitialContact
FROM qryREPORT_UniversalQuestionsForCurrentReport;

But this query throws a "Data Type Mismatch in criteria expression" error:

SELECT qryREPORT_UniversalQuestionsForCurrentReport.InitialContact
FROM qryREPORT_UniversalQuestionsForCurrentReport
GROUP BY qryREPORT_UniversalQuestionsForCurrentReport.InitialContact;

InitialContact is a text field. It's never null. I've confirmed that there are two records in the underlying query in my test database, and the value in InitialContact is "Telephone" in both records.

Any ideas?

Thanks again!

Katie

 

[Katerine]

Update: I have tried compacting/repairing the database several times. I've removed all combo-box lookups from the table design, and relinked the tables, and then recreated all 4 queries (the three filter queries and qryREPORT_UniversalQuestionsForCurrentReport) from scratch. Interestingly, the first time I tried to run qryREPORT_UniversalQuestionsForCurrentReport after doing that, it immediately gave me a Data Type Mismatch error, but that went away when I deleted one of the filter queries and then re-added it. None of this affected the result of the above two queries at all - the latter query still gives me a Data Type Mismatch error.

These are the updated queries:
qryREPORT_FilterCounty:

SELECT Clients.ClientID
FROM Clients
WHERE (((Clients.SpecificCounty)=CStr(GetReportCounty())) AND ((CStr(GetReportCounty()))>"")) 
OR (((CStr(GetReportCounty()))=""));

(GetReportCounty() is a function that returns a string variable)

qryREPORT_FilterLocationCriteria:

SELECT Clients.ClientID
FROM Clients
WHERE (((Clients.OverallCounty)="City of Madison") AND ((GetReportLocation())="Madison")) 
OR (((GetReportLocation())="" Or (GetReportLocation())="All"));

(GetReportLocation() is a function that returns a string variable)

qryREPORT_FilterOfficeLocation:

SELECT Clients.ClientID
FROM Clients
WHERE (((Clients.FromOtherDatabase)=CStr(GetReportOffice())) AND ((CStr(GetReportOffice()))>"" And (CStr(GetReportOffice()))<>"Main")) 
OR (((Clients.FromOtherDatabase) Is Null) AND ((CStr(GetReportOffice()))="Main")) 
OR (((CStr(GetReportOffice()))="" Or (CStr(GetReportOffice()))="All"));

(GetReportOffice() is a function that returns a string variable)

qryREPORT_UniversalQuestionsForCurrentReport:

SELECT DISTINCTROW ClientUniversalQuestions.*
FROM (((Clients INNER JOIN ClientUniversalQuestions ON Clients.ClientID = ClientUniversalQuestions.ClientID) 
INNER JOIN qryREPORT_FilterCounty ON Clients.ClientID = qryREPORT_FilterCounty.ClientID) 
INNER JOIN qryREPORT_FilterLocationCriteria ON Clients.ClientID = qryREPORT_FilterLocationCriteria.ClientID) 
INNER JOIN qryREPORT_FilterOfficeLocation ON Clients.ClientID = qryREPORT_FilterOfficeLocation.ClientID
WHERE (((Clients.DateCreated) Between CDate(GetBeginDate()) And CDate(GetEndDate())) 
AND ((Clients.GrantType)=GetGrantType()));

(GetBeginDate() and GetEndDate() both return date variables. GetGrantType() returns a string.)

Many thanks again!

Katie

 

[dhookom]

A datatype mismatch can also be generated from fields used in joins. I would look at each query to make sure the ClientID is always left-aligned (text) or right-aligned (numeric) in datasheet view.

Duane
Hook'D on Access
MS Access MVP

 

[Katerine]

Yep, it's always right-aligned, as it should be, given that all 3 queries get ClientID from the Clients table, and the qryREPORT_UniversalQuestionsForCurrentReport query joins on the Clients table.

The issue is that it's not allowing joins or group by on any field from qryREPORT_UniversalQuestionsForCurrentReport. To give a quick review, this query works:

SELECT qryREPORT_UniversalQuestionsForCurrentReport.InitialContact
FROM qryREPORT_UniversalQuestionsForCurrentReport;

...while this one doesn't:

SELECT qryREPORT_UniversalQuestionsForCurrentReport.InitialContact
FROM qryREPORT_UniversalQuestionsForCurrentReport
GROUP BY qryREPORT_UniversalQuestionsForCurrentReport.InitialContact;

I'm pulling out my hair. There's a deadline on this. Any other ideas? Many thanks for your help thus far.

Katie

 

[Katerine]

I might have found some clue. Still don't know WHY this is happening, but I do know a bit more about WHEN it's happening...

I've changed the qryREPORT_UniversalQuestionsForCurrentReport query and removed the .* (all fields) column, instead adding in each column individually and grabbing ClientID from Clients instead of from ClientUniversalQuestions. So the SQL is now:

SELECT DISTINCTROW Clients.ClientID, ClientUniversalQuestions.InitialContact, ClientUniversalQuestions.Role, 
ClientUniversalQuestions.Age, ClientUniversalQuestions.IsHispanicLatino, ClientUniversalQuestions.Ethnicity, 
ClientUniversalQuestions.Gender, ClientUniversalQuestions.SizeOfHousehold, 
ClientUniversalQuestions.NumberOfChildren, ClientUniversalQuestions.Income, ClientUniversalQuestions.FamilyStatus, 
ClientUniversalQuestions.HomelessStatus, ClientUniversalQuestions.DateCreated
FROM (((Clients INNER JOIN ClientUniversalQuestions ON Clients.ClientID = ClientUniversalQuestions.ClientID) 
INNER JOIN qryREPORT_FilterLocationCriteria ON Clients.ClientID = qryREPORT_FilterLocationCriteria.ClientID) 
INNER JOIN qryREPORT_FilterOfficeLocation ON Clients.ClientID = qryREPORT_FilterOfficeLocation.ClientID) 
INNER JOIN qryREPORT_FilterCounty ON Clients.ClientID = qryREPORT_FilterCounty.ClientID
WHERE (((Clients.DateCreated) Between CDate(GetBeginDate()) And CDate(GetEndDate())) AND ((Clients.GrantType)=GetGrantType()));

Now, this SQL still gives me a Data Type Mismatch error:

SELECT qryREPORT_UniversalQuestionsForCurrentReport.InitialContact
FROM qryREPORT_UniversalQuestionsForCurrentReport
GROUP BY qryREPORT_UniversalQuestionsForCurrentReport.InitialContact;

But this SQL runs fine:!

SELECT qryREPORT_UniversalQuestionsForCurrentReport.ClientID
FROM qryREPORT_UniversalQuestionsForCurrentReport
GROUP BY qryREPORT_UniversalQuestionsForCurrentReport.ClientID;

Also, if I change the SQL for the qryREPORT_UniversalQuestionsForCurrentReport query to instead work like this, it immediately returns a Data Type Mismatch error, not even running the base query:

SELECT DISTINCTROW Clients.ClientID, ClientUniversalQuestions.InitialContact, ClientUniversalQuestions.Role, 
ClientUniversalQuestions.Age, ClientUniversalQuestions.IsHispanicLatino, ClientUniversalQuestions.Ethnicity, 
ClientUniversalQuestions.Gender, ClientUniversalQuestions.SizeOfHousehold, ClientUniversalQuestions.NumberOfChildren, 
ClientUniversalQuestions.Income, ClientUniversalQuestions.FamilyStatus, ClientUniversalQuestions.HomelessStatus, 
ClientUniversalQuestions.DateCreated
FROM (((Clients INNER JOIN ClientUniversalQuestions ON Clients.ClientID = ClientUniversalQuestions.ClientID) 
INNER JOIN qryREPORT_FilterCounty ON ClientUniversalQuestions.ClientID = qryREPORT_FilterCounty.ClientID) 
INNER JOIN qryREPORT_FilterLocationCriteria ON ClientUniversalQuestions.ClientID = qryREPORT_FilterLocationCriteria.ClientID) 
INNER JOIN qryREPORT_FilterOfficeLocation ON ClientUniversalQuestions.ClientID = qryREPORT_FilterOfficeLocation.ClientID
WHERE (((Clients.DateCreated) Between CDate(GetBeginDate()) And CDate(GetEndDate())) AND ((Clients.GrantType)=GetGrantType()));

So... that's... something, at least. I think. I still don't know what's wrong with the ClientUniversalQuestions table that it's doing this to me. I've confirmed that the one and only bit field in that table does not have any null values in it. InitialContact is never Null. I've removed all combo box lookups in the tables yesterday and imported into a fresh database, so I'm pretty sure there isn't a corruption issue.

Clients.ClientID is an AutoNumber field (so, Long Integer). ClientUniversalQuestions.ClientID is a Number field, also Long Integer. Both are the Primary Keys of their respective tables.

One more note that shouldn't make a difference, but I'm including it anyway: I'm running this in a database front-end (with linked tables). Both the front-end and the back-end are .mdb files, specifically Access 2003 databases that I've created in Access 2010.

Any further ideas? Many thanks again.

Katie

 

[dhookom]

Have you attempted a compact and repair on the database?

Duane
Hook'D on Access
MS Access MVP

 

[Katerine]

Many times. Both before and after importing the data into a fresh database and relinking the tables.

Katie

 

[dhookom]

What happens if you begin replacing your functions in the criteria one at a time with constants?

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Hmmm...

What are you trying to get out of this statement:

[pre]
SELECT qryREPORT_UniversalQuestionsForCurrentReport.InitialContact
FROM qryREPORT_UniversalQuestionsForCurrentReport
GROUP BY qryREPORT_UniversalQuestionsForCurrentReport.InitialContact
[/pre]
You are asking for one field (InitialContact) Grouped by the same field.

Is that what you are after:
[tt]
SELECT DISTINCT InitialContact
FROM qryREPORT_UniversalQuestionsForCurrentReport
[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Katerine]

I FOUND IT! Or at least, I assume I found it. The issue was staring at me in the face the whole time!

Clients.OverallCounty, which was filtered as a string in the qryREPORT_FilterLocationCriteria query, is actually an Integer. Don't know why that query works just fine until I try to group on something... theoretically, shouldn't that query refuse to work at all? But never mind. I changed the query to:

SELECT Clients.ClientID
FROM Clients
WHERE (((Clients.OverallCounty)=1) AND ((GetReportLocation())="Madison")) OR (((GetReportLocation())="" Or (GetReportLocation())="All"));

...and the group by works now. Now for the real test (and to answer your question, Andy, the Group By query was really just a simplified version of the problem's symptoms. The actual generated queries (first example below) that I'm really trying to run are much more complex):

INSERT INTO REPORTS_Data ( ReportRunID, ReportDesignOptionID, Label, OptionCount )
SELECT 28 AS ReportRunID, 1 AS ReportDesignOptionID, Nz([OptionDescription],[FieldOption]) AS Label, Count(qryREPORT_UniversalQuestionsForCurrentReport.ClientID) AS CountOfClientID
FROM LIST_MiscDropDowns LEFT JOIN qryREPORT_UniversalQuestionsForCurrentReport ON LIST_MiscDropDowns.FieldOption = qryREPORT_UniversalQuestionsForCurrentReport.InitialContact
WHERE (((LIST_MiscDropDowns.FieldName)='Initial Contact') AND ((LIST_MiscDropDowns.IsActive)=True))
GROUP BY 28, 1, Nz([OptionDescription],[FieldOption]), LIST_MiscDropDowns.SortOrder
ORDER BY LIST_MiscDropDowns.SortOrder;

...and... it works! Yes!

Many thanks, guys. Even though I didn't get around to trying your last suggestion, dhookom, it did point me in the right direction (I tried a group by of just the UniversalQuestions table, without a query, and then added the filter queries one by one).

Katie