Records that contain a certain word in a field (among other words)

[LeBron]

I have a database that includes an Address field, containing full addresses - street, number, city, state and country. I'm trying to create a query that will include only records that have a certain state in the Address field ie. NY. What's the syntax to filter for a full word among other text in a field?

The query I set up either finds nothing, or also finds records with "Germany" in the address because it includes "ny".

 

[pbaldy]

The first mistake was putting the whole address in one field, but try including spaces in your criteria, if that's what the data looks like. In other words, instead of "NY", try " NY "

 

[MSAccessRookie]

I have a database that includes an Address field, containing full addresses - street, number, city, state and country. I'm trying to create a query that will include only records that have a certain state in the Address field ie. NY. What's the syntax to filter for a full word among other text in a field?

The query I set up either finds nothing, or also finds records with "Germany" in the address because it includes "ny".

Since the Address is stored in a single Text/Memo Column, unless you add some sort of Delimiter for the State Code Identifier, you will not be easily able to isolate it.

As you are learning, a single Text/Memo Column is not usually the best method for storing an entire Address. Breaking it into its distinct parts and putting them back to gether in a subsequent Query or Function is a much better way to go.

Note: Pbaldy's suggestion about adding spaces (" NY ") will probably work most of the time, but does not take into account any cases where a period or a Comma is found ("NY." and "NY,"). Breaking it into its distinct parts and putting them back to gether in a subsequent Query or Function remains a much better way to go.

 

[LeBron]

Thanks both. Yes, realizing putting the whole address in one field it was unwise...

Tried Like " NY ", ", NY " and other variants but it finds nothing, strangely. Maybe it just doesn't recognize spaces?

 

[MSAccessRookie]

Thanks both. Yes, realizing putting the whole address in one field it was unwise...

Tried Like " NY ", ", NY " and other variants but it finds nothing, strangely. Maybe it just doesn't recognize spaces?

I am not sure about where your issues are. Standard Text Searching (Instr() and InstrRev()) recognize spaces. Perhaps you can post the SQL Code for us to review.

 

[pbaldy]

You would need wild cards of course.

 

[Scooterbug]

I noticed that a zipcode isn't included in your memo field. If you have a zipcode, you could create a table with zipcodes and their related states (google search, they are out there). You can then link the zips together and pull the state off of that.

 

[MSAccessRookie]

I noticed that a zipcode isn't included in your memo field. If you have a zipcode, you could create a table with zipcodes and their related states (google search, they are out there). You can then link the zips together and pull the state off of that.

As you can see, Searching a Text/Memo Column containing the Address for a state code is possible, if you put enough effort into the process.

Breaking the address into its distinct parts and putting them back together in a subsequent Query or Function remains a much better way to go.

 

[Scooterbug]

As you can see, Searching a Text/Memo Column containing the Address for a state code is possible, if you put enough effort into the process.

Breaking the address into its distinct parts and putting them back together in a subsequent Query or Function remains a much better way to go.

Sorry, was assuming that the zipcode might have it's own field and isn't part of the Address Memo field. and for the record..I agree on breaking up the address

 

[MSAccessRookie]

Sorry, was assuming that the zipcode might have it's own field and isn't part of the Address Memo field. and for the record..I agree on breaking up the address

----

ScooterBug,

Please understand that I was referring to the effort that it would to create and/or adapt a table of Zip Codes / State Codes that would be required to perform the search. For the record, I assumed that since it was a part of the address, the Zip Code would be contained in the Address Memo Field.

 

[Scooterbug]

Ahhh...gotcha

I only mentioned the zipcode table because I have one here for my database. I obtained it off a quick google search a while back ago. It was missing a some zipcodes, but I was able to add them in when needed.

 

[MSAccessRookie]

Ahhh...gotcha

I only mentioned the zipcode table because I have one here for my database. I obtained it off a quick google search a while back ago. It was missing a some zipcodes, but I was able to add them in when needed.

Sounds like a great thing to have in a database. My Company does not have one at this time, so I assumed that the Op (Like me) would need to either find or make one.

 

[Scooterbug]

Fyi...

http://sourceforge.net/projects/zips/