Run a query when a fieldname contains one or more spaces.

[Whit]

I am trying to run a query in Access SQL. The table I am querying has a number of field names that contain one more spaces in them.


One such field name is Serial ID (Filler9) . It is a field in in records in the table, tblit. Trust me, I did not come up with that field name.

I keep reading that by surrounding the field name with brackets [ ] , it will work fine in a query. Not all of the time
for me.

Below are several queries I ran with this field name. It seems that it will always work as an item in a select list, but in where clauses it sometimes works and sometimes doesn't. When it doesn't work, it is considered a parameter, which means it is not recognized as a field in a record in the table. I cut and pasted the field from a query that worked into all the other queries, so I know the field name is spelled properly.

Any ideas on how to get it to work all the time?


This one works

INSERT INTO tblErr ( LetterSerialID )
SELECT [Serial ID ]
FROM tblIT;



This one does not work.



This one works.

SELECT tblIT.[Serial ID (Filler9)]
FROM tblIT
LEFT JOIN tblVendor ON tblVendor.filler9= tblIT.[Serial ID (Filler9)];
-------------------------------------------------------------------
This One does not work








__________________________________________________ ___________________
This one does not work

----------------------
------------------------------------------------------------------------------------------------


This one works

select tblIT.[Serial ID (Filler9)] from tblit
where tblIT.[Serial ID (Filler9)] > ''



This one works

INSERT INTO tblErr ( LetterSerialID )
SELECT [Serial ID (Filler9)]
FROM tblIT
where [Serial ID (Filler9)] > ''

[June7]

Seem to be missing all the 'does not work' examples. Might edit your post to clarify.

Cannot replicate issue. I tested with a similar name field and query works fine.