Solved

Counting records in a querydef

Posted on 1998-05-04
6
1,683 Views
Last Modified: 2010-07-27
I have a problem when i count records in a querydef.  It works ok when i do not have a 'WHERE' call in the querydef and correctly gives me the number of records, but when i have the 'WHERE' statement included the line set rst = querydf asks for another parameter. Any ideas
Thanks
here is the code that iam using

Dim dbs As Database, rst As Recordset
Dim qd As QueryDef

Set dbs = CurrentDb
Set qd = dbs.CreateQueryDef("", " SELECT * from Projects_ WHERE 'Field1' = [Forms]![Front page]![field1] ")
 
   

Set rst = qd.OpenRecordset()
   
    MsgBox rst.RecordCount
    rst.Close
    Set dbs = Nothing
0
Comment
Question by:tuck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 1

Expert Comment

by:Edithf
ID: 1961683
The reason why you are asked for another parameter is when you run the query you don't have the form "Front Page" open or you don't a field called [Forms]![Front page]![field1].

If you have that form open and the name field1 is on the form then this should work without asking you another parameter
0
 
LVL 12

Expert Comment

by:Trygve
ID: 1961684
Another problem with this syntax is that Access may misinterpret the forms... part as a parameter that is to be input by the user. A better way of writing your line is like this;

Set qd = dbs.CreateQueryDef("", " SELECT * from Projects_ WHERE 'Field1' = '" & [Forms]![Front page]![field1] & "'")

Your query is comparing the field on the form against the string value 'Field1'. If you want it to compare Field1 on the table against the field on the form you should write this.

Set qd = dbs.CreateQueryDef("", " SELECT * from Projects_ WHERE [Field1] = '" & [Forms]![Front page]![field1] & "'")

Note that the form-part has been extracted from the string to force access to look upon it as a reference to a field on the form. Also note the apostrophes on each side forcing access to look upon the value as a string value.

Hope this helps !
Trygve

0
 
LVL 1

Author Comment

by:tuck
ID: 1961685
Let me no how to transfer the points to trygve as this answer is the one that works thanks for the answer.
The forms were open and the fields were there.
Trygve answer seems most correct.

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 2

Expert Comment

by:marti
ID: 1961686
Ask him to post his comment as an answer
0
 

Expert Comment

by:boazm
ID: 1961687
tuck, I agree with Trygve's answer, there just one more tip:  opening a Recordset does not always gives the RecordCount property the proper value right away. In that case you have to perform rst.MoveLast before using rst.RecordCount

0
 
LVL 12

Accepted Solution

by:
Trygve earned 100 total points
ID: 1961688
Thanks good people !

boazm is right. To ensure getting the correct recordcount you should include the rst.movelast line.

Another way of getting hold of the recordscount is to use a DCount statement.

Dim NoOfRecs as long
NoOfRecs=DCount("[Field]","Projects_","[Field1] = '" & [Forms]![Front page]![field1] & "'")
MsgBox(NoOfRecs)

Have a nice day everyone !
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question