Query by Max and Len

[bd528]

Hi all,

I have the following query which returns the last 5 digits of a statement number :-

SELECT DISTINCT b.Brokerage_Name, Max(Right([Statement_Number],5)) AS Expr1
FROM tblBrokeragesNew b LEFT JOIN tblStatementsNew s ON b.ID = s.Brokerage_ID
GROUP BY b.Brokerage_Name;

How can I amend this so the query only considers [Statement_Number] with a length of 17?

Thanks

 

[June7]

Create calculated field with: Len([Statement_Number]). Select WHERE in the Total row. On the Criteria row: =17.

 

[bd528]

Create calculated field with: Len([Statement_Number]). Select WHERE in the Total row. On the Criteria row: =17.

That worked - thank you

 

[plog]

FROM tblBrokeragesNew b LEFT JOIN tblStatementsNew s ON b.ID = s.Brokerage_ID

If [Statement_Number] is from tblStatementsNew, you will effectively turn that LEFT JOIN into an INNER JOIN with that criteria. That may be ok, but what it means is that prior to that criteria every Brokerage_Name would be shown--even if it doesn't have a matching record in tblStatementsNew. This criteria will eliminate those without a match.