10

The database my application uses has field names containing spaces. I believe this to be the cause of my problem. Here is a typical query:

SELECT * FROM 'OV2 BAS' AS bas 
INNER JOIN 'OV2 RefID' AS ids ON 'bas.Ref ID' = 'ids.Ref ID' 
WHERE ids.ENUM_H = 'TDischarge';

How do I deal with the spaces in the field names? Thanks.

Additional Information

This is to access a database made with MS Access 2007 (Microsoft.ACE.OLEDB.12.0).

Jim Fell
  • 13,750
  • 36
  • 127
  • 202

5 Answers5

14

I don't think you can use quotes around the actual table name; only the name you assign to it. I would wrap the table in brackets instead: [OV2 BAS]

You also can't put quotes around your joining syntax either. Try this instead:

SELECT * FROM [OV2 BAS] AS bas INNER JOIN [OV2 RefID] AS ids ON bas.[Ref ID] = ids.[Ref ID] WHERE ids.ENUM_H = 'TDischarge';
James Johnson
  • 45,496
  • 8
  • 73
  • 110
10

Replace ' with

  • postgreSQL, Oracle: "
  • MySQL `
  • SQL-server: [ and ]

For example: "OV2 BAS", bas."Ref ID" = ids."Ref ID", etc.

Johan
  • 74,508
  • 24
  • 191
  • 319
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • +1 as delimiting tablename and column name together definitely seems wrong. Though I'm guessing backticks rather than quotes based on the delimiter used in the question. – Martin Smith Nov 02 '11 at 16:51
2

For Microsoft Access, wrap field names that contain spaces with back ticks e.g. SELECT `Eng Units` FROM Table

Rob Bowman
  • 7,632
  • 22
  • 93
  • 200
1

That depends on the database engine you're using.
For SQL Server, you have to put the field names in brackets: [ ]

SELECT * FROM [OV2 BAS] AS bas 
INNER JOIN [OV2 RefID] AS ids ON bas.[Ref ID] = ids.[Ref ID]
WHERE ids.ENUM_H = 'TDischarge';
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
1

You don't specify which DBMS you're using, but I'm guessing SQL server, so

SELECT *
FROM [OV2 BAS] AS bas
     ^^^^^^^^^

... enclose the field name in brackets. Using quotes as you are, turns the field name into a plain string, which will NOT be treated as a fieldname by SQL server.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • This is to access a database made with MS Access 2007 (Microsoft.ACE.OLEDB.12.0). – Jim Fell Nov 02 '11 at 16:56
  • Ok. brackets'll work there too. Consider renaming your fields so there's no spaces in them if you can - spaced names will cause pain down the road (as you're finding now). – Marc B Nov 02 '11 at 16:57
  • You'll have to unquote the field names on the JOIN clause as well - quoting field names turns them into strings, and they WON'T be treated as field names. ` ... ON bas.[Ref ID] = ids.[Ref ID]` – Marc B Nov 02 '11 at 17:04
  • Why do you say "spaced names will cause pain down the road"? As long as you quote your fieldnames (which you should **always** do), it will be fine. – rjmunro Sep 17 '13 at 11:48
  • @rjmunro "As long as you quote, you should be fine" That's exactly the problem. Not everybody does. Marc B is correct: putting spaces in field names is not best practice. – Annatar Apr 07 '17 at 20:56