Discuss "Query Counts" in the Microsoft Access forum.

Jaykappy · #1 May 13th, 2008, 01:40 PM

I am trying to get a record count from a Query.
I want to get this count to a variable and then place it in a textbox...

I have a dropdown box on my form that allows the user to select a Field Name from the table.
It then populates a second dropdown with the unique values from that field, which the user then selects

They can build 4 querys like this...although they might only write one...returning all the unique values from a field....

I then put them together like this....

ARTICLE=SHIRT AND CATEG=STORE AND DISPO=HOLD;

the "critStr" is a variable that holds the string above

I THEN Was trying to run this:

Code:

Dim MySQL3 As String
MySQL3 = "SELECT Count(*) as numRecords FROM dbo_Core2 WHERE " & critStr & ";"
DoCmd.RunSQL MySQL3
MsgBox MySQL3

The message box returns this....but I dont think this was right...I dont know how to get the query to run and get the value?????

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

I THEN started with this....BUT the below code only returns a value of 1...I know I have more records than that...

I DONT knwo what I am doing right now.....All I am trying to do is run a query that gives me a count from a variable holding the string...

PLease help....

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"

Jaykappy · #2 May 13th, 2008, 01:49 PM

I am also trying this...

I cant figure this out....I dotn think this is that hard to accomplish...

Code:

Dim LTotal As String

'LTotal = DCount("ARTICLE", "dbo_Core2", critStr)
'LTotal = DCount("ARTICLE", "dbo_Core2", " & critStr & ")
LTotal = DCount("ARTICLE", "dbo_Core2", "' & critStr & '")

MsgBox LTotal

Wolffy · #3 May 13th, 2008, 01:50 PM

In your first instance, you will need to put quotes around the values in your Where clause, as in:

Code:

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

In you second case, you're getting 1 because of:

Code:

MyRecordCount = RCountRS.RecordCount

which is the number of records returned, not the value.

mehere · #4 May 13th, 2008, 01:51 PM

is this just what you placed here or is this actually how it is

Code:

WHERE ARTICLE=SHIRT AND CATEG=STORE AND DISPO=HOLD

because your values should have single quotes around them

Code:

WHERE ARTICLE='SHIRT' AND CATEG='STORE' AND DISPO='HOLD'

and are you positive that you have more than one record that has those values?

Jaykappy · #5 May 13th, 2008, 01:58 PM

FIRST EXAMPLE:

ARTICLE=SHIRT AND CATEG=STORE AND DISPO=HOLD;

This is exaclty how it is returning the string in the variable......so it seems that I am missing the ' ' ..I will get those in and test again...

SECOND EXAMPLE:
where I am getting a return value of 1...

I am looking for the number of records that meet the query...

This should return all the values in the table right?

Code:

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

Wolffy · #6 May 13th, 2008, 02:05 PM

RecordSet.RecordCount returns the number of records in the RecordSet -- which for your query will always be 1.

Jaykappy · #7 May 13th, 2008, 02:08 PM

I changed the string variable (critStr) and now it reads with ' '

DISPO = 'HOLD' AND ...........

Although I am not sure how to run the query and get it to a variable...

I do the below and I still get an error:

RUn Time error 2342
A RunSQL action requires an argument consisting of an SQL statement

This is exacly what is being displayed int he MEssage Box:

SELECT Count(*) as numRecords FROM dbo_Core2 WHERE CATEG = 'NARCOTICS' AND DISPO = 'HOLD' AND ARTICLE = 'MARJIUANA';

Code:

Dim MySQL3 As String
MySQL3 = "SELECT Count(*) as numRecords FROM dbo_Core2 WHERE " & critStr & ";"
MsgBox MySQL3
DoCmd.RunSQL MySQL3
MsgBox MySQL3

Jaykappy · #8 May 13th, 2008, 02:10 PM

How can I change that to get the number of records return from the query?

--------------------------------------------------------------------------------

RecordSet.RecordCount returns the number of records in the RecordSet -- which for your query will always be 1.

Wolffy · #9 May 13th, 2008, 02:25 PM

Try something like

Code:

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

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

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

Jaykappy · #10 May 13th, 2008, 02:35 PM

Its not liking this line..

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

I tried 'HOLD'";" but thats not it....

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
SQL VBA Query	Jaykappy	Microsoft Access	12	May 8th, 2008 09:30 AM
[ASP/VBScript] Using Dropdown List Value in Database SQL Query	richyrich	Code Samples	0	April 2nd, 2008 10:05 AM

Discuss "Query Counts" in the Microsoft Access forum.

Microsoft Access - Microsoft Access is a database for small to medium applications. Learn tips and tricks and best database practices here.