Convert sql count to integer (1 Viewer)

exaccess

Registered User.
Local time
Today, 12:07
Joined
Apr 21, 2013
Messages
287
How can I convert the result of a SELECT COUNT statement to integer so that I can use in vba?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:07
Joined
May 7, 2009
Messages
19,175
The result is already numeric.
If you need further, cast it to Int() function

Select Int(Count(field)) As IntCount ....
 

exaccess

Registered User.
Local time
Today, 12:07
Joined
Apr 21, 2013
Messages
287
The result is already numeric.
If you need further, cast it to Int() function

Select Int(Count(field)) As IntCount ....

I tried it says type 13 mismatch. Here is the code:

Code:
 I = "SELECT Int(Count([Email])) as [ResultFm].[nnum] FROM [" & dest & "]" & _
        " WHERE [Language] <> 'X' ORDER BY [LastName];"
Here ResultFm is the continuous form to be displayed, nnum is the variable in the header, dest is a variable representing the table which can change
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:07
Joined
May 7, 2009
Messages
19,175
Yiu lack a recordset for that to work:

I= currentdb.openrecordset("select count(email)....")(0)
 

exaccess

Registered User.
Local time
Today, 12:07
Joined
Apr 21, 2013
Messages
287
Yiu lack a recordset for that to work:

I= currentdb.openrecordset("select count(email)....")(0)

I have done exactly as you wrote, but does not work. It stops at that sentence and says type mismatch.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:07
Joined
May 7, 2009
Messages
19,175
what is "[ResultFM].[nnum]" on your query?
that is illegal (having dot (.) on query).

there is no need for sorting Order since
we are only interested on the Count() Value.

should be:

I = CurrentDB.OpenRecordset("SELECT Count(Email) As Expr1 FROM [" & dest & "]" & _
" WHERE [Languange] <> 'X'")(0)
 

isladogs

MVP / VIP
Local time
Today, 11:07
Joined
Jan 14, 2017
Messages
18,186
Your code makes little sense to me :

Code:
I = "SELECT Int(Count([Email])) as [ResultFm].[nnum] FROM [" & dest & "]" & _
        " WHERE [Language] <> 'X' ORDER BY [LastName];

If you are doing a count, first remove the order by section as its not doing anything helpful

Next calculate I, possibly using a DCount like this:
Code:
I = DCount("Email","[" & dest & "]")

Finally put the value of I into your form
Code:
Me.nnum = I
or
Code:
Forms!ResultFm.nnum=I

Or to do it in one step:
Code:
 Me.nnum=DCount("Email","[" & dest & "]")
 
Last edited:

exaccess

Registered User.
Local time
Today, 12:07
Joined
Apr 21, 2013
Messages
287
Your code makes little sense to me :

Code:
I = "SELECT Int(Count([Email])) as [ResultFm].[nnum] FROM [" & dest & "]" & _
        " WHERE [Language] <> 'X' ORDER BY [LastName];

If you are doing a count, first remove the where section as its not doing anything helpful

Next calculate I, possibly using a DCount like this:
Code:
I = DCount("Email","[" & dest & "]")

Finally put the value of I into your form
Code:
Me.nnum = I
or
Code:
Forms!ResultFm.nnum=I

Or to do it in one step:
Code:
 Me.nnum=DCount("Email","[" & dest & "]")
Yes but if I omit the where clause then how can I impose the Language <> 'X' filter?
 

isladogs

MVP / VIP
Local time
Today, 11:07
Joined
Jan 14, 2017
Messages
18,186
How about this:

Code:
Me.nnum=DCount("Email","[" & dest & "]","Language <> 'X'")
 

exaccess

Registered User.
Local time
Today, 12:07
Joined
Apr 21, 2013
Messages
287
How about this:

Code:
Me.nnum=DCount("Email","[" & dest & "]","Language <> 'X'")

OK I will try that but at the moment I am having another problem where "The button you clicked has not produced any event" or"...event refers to an external object". Where as I did not touch any other part of the application.
 

exaccess

Registered User.
Local time
Today, 12:07
Joined
Apr 21, 2013
Messages
287
OK I will try that but at the moment I am having another problem where "The button you clicked has not produced any event" or"...event refers to an external object". Where as I did not touch any other part of the application.
I tried yours. It does not work. But I have modified your proposal as below. It does not complain and runs. The problem is it gives I=0 whereas I know that there are at least 400 records in the table.
Code:
 J = DCount("EMAIL", "[" & dest & "]", "[Language] = 'X'")
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:07
Joined
May 7, 2009
Messages
19,175
I = 0, because you are saving it in another
variable, J.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:07
Joined
May 7, 2009
Messages
19,175
alias only applies to table and not to
variables.

this is a sample:
if you have a form (Form1), and the textbox
you want to update is Textbox1, set the Control
Source Property (Data) of Textbox1 to:

=DCount("Email", "[" & Dest & "]", "[Language]='X'")

on the Current Event of form Form1, you requery
the Textbox1:

Private Sub Form_Current()
On Error Resume Next
Me.Textbox1.Requery
End Sub
 
Last edited:

exaccess

Registered User.
Local time
Today, 12:07
Joined
Apr 21, 2013
Messages
287
alias only applies to table and not to
variables.

this is a sample:
if you have a form (Form1), and the textbox
you want to update is Textbox1, set the Control
Source Property (Data) of Textbox1 to:

=DCount("Email", "[" & Dest & "]", "[Language]='X'")

on the Current Event of form Form1, you requery
the Textbox1:

Private Sub Form_Current()
On Error Resume Next
Me.Textbox1.Requery
End Sub
It works like charme.
Thanks I sorted out the other problem by debug,compile. By the way I is not an alias of J. It is a programming habit. where some times is is necessary to have intermediate variables to reach a clear result. Here I = J.
 

Mirage Guaridan

New member
Local time
Today, 04:07
Joined
Jan 2, 2018
Messages
3
try this ( NZ ---- )
for exampele

Code:
I =Nz(DCount("Email", "[" & Dest & "]", "[Language]='X'"),0)
 

Users who are viewing this thread

Top Bottom