SELECT string with table name of two words problem

[Dave F.]

Hi

I'm writing a VBA routine within AutoCAD to query a Table which has a name of ALL LAYERS.
I believe it's have trouble coping with the space in the name.

Dim SqlStr As String

Set MyDB = OpenDataBase("c:\dwgs\docs\BH-Data.mdb", False, True, "MS Access;PWD=sbpl123")
SqlStr = "SELECT Layer FROM " & "'All Layers'" & " WHERE Layer = 's%'"
Set Myrs = MyDB.OpenRecordset(SqlStr)

I get an error of - Syntax error in query. incomplete query clause.
I've tried various ways inlcuding using double quotes but nothing seems to work.

This is another table query in the same database that works.
SqlStr = "SELECT Type, ID FROM LayerType WHERE Type = 'Structural'"

So where am I going wrong.

Hope you can help

Cheers
Dave F.

 

[Michel Walsh]

If the name is ill formed, or a reserved name, you need to include it inside
[ ], [like this]

Vanderghast, Access MVP

 

[Chris O'C via AccessMonster.com]

You should only use letters of the alphabet, numbers and the underscore for
names. If you forget that rule, most of the time you can enclose the bad
name in brackets. It doesn't always work correctly, so don't depend on it.

Chris
Microsoft MVP

 

[Dave F.]

You should only use letters of the alphabet, numbers and the underscore for names.

I had a feeling that was the case. Unfortunately this is an external DB & I have no control over it.
Thankfully [....] appears to work.

Thanks to both for your prompt replies.


Chris O'C via AccessMonster.com wrote:
.. If you forget that rule, most of the time you can enclose the bad

 

[Dave F.]

Oh Boy! I've ran straight into my second problem. Hope you a solve it as easily as before.

SqlStr = "SELECT Layer, Description FROM [All Layers] WHERE Layer LIKE 's%'"

AFAIK, using this wildcard should return numerous rows of all the Layers that begin with s.
It returns 0.

For some reason I tried * instead of % it returned 1 row - the first one beginning with s.

Looking on the web * isn't even a wildcard.

What am I doing wrong?


Cheers
Dave F.

 

[Michel Walsh]

Jet can use the * (the DOS wildcard) or % (the UNIX wildcard). It
depends on an option setting.


Hoping it may help,
Vanderghast, Access MVP

 

[Chris O'C via AccessMonster.com]

Description is a reserved word so you should enclose that in brackets, too.
Jet uses the * as a wildcard, unlike other dbs that use the % symbol.

Chris
Microsoft MVP

Oh Boy! I've ran straight into my second problem. Hope you a solve it as easily as before.

SqlStr = "SELECT Layer, Description FROM [All Layers] WHERE Layer LIKE 's%'"

AFAIK, using this wildcard should return numerous rows of all the Layers that begin with s.
It returns 0.

For some reason I tried * instead of % it returned 1 row - the first one beginning with s.

Looking on the web * isn't even a wildcard.

What am I doing wrong?

Cheers
Dave F.

 

[Jeff Boyce]

Dave

In Access SQL, use "*". In SQL SQL, use "%"

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Chris O'C via AccessMonster.com]

FYI, you can switch to the % symbol for wildcards in queries if you set the
option for SQL Server compatible syntax (ANSI 92) in Tools > Options >
Tables/Queries. That won't help you in a db you have no control over, but it
might help in your own dbs.

Chris
Microsoft MVP