Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 21st, 2017, 12:58 PM
Registered User
Join Date: Aug 2017
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default thanks alot worked

Originally Posted by wscheiman View Post
Here's another option that I use quite often using a field alias:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intResult As Integer
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT COUNT(*) As RecordCount FROM tblProducts"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapShot)

intResult = rs("RecordCount")


Utilizing an alias can be beneficial when running these types
of queries. You can use any value you'd like for the alias
name as long as it follows the rules for column names in a

If you are using VBA within Access you can also use a domain
function. Three of the most used as called DLookup, DCount,
and DSum. In this case, DCount() will give you the record
count like this:

intResult = DCount("ProdID", "tblProducts", "")

Here, you can use any valid existing column name from the table
in the first argument.

Both options should do exactly what you need. If you haven't
already, you may want to look in MS-Access online help for more
info on these 3 domain functions listed above (there are others
as well starting with D*) - I still use them quite extensively
myself in end-user delivered applications.

Best Wishes and Good Luck.
you saved me god bless you
thanks alottt

Similar Threads
Thread Thread Starter Forum Replies Last Post
Docmd.runsql anne.burrows VB How-To 2 October 25th, 2006 08:21 AM
DoCmd.RunSQL dates and time TarkaDahl Access VBA 3 May 11th, 2006 11:19 AM
Removing the MsgBox in DoCmd.RunSQL Update arfa Access 2 March 24th, 2006 08:05 PM
docmd.runsql "select RodMead Access VBA 2 July 31st, 2004 02:55 PM
Help with DoCmd.RunSQL command ricmar Access VBA 3 July 21st, 2004 03:32 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.