What would be the best way to count the company name, or the last name?

Including Left([LastName],3)

So it really counts people with the same last 3 letters in their last name,


thing is I will only count the last name if the Company field isn't being used... how do I go around this then!?

Are we talking about counting in a query, on a form, or what?

query perhaps... or a form

they will be used in both so Im not sure which is best.

All Ill do then is add a prefix, and add 1 to the number
(i.e. im making a customer reference thingy)

As far as the query is concerned, try

SELECT Left([LastName],3) AS LastThree, Count(*)
FROM Tablename
GROUP BY Left([LastName],3);

That should give you the number of each combination.

On a form, you could set the source for the field displaying the count to something like

=IIf(IsNull([Company Field], DCount("*","Tablename","Left([LastName],3) = '" & set your value & "'", "")

This assumes you want to display nothing if the company name field is being used.

Aaah! So far so good...

but I need it to count the company field if it is there, if not it counts the lastname field instead

Okay, how about

=IIf(IsNull([Company Field], DCount("*","Tablename","Left([LastName],3) = '" & set your value & "'", DCount("*","Tablename","Company Name = '" & [Company Field] & "'")

Yes, getting there... i'll tweak it a bit later, thanks!

So it really counts people with the same last 3 letters in their last name,

Isn't that Right(lastname,3)

Brian