MsysObjects WHERE Function isn't available 1

[knownote]

Function isn't available in expressions in query expression ...
error occurs for
MsysObjects WHERE section.

Below code used to work in Access 97 (before reinstalling Access 97 and 2000). It now only works without Where clause, specifically "left$".

Is there a missing reference for "left$"?


SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") And ((Left$([Name],4))<>"Msys") And ((Left$([Name],4))<>"tsys") And
((Left$([Name],4))<>"tlkp") And ((Left$([Name],8))<>"tblAdmin") And ((Left$([Name],7))<>"tblInfo") And
((Left$([Name],17))<>"Switchboard Items") And ((Left$([Name],22))<>"tbl LIBRARY Parameters") And ((Left$([Name],10))<>"tblAccount") And ((Left$([Name],11))<>"tblCategory") And ((Left$([Name],7))<>"tblTodo") And ((Left$([Name],10))<>"tblEssence") And ((Left$([Name],9))<>"tblStatus") And
((Left$([Name],10))<>"qdfReports") And ((Left$([Name],9))<>"qryCities") And
((Left$([Name],8))<>"qryDates") And ((Left$([Name],15))<>"qryOverdueItems") And
(([MsysObjects].[Type])=1 Or ([MsysObjects].[Type])=5 Or ([MsysObjects].[Type])=6) And (([MsysObjects].[Flags])=2097152 Or ([MsysObjects].[Flags])=128 Or ([MsysObjects].[Flags])=0 Or ([MsysObjects].[Flags])=16))
ORDER BY MsysObjects.Name;

 

[Remou]

It works for me (Access 2000). Have a look to see if you are missing any references. It is more normal now to just use Left.

 

[knownote]

Nevermind, I reversed approach to 3 items to be included.

I usually start a thread for a syntax error, not being a programmer, but I guessed right (a first and probably last).


SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE (([MsysObjects].[Name]="tblClients") Or ([MsysObjects].[Name]="tblContacts") Or ([MsysObjects].[Name]="qdfAll") And
(([MsysObjects].[Type])=1 Or ([MsysObjects].[Type])=5 Or ([MsysObjects].[Type])=6))
ORDER BY MsysObjects.Name;

 

[knownote]

Thanks, Remou.
This works, but is extra "(" needed before

(([MsysObjects].[Type])=1 Or

 

[Remou]

Try:

[tt]WHERE ([MsysObjects].[Name]="tblClients"
Or [MsysObjects].[Name]="tblContacts"
Or [MsysObjects].[Name]="qdfAll")
And
([MsysObjects].[Type]=1
Or [MsysObjects].[Type]=5
Or [MsysObjects].[Type]=6)
ORDER BY MsysObjects.Name;[/tt]

 

[knownote]

Thanks, Remou, that's easier to follow.