Convert sql count to integer

[exaccess]

How can I convert the result of a SELECT COUNT statement to integer so that I can use in vba?

 

[arnelgp]

The result is already numeric.
If you need further, cast it to Int() function

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

 

[exaccess]

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:

 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]

Yiu lack a recordset for that to work:

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

 

[exaccess]

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]

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]

Your code makes little sense to me :

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:

I = DCount("Email","[" & dest & "]")

Finally put the value of I into your form

Me.nnum = I

or

Forms!ResultFm.nnum=I

Or to do it in one step:

 Me.nnum=DCount("Email","[" & dest & "]")

 

[exaccess]

Your code makes little sense to me :

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:

I = DCount("Email","[" & dest & "]")

Finally put the value of I into your form

Me.nnum = I

or

Forms!ResultFm.nnum=I

Or to do it in one step:

 Me.nnum=DCount("Email","[" & dest & "]")

Yes but if I omit the where clause then how can I impose the Language <> 'X' filter?

 

[isladogs]

How about this:

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

 

[exaccess]

How about this:

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]

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.

 J = DCount("EMAIL", "[" & dest & "]", "[Language] = 'X'")

 

[arnelgp]

I = 0, because you are saving it in another
variable, J.

 

[exaccess]

I = 0, because you are saving it in another
variable, J.

I am so sorry am using I as alias to J. In any case unfortunately it does not work.

 

[arnelgp]

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

 

[exaccess]

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]

try this ( NZ ---- )
for exampele

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

 

[isladogs]

try this ( NZ ---- )
for exampele

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

Nz isn't needed for DCount - if there are no records, the result = 0