ACCESS FEED: RE: [MS_AccessPros] help with coded select

Minggu, 01 Juli 2012

RE: [MS_AccessPros] help with coded select

Jackie-

You're not explicitly declaring your object variables: Dim rs As DAO.Recordset.
If you have the ADODB library loaded in your project references and it's in
front of DAO, then that's the problem. You're getting an ADODB recordset object
that won't work.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jdiek_2000
Sent: Sunday, July 01, 2012 9:55 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] help with coded select

Hi,
I have the following code. I keep getting Type Mismatch. I have tried numerous
solutions but can't get it to work. Suggests???

Thanks,
Jackie

Dim dbs As Database
Dim rs As Recordset

Dim selpart As String
Dim selfrom As String
Dim selhave As String
Dim ssql As String

Set dbs = DBEngine.Workspaces(0).Databases(0)

curyr = CStr(Right(Year(Date), 2))

selpart = "SELECT Count(Left([companies]![coid],2)) AS totyr,
Left([companies]![coid],2) AS yr"
'selpart = "select coid AS yr"
selfrom = " FROM companies GROUP BY Left([companies]![coid],2)"
selhave = " having (((Left([companies]![coid],2))='" & curyr & "'));"

ssql = selpart & selfrom & selhave

set rs = dbs.OpenRecordset(ssql)

[Non-text portions of this message have been removed]