Pulling Minimum Lengths

[Swi]

Hi,

I have the below query. My thought was the part at the end would only select records that had a +4 zip code (12345-1234), however this does not seem to work. Any ideas?

Thanks.

"SELECT Min(Len([NAME])) AS [NAME], " & _
            "Min(Len([ADDR2])) As [ADDR2], Min(Len([ADDR1])) As [ADDR1], " & _
            "Min(Len([CITY ST ZIP])) As [CITY ST ZIP], Min(Len([Salutation])) As [Salutation] " & _
            "FROM [Test.csv] WHERE LEFT(RIGHT([CITY ST ZIP],5),1) = '-'"

 

[gmmastros]

try this

Where [CITY ST ZIP] like '%[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%'

 

[dhookom]

You never said why you think the expression didn’t seem to work or provide examples.

Most of us recommend three separate fields for City, State, and Zip. Maybe you have no control over this.

 

[Swi]

I will give that a shot. After I posted I think I may have found the issue but had to leave for the day. I will check it Monday and report back. Thanks.

 

[Chriss Miller]

I think you have trailing spaces in the field, so the thought of having a - in the left 1 char of the rightmost 5 chars is wrong, you first need to trim off spaces (and other whitespace, if there may be tabs, for example, even if only accidentally).