By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,987 Members | 967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,987 IT Pros & Developers. It's quick & easy.

Help with RecordCount Code

100+
P: 121
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.
Apr 19 '07 #1
Share this Question
Share on Google+
6 Replies


100+
P: 375
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
Apr 19 '07 #2

P: 47
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.
Expand|Select|Wrap|Line Numbers
  1.   dim rsTemp as new ADODB.recordset
  2.   dim conntemp as new ADODb.Connection
  3.  ' set the Connection String to the Connection Object.
  4.  if rstemp.state=adstateopen then rstemp.close
  5.  rstemp.open"Select count(*) as cnt from ETL_TBL where ERR_DESC<>Null",contemp
  6.  
  7.  Msgbox"total Record=" & rstemp.fields("cnt").value
  8.  
Apr 19 '07 #3

100+
P: 121
Expand|Select|Wrap|Line Numbers
  1.   dim rsTemp as new ADODB.recordset
  2.   dim conntemp as new ADODb.Connection
  3.  ' set the Connection String to the Connection Object.
  4.  if rstemp.state=adstateopen then rstemp.close
  5.  rstemp.open"Select count(*) as cnt from ETL_TBL where ERR_DESC<>Null",contemp
  6.  
  7.  Msgbox"total Record=" & rstemp.fields("cnt").value
  8.  
Thanks for everyone's great help.

I tried this code:
Expand|Select|Wrap|Line Numbers
  1. Function REC_COUNT_TEST_2()
  2.  
  3.   Dim rsTemp As New ADODb.Recordset
  4.   Dim conntemp As New ADODb.Connection
  5.  ' set the Connection String to the Connection Object.
  6.  If rsTemp.State = adStateOpen Then rsTemp.Close
  7.  rsTemp.Open "select count(*) from ETL_TBL where ERR_DESC IS NOT NULL", MyCon, adOpenDynamic, adLockOptimistic
  8.  
  9.  MsgBox "total Record=" & rsTemp.Fields("cnt").Value
  10.  
  11. End Function
  12.  
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
Apr 19 '07 #4

Expert 5K+
P: 8,434
What is the field type of field ERR_DESC? Text?

Also, just in case we're overlooking something, which line produces the error?
Apr 20 '07 #5

100+
P: 121
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
Apr 20 '07 #6

P: 47
Thanks for everyone's great help.

I tried this code:
Expand|Select|Wrap|Line Numbers
  1. Function REC_COUNT_TEST_2()
  2.  
  3.   Dim rsTemp As New ADODb.Recordset
  4.   Dim conntemp As New ADODb.Connection
  5.  ' set the Connection String to the Connection Object.
  6.  If rsTemp.State = adStateOpen Then rsTemp.Close
  7.  rsTemp.Open "select count(*) from ETL_TBL where ERR_DESC IS NOT NULL", MyCon, adOpenDynamic, adLockOptimistic
  8.  
  9.  MsgBox "total Record=" & rsTemp.Fields("cnt").Value
  10.  
  11. End Function
  12.  

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
Expand|Select|Wrap|Line Numbers
  1. rsTemp.Open "select count(*) from ETL_TBL where ERR_DESC IS NOT NULL", MyCon, adOpenDynamic, adLockOptimistic
  2.  
Again u use the code
Expand|Select|Wrap|Line Numbers
  1. MsgBox "total Record=" & rsTemp.Fields("cnt").Value
  2.  
Now U tell me where is the "cnt" field???--->thats the error
just use
Expand|Select|Wrap|Line Numbers
  1. MsgBox "total Record=" & rsTemp.Fields(0).Value
  2.  
check it. good luck.
Apr 20 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.