Count number of fields populated from query

[toyt78]

Anyway to find out how many fields are populated in a query on my Access 2000 database?

In the below I need to create a query to give me a count of 4 because 4 fields are populated:

TableOne:
Id    firstName   LastName   City        State
1     John        Jones      San Diego

This one would be a count of 3:

TableOne:
Id    firstName   LastName   City        State
1     John                   San Diego

Please advise?

select * from TableOne where LastName = "Jones";

 

[PHV]

Something like this (I assumed Id is never null)?
select *, 1+IIf([firstName] Is Null,0,1)+IIf([LastName] Is Null,0,1)+IIf([City] Is Null,0,1)+IIf([State] Is Null,0,1) AS CountOfFields
from TableOne
where LastName = "Jones";

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

 

[steven290]

what about something like

select *,count(*) as count from Tableone 
group by firstname
having lastname="Jones"