Quotes Not Added by Access

[phmckeever]

Hello,
I am setting up a query in Design View in Access 2007. In 2003 when I use the IN operator, enter the list of items, and move to the next column, the quotes marks were automatically added to the list of items. Here is a sample:

In (“039H4929”,”039H5239”,”039H5240”,”041C00D6”,”1756WC1”,”
200NF51”,”2GYSMC1”,”2UX61501N5”,”2UX63200F2”,”345U2”,”
3802A78866”,”415A00321”,”6J18FXK19062”,”6J26KQV1A02R”,”
709F0A4F”,”7ZQ74B1”,”834F25D5”,”9FR1N21”,”9ZJN581”,”
D006DDM2K445”,”D015CQX3K046”,”D235JZG1D598”,”
D240JZG2F871”,”D242JZG2H663”,”D245JZG1D427”,”D245JZG1D433”)
(Above, I added hard returns for easy viewing)

These quote marks are not being added using 2007. Even if I set the criteria list up with quote marks in word or excel and copy it into the column on the criteria line in Access 2007 design view, I still get an error. The only way I have been able to get it to work is to manually enter the quote marks on the criteria line in the Design View. These lists are going to grow, and it is counter productive to have to manully enter these marks.

Can someone help!

phmckeever

 

[vbaInet]

So you're giving your users the power to go into design view and create their own queries?

 

[phmckeever]

No; I am creating the queries.

 

[vbaInet]

It's working for me. Have you tried on a new db?

By the way, if you're creating the query and you're needing to type all those values in, why not create a table instead?

 

[phmckeever]

This is a new db; and, this option is not work on any of the dbs created in 2007 (in the last three weeks, I have created four).

 

[vbaInet]

Alright, just create a brand new db, open a new query in design view and type
IN (Hello) in the Criteria row then tab out.

 

[phmckeever]

Ok; that worked. But, I need to know why it is not working with my alpha-characters?

 

[vbaInet]

You're using the wrong type of quotes. It should be:

In ("039H4929","039H5239","039H5240","041C00D6","1756 WC1","
200NF51","2GYSMC1","2UX61501N5","2UX63200F2","345U 2","
3802A78866","415A00321","6J18FXK19062","6J26KQV1A0 2R","
709F0A4F","7ZQ74B1","834F25D5","9FR1N21","9ZJN581" ,"
D006DDM2K445","D015CQX3K046","D235JZG1D598","
D240JZG2F871","D242JZG2H663","D245JZG1D427","D245J ZG1D433")

 

[boblarson]

Also, since you have so many of these, and you yourself said that it will be growing, you need to change the way you are doing it.

Create a table to store them in and then just link to it using an INNER JOIN when you want your criteria.

It is better to do it that way, it is more efficient and it is more easily changed than having to go into design view of an object (query) and modify it.

 

[vbaInet]

Also, since you have so many of these, and you yourself said that it will be growing, you need to change the way you are doing it.

Create a table to store them in and then just link to it using an INNER JOIN when you want your criteria.

It is better to do it that way, it is more efficient and it is more easily changed than having to go into design view of an object (query) and modify it.

I mentioned this a few posts ago but it seems the OP didn't pay attention. Hopefully now that you've elaborated he/she will go down this route.

 

[phmckeever]

After some researh and discovery, there seems to be an issue with Access 2007 query automatically adding quotation marks in the In operator list query for alphanumeric data. In this instance when you tab to the next column, you get a syntax error. If each items in the list in the query is numbers or letters, Access adds the marks. But, if it is a mix of numbers and letters, alphanumeric, it does not. My list is alphanumeric. An example is as follows:

In ("0216AH0304","0530NNA00P","063802334","071053EP","10011444","102H30B4",
"108AC5D","10A4119","1110B","1300472","1301448","212C5FD1","229C5EFC","231C5DA5"
,"2U2051306V","334SC6A2","503000005535","503000005536","54200444","557000200079"
,"7814606","D305FSZ1H145","G33CYK","G33CYM","G33CYN","G33CYP","G33CYQ","G33FLY",
"G33FLZ","G33FM0","G33FM1","G33FM2","KPGRZ12","KPTD431","P00120138","SG03340145",
"SG04340205","SG04450313","SG04510044","SG05270247","SGF0614204")

These quotation marks were manually added to the query. When you manually add them, it works. However, because this list is growing, I can't keep manually adding these marks. Can someone code for me to have quotation marks added by Access? Also, someone mentioned adding the values in a table join. Can some elaborate on this option?

 

[MSAccessRookie]

After some researh and discovery, there seems to be an issue with Access 2007 query automatically adding quotation marks in the In operator list query for alphanumeric data. In this instance when you tab to the next column, you get a syntax error. If each items in the list in the query is numbers or letters, Access adds the marks. But, if it is a mix of numbers and letters, alphanumeric, it does not. My list is alphanumeric. An example is as follows:

In ("0216AH0304","0530NNA00P","063802334","071053EP","10011444","102H30B4",
"108AC5D","10A4119","1110B","1300472","1301448","212C5FD1","229C5EFC","231C5DA5"
,"2U2051306V","334SC6A2","503000005535","503000005536","54200444","557000200079"
,"7814606","D305FSZ1H145","G33CYK","G33CYM","G33CYN","G33CYP","G33CYQ","G33FLY",
"G33FLZ","G33FM0","G33FM1","G33FM2","KPGRZ12","KPTD431","P00120138","SG03340145",
"SG04340205","SG04450313","SG04510044","SG05270247","SGF0614204")

These quotation marks were manually added to the query. When you manually add them, it works. However, because this list is growing, I can't keep manually adding these marks. Can someone code for me to have quotation marks added by Access? Also, someone mentioned adding the values in a table join. Can some elaborate on this option?

I think that no matter what fix you make, it is going to break when you have to add more choices. Please take time to read posts 4 and 9 of this thread where vbaInet and boblarson have provided an appropriate solution for you. Properly included into your Query, a Table will allow your list to grow without affecting the functionality of the Query.

 

[boblarson]

Also, someone mentioned adding the values in a table join. Can some elaborate on this option?

It is like this -

You create a table with a single field for your items.

You then just create your query and include that table and have it joined to the like field.

So, for example, if I have a table with employees

tblCoEmployees
EmpID - Autonumber
EmpName - Text

And I want to include 3 employees - ID's 3, 8, and 12

I would have my table
tblInclustions
EmpIDsToInclude - Long Integer

I would have my SQL for my query like this:

 .
 
SELECT tblCoEmployees.EmpID, tblCoEmployees.EmpName
FROM tblCoEmployees INNER JOIN tblInclusions ON tblCoEmployees.EmpID = tblInclusions.EmpIDsToInclude;
 
 
.