Forum Discussion

bkirk370's avatar
bkirk370
Copper Contributor
Mar 18, 2021

Access criteria not recognizing field names in quotes

I imported a large file which was in the "text" format. Most of the fields imported with quotations around them. They sort correctly most of the time. When I create a query and wish to use the county...
access
George_Hepworth's avatar
George_Hepworth
Silver Contributor
Mar 18, 2021

bkirk370 

There are actually two different approaches to resolving this; which one you select depends, in part, on the context.

If this is a one-time import of a file, and it came in with the "bonus quote marks" AND if you don't need them in Access for any other reason, the simplest thing would be to remove them with an update query:
Update tblYourTableNameGoesHere Set CountyName = Replace([CountyName, """","")

 

The Replace function will permanently remove the superfluous characters.

 

DO THIS ON A BACKUP COPY OF THE TABLE FIRST TO ENSURE YOU GET THE RESULTS YOU NEED.

 

Even if this is a repeat import of the same text file from time to time, that would work.

 

You could also adjust the import specification for this file, if you have to repeatedly import it, to eliminate the quote marks on the way in.

 

And finally, if you want to keep the quote marks for some reason (can't think what that might be, but who knows), you could wrap your criteria in that query you use to filter the list the same way, i.e. with Replace() to exclude the quote marks from that specific query criteria. 

Again, context will determine, in part, which approach is most appropriate. 

 

hm. I guess that's more than two approaches....