Microsoft Access Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free Forums > Database > Microsoft Access Help
Reload this Page

Count Query

Discuss Count Query in the Microsoft Access Help forum on ASP Free.
Count Query Microsoft Access forum discussing questions and answers for the Access database. MS Access is used to handle small to medium database needs.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old May 13th, 2008, 10:30 AM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 700 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 5 Days 3 h 38 m 11 sec
Reputation Power: 22
Count Query
I can build a query with Access Query builder and get a number/count of the number of records that meet specific criteria...I then try and write that in VBA and get confused and it dosent work...

critStr is a string built by user input...
Basically like the select below...

Any thoughts?

ERROR:

Run time error 2342
A RunSQL action requires an argument consisting of an SQL Statement



TRYING THIS:

Dim MySQL3 As String

MySQL3 = "SELECT Count(*) as numRecords FROM dbo_Core2 WHERE " & critStr & ";"

DoCmd.RunSQL MySQL3

MsgBox MySQL3



THIS WORKS:

'SELECT Count(dbo_Core2.CASE_NO) AS CountOfCASE_NO, dbo_Core2.ARTICLE, dbo_Core2.CATEG, dbo_Core2.DISPO
'FROM dbo_Core2
'GROUP BY dbo_Core2.ARTICLE, dbo_Core2.CATEG, dbo_Core2.DISPO
'HAVING (((dbo_Core2.ARTICLE)="SHIRT") AND ((dbo_Core2.CATEG)="STORE") AND ((dbo_Core2.DISPO)="HOLD"));
Last edited by Jaykappy : May 13th, 2008 at 11:59 AM.
Reply With Quote
  #2  
Old May 13th, 2008, 11:58 AM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 700 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 5 Days 3 h 38 m 11 sec
Reputation Power: 22
This is what the message box returns...

SELECT Count(*) numRecords FROM db_Core2 WHERE ARTICLE=SHIRT AND CATEG=STORE AND DISPO=HOLD;

BUT it errors out...

Thoughts
Reply With Quote
  #3  
Old May 13th, 2008, 12:02 PM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 700 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 5 Days 3 h 38 m 11 sec
Reputation Power: 22
Just looking to get the query to work and assign it to a varriable so I can populate a text box...
Reply With Quote
  #4  
Old May 13th, 2008, 12:29 PM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 700 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 5 Days 3 h 38 m 11 sec
Reputation Power: 22
This is what I am trying but I keep getting 1 for a return value...I have way more than that...



Code:
Dim RCountDB As Database
Dim RCountRS As Recordset
Dim SQLstmt As String
'Dim Count As Integer
Dim MyRecordCount As Integer

SQLstmt = "SELECT Count(*) As CNT FROM dbo_Core2"

Set RCountDB = CurrentDb
Set RCountRS = RCountDB.OpenRecordset(SQLstmt)
'Count = RCountRS.RecordCount

'rst.MoveLast
MyRecordCount = 0
If RCountRS.EOF <> True Then
    RCountRS.MoveLast
    RCountRS.MoveFirst
    MyRecordCount = RCountRS.RecordCount
End If

MsgBox "Ending"
MsgBox MyRecordCount
MsgBox "END"
Reply With Quote
  #5  
Old May 13th, 2008, 01:25 PM
dykebert dykebert is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2008
Posts: 223 dykebert User rank is Second Lieutenant (5000 - 10000 Reputation Level)dykebert User rank is Second Lieutenant (5000 - 10000 Reputation Level)dykebert User rank is Second Lieutenant (5000 - 10000 Reputation Level)dykebert User rank is Second Lieutenant (5000 - 10000 Reputation Level)dykebert User rank is Second Lieutenant (5000 - 10000 Reputation Level)dykebert User rank is Second Lieutenant (5000 - 10000 Reputation Level)dykebert User rank is Second Lieutenant (5000 - 10000 Reputation Level) 
Time spent in forums: 1 Day 23 h 15 m 26 sec
Reputation Power: 65
You need to specify a field for the count function. For best results you want to specify a field that is ALWAYS populated. I typically use the primary key field.
Reply With Quote
  #6  
Old May 13th, 2008, 02:04 PM
rpeare rpeare is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Jan 2008
Posts: 817 rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 6 Days 3 h 31 m 27 sec
Reputation Power: 172
Code:
Dim RCountDB As Database
Dim RCountRS As Recordset
Dim SQLstmt As String
Dim MyRecordCount As Integer

SQLstmt = "SELECT Count(*) As CNT FROM dbo_Core2"

Set RCountDB = CurrentDb
Set RCountRS = RCountDB.OpenRecordset(SQLstmt)

rst.MoveLast
myrecordcount = RCountRs.Recordcount

MsgBox "Ending"
MsgBox MyRecordCount
MsgBox "END"



The problem is that you were moving to the last record then moving back to the first record before you got the count and when you moved back to the first record you're always going to get a count of 1.

EDIT: one other note, when you loop through a data set you will also have to increment the record as in

RCountRS.movenext

otherwise it will just loop through the same record forever.
__________________
----------------
If we've helped you and you have solved your problem please post that it's been resolved so we know! The suspense kills me!
Reply With Quote
  #7  
Old May 13th, 2008, 03:12 PM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 700 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 5 Days 3 h 38 m 11 sec
Reputation Power: 22
Got it......Thanks to you all and developer barn


Code:
Dim RCountDB As Database
Dim RCountRS As Recordset
Dim SQLstmt As String
Dim MyRecordCount As Integer

'SQLstmt = "SELECT Count(*) As numRecords FROM dbo_Core2 WHERE ARTICLE='MARIJUANA' AND CATEG='NARCOTICS' AND DISPO='HOLD';"

SQLstmt = "SELECT Count(*) As numRecords FROM dbo_Core2 WHERE " & critStr & ";"

Set RCountDB = CurrentDb()
Set RCountRS = RCountDB.OpenRecordset(SQLstmt)
MyRecordCount = RCountRS("numRecords")
RCountRS.Close

If MyRecordCount > 0 Then
    'MsgBox MyRecordCount
    MsgBox " There were " & MyRecordCount & " Records that match your criteria."
    Text141 = MyRecordCount
Else
    MsgBox " There were no Records that matched your query"
End If
Reply With Quote
Reply

Viewing: ASP Free Forums > Database > Microsoft Access Help > Count Query

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway. 
Stay green...Green IT