Left Function in Access 97

[hianjali]

Hello All,

I need to validate State and ZipCode. I will have to extract all the data where is there is data mismatch between state and zipcode in my table elgadr.

The fields in elgadr are
for state: erstat
for zipcode: erzpcd

and data shows like
erstat erzpcd
CA 95682

To handle this. I created another table called state with valid zipcode ranges for each state

state_Code zipcd1 zipcd2 zipcd3
CA 900 966 0

My Query is :

SELECT
elgadr.erstat,
elgadr.erzpcd
FROM elgadr, state
WHERE elgadr.erstat = state.state_code And
((elgadr.erzpcd) NOT BETWEEN left([zipcd1, 3]) And
left([zipcd2, 3]) OR
elgadr.erzpcd <> left([zipcd3, 3]);

Someone please help

 

[SkipVought]

Hi,

Boolean arithmetic can be tricky wher using negating logic.

Try

Where...

And (Not [Between Expression] [b]And[/b] Not [Other Expression])

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[hianjali]

Thanks. I modified my query as

SELECT
elgadr.erstat,
elgadr.erzpcd
FROM elgadr, state
WHERE elgadr.erstat = state.state_code And
elgadr.erzpcd (NOT [BETWEEN (left([state.zipcd1,3]) And NOT (left([state.zpcd2,3])) OR elgadr.erzpcd <> (left([state.zpcd3,3])) ;

but still i get the wrong number of arguments error.

 

[SkipVought]

left([state.zipcd1[b][red]][/red][/b],3)

brackets in wrong place

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[hianjali]

Thank you. I modified it and my query now is

SELECT
elgadr.erstat,
elgadr.erzpcd
FROM elgadr, state
WHERE elgadr.erstat = state.state_code And
elgadr.erzpcd (NOT [BETWEEN (left([state.zipcd1],3) And NOT (left([state.zpcd2],3)) OR elgadr.erzpcd <> (left([state.zpcd3],3));

It still gives me error "Invalid bracketing of name '[BETWEEN (left([state.zipcd1]'

 

[SkipVought]

left(state.zipcd1,3)

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[Golom]

Also ... this bracket shouldn't be there

NOT [COLOR=red yellow][[/color]BETWEEN

 

[lespaul]

To handle this. I created another table called state with valid zipcode ranges for each state

state_Code zipcd1 zipcd2 zipcd3
CA 900 966 0

If this isn't a quick fix for some issue, I would reconsider this table design. It's 'committing spreadsheet' and there are better ways to store this information

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

And what about this ?
SELECT elgadr.erstat, elgadr.erzpcd
FROM elgadr INNER JOIN state ON elgadr.erstat = state.state_code
WHERE Not (
(Left(elgadr.erzpcd,3) Between state.zipcd1 And state.zipcd2)
OR Left(elgadr.erzpcd,3) = state.zipcd3);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hianjali]

Thank you everyone. I made couple of changes and my final query is like

SELECT elgadr.erstat, elgadr.erzpcd
FROM elgadr, state
WHERE ((elgadr.erstat)= [state].[state_code]) AND

((elgadr.erzpcd)) Not Between Left([state].[Zipcd1],3) And Left([state].[Zipcd2],3) AND

((elgadr.erzpcd)) <> Left([state].[Zipcd1],3) AND ((elgadr.erzpcd)) <> Left([state].[Zipcd2],3);

Here it gives me mixed data...i mean both correct data and wrong data... For eg:

For CA, zipcode should be between 900 - 966. Here it returns me the right values

erstat erzpcd
CA 89052
CA 89052
CA 89052

Which is right, but for OR, it shld be between 970-979 and it returns me

erstat erzpcd
OR 97914
OR 97914

Which shldnt have come up. as 979 is valid zipcode for OREGON.

Please advice

 

[lespaul]

again, if this is not for a quick fix, you should change your structure to something more normalized:

State     ZipPrefix
CA          900
CA          901
CA          902
.....
CA          966
OR          970
OR          971
.....

OR

State     ZipBegin     ZipEnd
CA         900          966
OR         970          979

I'm also confused by:

To handle this. I created another table called state with valid zipcode ranges for each state

state_Code zipcd1 zipcd2 zipcd3
CA          900     966     0

Left([state].[Zipcd1],3)

From your example above, the State table Zipcd1 field only has 3 characters, why do you need the LEFT function to get only 3 characters when there are only 3 characters in the field?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design