[Td04]

I try to count a number which is a string, but some rows are empty (null) so I need to make some thing to make this work.
Any ideas?
Thanks//Martin

SQL1 = "SELECT DISTINCT COUNT(cint(Fnr)) AS AntalUnika FROM plan_info"

[DMWCincy]

If I understand this, you have multiple rows but the values you are using to count on are equal to null.

Try doing this:

SQL1 = "SELECT DISTINCT COUNT(1) AS AntalUnika FROM plan_info"

[Td04]

yes its a table with a field which is in some rows null, not all of them, most of the rows have a number as a string for example "12332455" but there are duplicates so I have to use the distinct..Maby I can use some kind of case statement?
The table is actually a "viritual acess table" wich contents is dependent on real tables in the mdb, so I can´t edit the fields properties to not allow nulls either
//M

[r937]

okay, there are several things wrong here

if you are doing a count from a table, then DISTINCT is not necessary, because the aggregate function will return only one value, and it will be distinct

if you are just counting them, there's no need to convert them from string to integer

if all you want is the number of non-nulls, then just count the column values, because aggregate functions ignore nulls

if you want the count of the number of distinct values, then the DISTINCT has to go inside the COUNT()

so, what do you actually want?

[Td04]

ok I thought i had to use a numeric field to do the count.. so count skipp the "nulls" thats good.
What I would like to do is count the records in a previous selection.
But only to count the Unique values, so the duplicate values does not get included.
I dont want to count the whole table, but only a user selected part so its kind of tricky. :-#

'first sql
cn="Driver=Microsoft Access Driver (*.mdb); DBQ=Q:\Winapp32\Edp\Fasoff\data\fas.mdb;"
set rs = server.createobject("ADODB.Recordset")
SQL = "SELECT * FROM plan_info WHERE LMAKT = '"& session("planen") &"' order by FastBeteckn"
rs.open SQL,cn
'number of unique
Set rs1 = Server.CreateObject("ADODB.Recordset")
SQL1 = "SELECT COUNT(Fnr) AS AntalUnika FROM plan_info"
rs1.Open SQL1,cn

[r937]

select count(distinct Fnr) as AntalUnika 
  from plan_info
 where LMAKT = value

[Td04]

That looks promising but..
I tried as below but I get syntax error at this line
"Syntaxerror (missing operator) in experssion 'count(distinct Fnr)'.
did I miss out on sth?
This is the code I tried
thanks//M

'user query
cn="Driver=Microsoft Access Driver (*.mdb); DBQ=Q:\Winapp32\Edp\Fasoff\data\fas.mdb;"
set rs = server.createobject("ADODB.Recordset")
'SQL = "SELECT FastBeteckn,NAMN,PLNAMN,LMAKT,PLANFK,PSTAT,PLANANM M FROM plan_info WHERE LMAKT = '"& session("planen") &"' order by FastBeteckn"
SQL = "SELECT * FROM plan_info WHERE LMAKT = '"& session("planen") &"' order by FastBeteckn"
rs.open SQL,cn
'number of unique
Set rs1 = Server.CreateObject("ADODB.Recordset")
SQL1 = "select count(distinct Fnr) as AntalUnika from plan_info where LMAKT = value"
rs1.Open SQL1,cn

[r937]

sorry, i knew this but i momentarily forgot it

access can't do COUNT(DISTINCT xxx))

what you could do is run SELECT DISTINCT Fnr .... and then use RecordCount

[Td04]

ok, "r937", "DMWCincy" Thanks alot your help, I really have to get a good SQL book one of these days..
//Martin