how to store result from "select count(*) ..." in a vba-string

[frag]

hi there!

here is my problem:

i'd like to store the result of a sql-select (like: 'select count(*) from

') in a string.
i am using MS Access97 with VBA!

i just don't get this DAO-stuff...

thanx!

frag

 

[padinka]

Dim myResults as string

myResults = SELECT Count([tablename].[fieldname]) FROM tablename;

However you can't do a count using an *.




Trisha
padinka@yahoo.com

 

[frag]

to padinka:

thank you for your help!!

i just have thrown this 'select count(*)' - idea away.

i am using a 'normal' select and save the result to a recordset and count it's entries...

------------------------------------------------------------
' SELECT-string for Recordset

If subject <> &quot;'*'&quot; Then
strSELECT = &quot;SELECT * FROM supportcall WHERE supportcall.subject = &quot; & _
subject & &quot; AND supportcall.calldate = #&quot; & mydate & &quot;#;&quot;
Else
strSELECT = &quot;SELECT * FROM supportcall WHERE supportcall.calldate = #&quot; & mydate & &quot;#;&quot;
End If

' build Recordset
Set rstCallCount = CurrentDb.OpenRecordset(strSELECT)

If Not rstCallCount.EOF Then
rstCallCount.MoveFirst

Do While Not (rstCallCount.EOF)
i = i + 1
Select Case rstCallCount.Fields(2).Value
Case Is = &quot;< 5 min&quot;
less5min = less5min + 1
Case Is = &quot;~ 1 hour&quot;
about1hour = about1hour + 1
Case Is = &quot;~ 1 day&quot;
about1day = about1day + 1
Case Is = &quot;> 1 day&quot;
greater1day = greater1day + 1
End Select

Select Case rstCallCount.Fields(3).Value
Case Is = &quot;none&quot;
vit_none = vit_none + 1
Case Is = &quot;Front-Office&quot;
vit_front = vit_front + 1
Case Is = &quot;Back-Office&quot;
vit_back = vit_back + 1
Case Is = &quot;Kondor+&quot;
vit_kondor = vit_kondor + 1
End Select

rstCallCount.MoveNext

Loop

End If
------------------------------------------------------------

this seems to work fine.
but anyway thank you!

cya

frag