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

DHompsterAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
you can use this

If DCount("Zip", "tbl_PostalCodes_US","[Zip]='" & Left(Me.[Zip],5) &"'") > 1 Then
0
 
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?
0
 
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.
0
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.

All Courses

From novice to tech pro — start learning today.