Access inserts brackets around items in the IN operator

[teachman]

Hello,

I don't use Access very often, more of an Excel user, so my knowledge of Access is very basic. I'm hoping the fine folks in this forum can give me guidance with respect to my problem.

I have a simple delete query where I need to delete entire records based on a value in a single column. I have a list of 52 records that need to be deleted based on the value in the usiteid column. Since I don't want to type "," more than 100 times I bring the list of values to delete into MS Word and manipulate the list so the values are separated by ",". A short example: “08CS01201”,”08CS01203”,”08CS01903”. I put this into the query as part of the WHERE IN statement. Below is my very short delete query using the short list above.

DELETE VegStop.*, VegStop.[usiteid]
FROM VegStop
WHERE VegStop.[usiteid] In (“08CS01201”,”08CS01203”,”08CS01903”);

I usually create this kind of statement via the SQL View. But, when I switch to the Design View, the values inside the IN() look like the following:
[“08CS01201”],[”08CS01203”],[”08CS01903”]

Thinking this is an MS Word problem, I copied the manipulated list into NotePad and then copied it into the SQL View in Access. When I displayed the Design View, the brackets were back.

When I tried running this query, each of the bracketed values is being treated as a parameter requiring input.

I really don't want to have to type 50 or more values into the IN statement via the Design View. So, I was wondering if there is something I'm doing wrong or if I missed a step in the copy/paste manipulation, or perhaps something else entirely?

Thanks for your help.

George Teachman

 

[Joe4]

I have seen issues with the type of double-quotes being used. Believe it or not, “ and ” are often times treated differently than " (and I think you want this later one).

Instead of putting all the value you want to delete in MS Word, why not just put them in another Access table, a simple one column table. Then you could do a delete query structured something like this:

[COLOR=#333333]DELETE VegStop.*[/COLOR]
[COLOR=#333333]FROM VegStop[/COLOR]
[COLOR=#333333]WHERE VegStop.[usiteid] In 
(SELECT [/COLOR][COLOR=#333333]MyListTable.[/COLOR][COLOR=#333333]MyListValue
 FROM MyListTable);
[/COLOR]

 

[teachman]

Joe4,

I believe it. I've spent the better part of the last hour entering just a few of 50 values at a time into the WHERE IN() clause. I'm not sure of the number of times, but more than 5 times I used double quotes and Access thought it was a parameter. I then changed them to single quotes, worked just fine. So, the next few of the 50 values where typed in with the single quote. Access thought these were parameters. I then changed the single quote to the double quote and the delete worked fine. While I may occasionally curse the software, usually I'm not loud enough for anyone else to hear. Towards the end of the hour, I'm sure folks in Omaha heard my cursing.

I thought about doing what you suggested, but as I mentioned I'm not anywhere near an accomplished Access user. But, I will CASE your example and try that. CASE (Army for copy and steal everything).

Thanks for your help.

George

 

[Joe4]

But, I will CASE your example and try that. CASE (Army for copy and steal everything).

Please do! That is how a lot of coding gets done!

Basically, you should just be able to replace the table and field name in my code with whatever you decide to choose as your table/field name for the list.

Yes, single quotes is more of a SQL thing, but that does usually work in Access too (as Access is really a variation of SQL).

 

[xenou]

Also for the record NEVER use MS Word for working with code. It has quote/double quote characters that are not the same as the standard ones you would find in an Ascii table. Use a text editor. Preferably a good free one like Notepad++, JEdit, Sublime Text Editor, and so on.