Counting

[Chrisopia]

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!?

 

[Alc]

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

 

[Chrisopia]

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)

 

[Alc]

As far as the query is concerned, try

SELECT Left([LastName],3) AS LastThree, Count(*)
FROM [I]Tablename[/I]
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[I]([Company Field], [/I]DCount("*","[I]Tablename[/I]","Left([LastName],3) = '" & [I]set your value[/I] & "'", "")

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

 

[Chrisopia]

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

 

[Alc]

Okay, how about

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

 

[Chrisopia]

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

 

[Brianwarnock]

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

Isn't that Right(lastname,3)

Brian