Data Type Mismatch

[wack]

Hi all!

I am hoping you can help me out here.

I need to automate a query. This query is based on 3
previous queries. Two of these queries have parameters
(the same parameter) which is being passed through a
form. The button is on_click and it exports the final
query to the an Excel template.

I have the following code:

Set qdf = CurrentDb.QueryDefs("qryFindMarketCompetitors02")
qdf.Sql = "SELECT b.ID, SUM(b.COUNT) as TID_Disch FROM
tblMarket02 a, tblMarket02 b WHERE a.zip = b.zip and a.ID
= " & Forms![frmExport]![txtID] & " GROUP BY b.ID;"

Set qdf = CurrentDb.QueryDefs("qryTotalMarketDischarges")
qdf.Sql = "SELECT a.Year, SUM(a.Discharges) as TMKT_Disch
FROM tblAll_AllYears a, tblHCODisch_AllYears b WHERE a.zip
= b.zip and b.ID = " & Forms![frmExport]![txtHospitalID]
& " GROUP BY a.Year;"

Set rs = CurrentDb.OpenRecordset
("qryInpatientMarketShare2_Final02")

I get the following error: Run-time error '3464' Data
Type Mismatch in the Criteria

and the debugger points to the final line of code here.

Any ideas? I have check and the ID and Year fields are
all text.

 

[Douglas J. Steele]

Let me guess. Access 2000 (or 2002), and you added a reference to DAO, but
didn't remove the reference to ADO. When you declared rs, you declared it as
Dim rs As Recordset.

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rs as DAO.Recordset (to guarantee an ADO recordset, you'd use Dim rs
As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

 

[yzhu014]

Thank you, Douglas, your answer is definitely correct.

It solved my question as well.