• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 647
  • Last Modified:

Use DCOUNT, etc. with dynamic SQL statement in VBA

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

  • 2
1 Solution
Rey Obrero (Capricorn1)Commented:
you can use this

If DCount("Zip", "tbl_PostalCodes_US","[Zip]='" & Left(Me.[Zip],5) &"'") > 1 Then
DHompsterAuthor Commented:
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?
Rey Obrero (Capricorn1)Commented:
No you can not use a select statement, it has to be a table or a saved query as the domain.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now