data type mismatch

[scubadiver]

Hello,

I want to list some specific queries in another query. The queries that I
want are prefixed by a 99 but when put 99 in the criteria of the first column
I get a "data type mismatch in criteria expression" error

any ideas?

SELECT Left([Name],2) AS prefix, Mid([Name],InStr([Name]," ")+1) AS QryName
FROM MsysObjects
WHERE (((MsysObjects.Type)=5) AND ((Left$([Name],1))<>"~"))
ORDER BY Left([Name],2);

 

[J_Goddard via AccessMonster.com]

Since the name of a query is text, try using "99" (text) in the criteria
instead of 99 (numeric).

John

Hello,

I want to list some specific queries in another query. The queries that I
want are prefixed by a 99 but when put 99 in the criteria of the first column
I get a "data type mismatch in criteria expression" error

any ideas?

SELECT Left([Name],2) AS prefix, Mid([Name],InStr([Name]," ")+1) AS QryName
FROM MsysObjects
WHERE (((MsysObjects.Type)=5) AND ((Left$([Name],1))<>"~"))
ORDER BY Left([Name],2);

 

[John Spencer]

How about posting the SQL of what you have tried and failed. As a guess
you didn't add quote marks around the 99. But that could be completely
wrong since we don't know what you did.

SELECT Left([Name],2) AS prefix, Mid([Name],InStr([Name]," ")+1) AS QryName
FROM MsysObjects
WHERE MsysObjects.Type=5 AND Left([Name],1)="99"
ORDER BY Left([Name],2);


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

I want to list some specific queries in another query. The queries that I
want are prefixed by a 99 but when put 99 in the criteria of the first column
I get a "data type mismatch in criteria expression" error

Just a plain 99 would be appropriate for a number field, but you have a Text
field. Try instead:

SELECT Left([Name],2) AS prefix, Mid([Name],InStr([Name]," ")+1) AS QryName
FROM MsysObjects
WHERE (((MsysObjects.Type)=5) AND
AND [Name] LIKE "99*"
ORDER BY Left([Name],2);

You don't need to check for the ~ temporary query if you're explicitly
selecting only "99" names.

 

[scubadiver]

I tried it yesterday but it didn't work. It is working now though.

thanks.

Since the name of a query is text, try using "99" (text) in the criteria
instead of 99 (numeric).

John

Hello,

I want to list some specific queries in another query. The queries that I
want are prefixed by a 99 but when put 99 in the criteria of the first column
I get a "data type mismatch in criteria expression" error

any ideas?

SELECT Left([Name],2) AS prefix, Mid([Name],InStr([Name]," ")+1) AS QryName
FROM MsysObjects
WHERE (((MsysObjects.Type)=5) AND ((Left$([Name],1))<>"~"))
ORDER BY Left([Name],2);