SELECT string with table name of two words problem

  • Thread starter Thread starter Dave F.
  • Start date Start date
D

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.
 
If the name is ill formed, or a reserved name, you need to include it inside
[ ], [like this]

Vanderghast, Access MVP
 
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
 
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.
 
Jet can use the * (the DOS wildcard) or % (the UNIX wildcard). It
depends on an option setting.


Hoping it may help,
Vanderghast, Access MVP
 
Dave

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

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top