Solved

Use DCOUNT, etc. with dynamic SQL statement in VBA

Posted on 2009-04-13
3
595 Views
Last Modified: 2012-05-06
In a VBA module, I need to get a count of records from a particular recordset, not a table.  Is there some way I can embed the SQL statement in the DCount instead of using a table name?  

Something like the Code sample below?


If DCount("Zip", "SELECT tbl_PostalCodes_US.Zip FROM tbl_PostalCodes_US WHERE (((tbl_PostalCodes_US.Zip)=Left('" & Me.[Zip] & "',5)));") > 1 Then

Open in new window

0
Comment
Question by:DHompster
  • 2
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 24134678
you can use this

If DCount("Zip", "tbl_PostalCodes_US","[Zip]='" & Left(Me.[Zip],5) &"'") > 1 Then
0
 

Author Closing Comment

by:DHompster
ID: 31569767
Duh!  Thanks!  You're solution is perfect for my example.  Unfortunately, I gave a lame example.  What about the idea of substituting a SQL statement (a SELECT stmt) in the place of the table name?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24134920
No you can not use a select statement, it has to be a table or a saved query as the domain.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Check a field in Access on a Timer 1 28
SQL to JSON 14 30
access query - field that substitutes characters 1 21
error in oracle form 11 17
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…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

679 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