Help with RecordCount Code

[JHNielson]

I have a table [ETL_TBL]
and I want to do a count of the number of records where the field [ERR_DESC] is not Null


I have been trying to write a recordcount code. can anyone help me in how to write this. My specific problems are in writing the criteria.

[cmrhema]

I have a table [ETL_TBL]
and I want to do a count of the number of records where the field [ERR_DESC] is not Null


I have been trying to write a recordcount code. can anyone help me in how to write this. My specific problems are in writing the criteria.

Try out this. I am assuming that you are using ADODB
[HTML]
If MyRs.State = adStateOpen Then MyRs.Close
'Myrs is the recordset
MyRs.CursorLocation = adUseClient
MyRs.Open "select count(*) from ETL_TBL where ERR_DESC IS NOT NULL", MyCon, adOpenDynamic, adLockOptimistic
'Mycon is the connection
Text1.Text = MyRs.Fields(0)[/HTML]

Good luck

[SanjuMtr]

I have a table [ETL_TBL]
and I want to do a count of the number of records where the field [ERR_DESC] is not Null


I have been trying to write a recordcount code. can anyone help me in how to write this. My specific problems are in writing the criteria.

  dim rsTemp as new ADODB.recordset
  dim conntemp as new ADODb.Connection
 ' set the Connection String to the Connection Object.
 if rstemp.state=adstateopen then rstemp.close
 rstemp.open"Select count(*) as cnt from ETL_TBL where ERR_DESC<>Null",contemp
 
 Msgbox"total Record=" & rstemp.fields("cnt").value
 

[JHNielson]

  dim rsTemp as new ADODB.recordset
  dim conntemp as new ADODb.Connection
 ' set the Connection String to the Connection Object.
 if rstemp.state=adstateopen then rstemp.close
 rstemp.open"Select count(*) as cnt from ETL_TBL where ERR_DESC<>Null",contemp
 
 Msgbox"total Record=" & rstemp.fields("cnt").value
 

Thanks for everyone's great help.

I tried this code:

Function REC_COUNT_TEST_2()
 
  Dim rsTemp As New ADODb.Recordset
  Dim conntemp As New ADODb.Connection
 ' set the Connection String to the Connection Object.
 If rsTemp.State = adStateOpen Then rsTemp.Close
 rsTemp.Open "select count(*) from ETL_TBL where ERR_DESC IS NOT NULL", MyCon, adOpenDynamic, adLockOptimistic
 
 MsgBox "total Record=" & rsTemp.Fields("cnt").Value
 
End Function
 

But it gives me an error:

'Arguments are of the wrong type, are out of acceptable range, or are in conflict with another"

Any ideas?

again, thanks os much

[Killer42]

What is the field type of field ERR_DESC? Text?

Also, just in case we're overlooking something, which line produces the error?

[JHNielson]

What is the field type of field ERR_DESC? Text?

Also, just in case we're overlooking something, which line produces the error?

Yes, it is a text field

[SanjuMtr]

Thanks for everyone's great help.

I tried this code:

Function REC_COUNT_TEST_2()
 
  Dim rsTemp As New ADODb.Recordset
  Dim conntemp As New ADODb.Connection
 ' set the Connection String to the Connection Object.
 If rsTemp.State = adStateOpen Then rsTemp.Close
 rsTemp.Open "select count(*) from ETL_TBL where ERR_DESC IS NOT NULL", MyCon, adOpenDynamic, adLockOptimistic
 
 MsgBox "total Record=" & rsTemp.Fields("cnt").Value
 
End Function
 

But it gives me an error:

'Arguments are of the wrong type, are out of acceptable range, or are in conflict with another"

Any ideas?

again, thanks os much

hi,
you use the code

rsTemp.Open "select count(*) from ETL_TBL where ERR_DESC IS NOT NULL", MyCon, adOpenDynamic, adLockOptimistic
 

Again u use the code

MsgBox "total Record=" & rsTemp.Fields("cnt").Value
 

Now U tell me where is the "cnt" field???--->thats the error
just use

MsgBox "total Record=" & rsTemp.Fields(0).Value
 

check it. good luck.