INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

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

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:

CODE --> sql

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:

CODE --> sql

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:

CODE --> sql

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:

CODE --> sql

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

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

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

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

(OP)
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:

CODE --> sql

SELECT qryREPORT_UniversalQuestionsForCurrentReport.InitialContact
FROM qryREPORT_UniversalQuestionsForCurrentReport; 

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

CODE --> sql

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

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

(OP)
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:

CODE --> sql

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:

CODE --> sql

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:

CODE --> sql

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:

CODE --> sql

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

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

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

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

(OP)
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:

CODE --> sql

SELECT qryREPORT_UniversalQuestionsForCurrentReport.InitialContact
FROM qryREPORT_UniversalQuestionsForCurrentReport; 

...while this one doesn't:

CODE --> sql

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. pc

Katie

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

(OP)
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:

CODE --> sql

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:

CODE --> sql

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

But this SQL runs fine:!

CODE --> sql

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:

CODE --> sql

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. smile

Katie

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

Have you attempted a compact and repair on the database?

Duane
Hook'D on Access
MS Access MVP

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

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

Katie

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

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

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

Hmmm...

What are you trying to get out of this statement:

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

Is that what you are after:

SELECT DISTINCT InitialContact
FROM qryREPORT_UniversalQuestionsForCurrentReport

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.

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

(OP)
I FOUND IT! smile 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:

CODE --> sql

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):

CODE --> sql

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! smile

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close