mgebste
asked on
Select Count Function
Hi folks
I am a Biginner SQL programmer and I want to count the number of records in a query.
Actually to do it I am using a sql code in a listbox and counting the number of records in order to know the total amount ;
lc.RowSource = "SELECT enviado,assunto,conteúdos FROM sentitems where para Like '* " & Sobrenome.Value & "*' or para like '*" & EMail1 & "*' or para like '*" & Email2 & "*'"
Email_enviado = lc.ListCount
I Think this is slowing my application can someone tell me how to use a select count function (according to the code above) and allocating it to a variable (I want to use this variable in atext box)
Regards
I am a Biginner SQL programmer and I want to count the number of records in a query.
Actually to do it I am using a sql code in a listbox and counting the number of records in order to know the total amount ;
lc.RowSource = "SELECT enviado,assunto,conteúdos FROM sentitems where para Like '* " & Sobrenome.Value & "*' or para like '*" & EMail1 & "*' or para like '*" & Email2 & "*'"
Email_enviado = lc.ListCount
I Think this is slowing my application can someone tell me how to use a select count function (according to the code above) and allocating it to a variable (I want to use this variable in atext box)
Regards
Last Comment
Muhammad Kashif
select count(*) as TotalRecords from (Your Own Query)
lc.RowSource = select count(*) as TotalRecords from
(
"SELECT enviado,assunto,conteúdos FROM sentitems where para Like '* " & Sobrenome.Value & "*' or para like '*" & EMail1 & "*' or para like '*" & Email2 & "*'"
Email_enviado = lc.ListCount
)
(
"SELECT enviado,assunto,conteúdos FROM sentitems where para Like '* " & Sobrenome.Value & "*' or para like '*" & EMail1 & "*' or para like '*" & Email2 & "*'"
Email_enviado = lc.ListCount
)
lc.RowSource = "SELECT Count(*) AS total FROM sentitems where para Like '* " & Sobrenome.Value & "*' or para like '*" & EMail1 & "*' or para like '*" & Email2 & "*'"
Email_enviado = lc.Column(1)
ASKER
Folks,
Perhaps I was not very clear on my question but I would not like to have a SQL as a rowsource of a listbox then count the results( This is WHat I am doing as it is). I would like to bring this value to a variable (Like if I was using a Dcount function(SQL is Faster then Dcount) i.e result=dcount (.........) , something like result= ("SELECT Count(*) AS total FROM sentitems where para Like '* " & Sobrenome.Value & "*' or para like '*" & EMail1 & "*' or para like '*" & Email2 & "*'") )
As I have many query like this with a big database I believe that populating a listbox is consuming a lot of time
Regards
Perhaps I was not very clear on my question but I would not like to have a SQL as a rowsource of a listbox then count the results( This is WHat I am doing as it is). I would like to bring this value to a variable (Like if I was using a Dcount function(SQL is Faster then Dcount) i.e result=dcount (.........) , something like result= ("SELECT Count(*) AS total FROM sentitems where para Like '* " & Sobrenome.Value & "*' or para like '*" & EMail1 & "*' or para like '*" & Email2 & "*'") )
As I have many query like this with a big database I believe that populating a listbox is consuming a lot of time
Regards
This will set the total in xRetrieve :
Dim rec As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT Count(*) AS total FROM sentitems where para Like '* " & Sobrenome.Value & "*' or para like '*" & EMail1 & "*' or para like '*" & Email2 & "*'"
Dim rst As New ADODB.Recordset
rst.Open strSQL, cnx
rst.MoveFirst
Email_enviado = CDbl(rst("total"))
rst.Close
Set rst = Nothing
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
