SQL - Counting an Expression

[m0atz]

Hi Guys, I have two columns in a "Customers" table: "Cust" and "Cust_Adr" which are the Customers Name and Customers Address. The postcode is included in the Address column and I want to extract the Postcode and then return results where the same postcode appears more than 5 times.

So I tried:

[COLOR=#333333][FONT=Segoe UI]SELECT c.Cust, c.Cust_ADR, [/FONT][/COLOR][COLOR=#333333][FONT=Segoe UI]Mid(Cust_adr,InStrRev(cust_adr,",")+1) AS Postcode[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]FROM tbl_Customers AS c[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]GROUP BY c.Cust, c.Cust_ADR, [/FONT][/COLOR][COLOR=#333333][FONT=Segoe UI]Mid(Cust_adr,InStrRev(cust_adr,",")+1)[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]HAVING COUNT([/FONT][/COLOR][COLOR=#333333][FONT=Segoe UI]Mid(Cust_adr,InStrRev(cust_adr,",")+1)[/FONT][/COLOR][COLOR=#333333][FONT=Segoe UI]) >=5[/FONT][/COLOR]

But no joy. I'm getting "Data Type Mismatch is Criteria Expression" Error.

Can anyone help with where I'm going wrong? Cheers

 

[Joe4]

You don't want to return the individual Cust and Cust_ADR because if records have the same PostCode but different Cust or Cust_ADR fields, they won't be grouped!

I think you code should look something like this:

SELECT Mid([Cust_adr],InStrRev([cust_adr],",")+1) AS Postcode, Count(Mid([Cust_adr],InStrRev([cust_adr],",")+1)) AS PostcodeCount
FROM Customers
GROUP BY Mid([Cust_adr],InStrRev([cust_adr],",")+1)
HAVING Count(Mid([Cust_adr],InStrRev([cust_adr],",")+1))>=5;

 

[m0atz]

Thanks Joe, appreciate the reply. I'm still getting the "Data Type Mismatch is Criteria Expression" Error. It must be something this end within the data I assume, I will recheck. Appreciate the code.

 

[Joe4]

Do you have any null or blank values in the Cust_ADR field anywhere in your table?
Or any really short entries that would cause errors for your formula?

 

[m0atz]

Hi Joe, could well be the case. I've ran your code on some sample data and it works a treat, so must be something within the live data. Appreciate the reply.

 

[Joe4]

I entered a NULL value in for one address, and got the error you were talking about.
I made this change, and it seemed to handle the NULLs and worked:

SELECT IIf(Len([Cust_adr])>5,Mid([Cust_adr],InStrRev([cust_adr],",")+1),"") AS Postcode, Count(IIf(Len([Cust_adr])>5,Mid([Cust_adr],InStrRev([cust_adr],",")+1),"")) AS PostcodeCount
FROM Customers
GROUP BY IIf(Len([Cust_adr])>5,Mid([Cust_adr],InStrRev([cust_adr],",")+1),"")
HAVING Count(IIf(Len([Cust_adr])>5,Mid([Cust_adr],InStrRev([cust_adr],",")+1),""))>=5;

 

[m0atz]

I like it Joe - superb idea - many thanks!!!