Type Mismatch Error

C

Can

For both lines with Set I get a type mismatch error. I'm confused, I just
copied my SQL statement from the recordsource. Even though
Monthly_Balance_SchoolBoardID has ID at the end it's a text field.

Thanks in advance for you help.

' Find the records that matches the control.
Dim dbs As Database
Dim rstMonthlyBalances As Recordset

Set dbs = CurrentDb

' Open a Recordset object that selects records from a
' table based on the schoolboard.
If (IsNull([Select_SchoolBoard]) = True) Or ([Select_SchoolBoard] = "")
Then
Set rstMonthlyBalances = dbs.OpenRecordset("SELECT
Monthly_Balances.Monthly_Balance_Month, SchoolBoard.SchoolBoard_Name,
Monthly_Balances.Monthly_Balance_Year,
IIf([Monthly_Balance_Month]='January',1,IIf([Monthly_Balance_Month]='February',2,IIf([Monthly_Balance_Month]='March',3,IIf([Monthly_Balance_Month]='April',4,IIf([Monthly_Balance_Month]='May',5,IIf([Monthly_Balance_Month]='June',6,IIf([Monthly_Balance_Month]='July',7,IIf([Monthly_Balance_Month]='August',8,IIf([Monthly_Balance_Month]='September',9,IIf([Monthly_Balance_Month]='October',10,IIf([Monthly_Balance_Month]='November',11,IIf([Monthly_Balance_Month]='December',12,Null))))))))))))
AS Balance_Month_Number, Monthly_Balances.Monthly_Balance_Amount,
Monthly_Balances.Monthly_Balance_SchoolBoardID " & _
" FROM SchoolBoard RIGHT JOIN Monthly_Balances ON
SchoolBoard.SchoolBoard_Identifier =
Monthly_Balances.Monthly_Balance_SchoolBoardID ORDER BY
SchoolBoard.SchoolBoard_Name, Monthly_Balances.Monthly_Balance_Year DESC ,
IIf([Monthly_Balance_Month]='January',1,IIf([Monthly_Balance_Month]='February',2,IIf([Monthly_Balance_Month]='March',3,IIf([Monthly_Balance_Month]='April',4,IIf([Monthly_Balance_Month]='May',5,IIf([Monthly_Balance_Month]='June',6,IIf([Monthly_Balance_Month]='July',7,IIf([Monthly_Balance_Month]='August',8,IIf([Monthly_Balance_Month]='September',9,IIf([Monthly_Balance_Month]='October',10,IIf([Monthly_Balance_Month]='November',11,IIf([Monthly_Balance_Month]='December',12,Null)))))))))))) DESC", dbOpenSnapshot)
Else
' Set rstMonthlyBalances = dbs.OpenRecordset("SELECT * FROM
Monthly_Balances WHERE Monthly_Balance_SchoolBoardID = '" &
Monthly_Balance_SchoolBoardID.Column(1) & "'", dbOpenSnapshot)
Set rstMonthlyBalances = dbs.OpenRecordset("SELECT
Monthly_Balances.Monthly_Balance_Month, SchoolBoard.SchoolBoard_Name,
Monthly_Balances.Monthly_Balance_Year,
IIf([Monthly_Balance_Month]='January',1,IIf([Monthly_Balance_Month]='February',2,IIf([Monthly_Balance_Month]='March',3,IIf([Monthly_Balance_Month]='April',4,IIf([Monthly_Balance_Month]='May',5,IIf([Monthly_Balance_Month]='June',6,IIf([Monthly_Balance_Month]='July',7,IIf([Monthly_Balance_Month]='August',8,IIf([Monthly_Balance_Month]='September',9,IIf([Monthly_Balance_Month]='October',10,IIf([Monthly_Balance_Month]='November',11,IIf([Monthly_Balance_Month]='December',12,Null))))))))))))
AS Balance_Month_Number, Monthly_Balances.Monthly_Balance_Amount,
Monthly_Balances.Monthly_Balance_SchoolBoardID " & _
" FROM SchoolBoard RIGHT JOIN Monthly_Balances ON
SchoolBoard.SchoolBoard_Identifier =
Monthly_Balances.Monthly_Balance_SchoolBoardID WHERE
(((Monthly_Balances.Monthly_Balance_SchoolBoardID) = '" &
Select_SchoolBoard.Column(1) & "')) ORDER BY SchoolBoard.SchoolBoard_Name,
Monthly_Balances.Monthly_Balance_Year DESC ,
IIf([Monthly_Balance_Month]='January',1,IIf([Monthly_Balance_Month]='February',2,IIf([Monthly_Balance_Month]='March',3,IIf([Monthly_Balance_Month]='April',4,IIf([Monthly_Balance_Month]='May',5,IIf([Monthly_Balance_Month]='June',6,IIf([Monthly_Balance_Month]='July',7,IIf([Monthly_Balance_Month]='August',8,IIf([Monthly_Balance_Month]='September',9,IIf([Monthly_Balance_Month]='October',10,IIf([Monthly_Balance_Month]='November',11,IIf([Monthly_Balance_Month]='December',12,Null)))))))))))) DESC", dbOpenSnapshot)
End If

rstMonthlyBalances.Close
dbs.Close
 
K

Ken Snell MVP

There are two types of Recordset objects in ACCESS: DAO and ADODB.

You want the DAO one for this code. So change your Dim statement to this:
Dim rstMonthlyBalances As DAO.Recordset
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Can said:
For both lines with Set I get a type mismatch error. I'm confused, I just
copied my SQL statement from the recordsource. Even though
Monthly_Balance_SchoolBoardID has ID at the end it's a text field.

Thanks in advance for you help.

' Find the records that matches the control.
Dim dbs As Database
Dim rstMonthlyBalances As Recordset

Set dbs = CurrentDb

' Open a Recordset object that selects records from a
' table based on the schoolboard.
If (IsNull([Select_SchoolBoard]) = True) Or ([Select_SchoolBoard] = "")
Then
Set rstMonthlyBalances = dbs.OpenRecordset("SELECT
Monthly_Balances.Monthly_Balance_Month, SchoolBoard.SchoolBoard_Name,
Monthly_Balances.Monthly_Balance_Year,
IIf([Monthly_Balance_Month]='January',1,IIf([Monthly_Balance_Month]='February',2,IIf([Monthly_Balance_Month]='March',3,IIf([Monthly_Balance_Month]='April',4,IIf([Monthly_Balance_Month]='May',5,IIf([Monthly_Balance_Month]='June',6,IIf([Monthly_Balance_Month]='July',7,IIf([Monthly_Balance_Month]='August',8,IIf([Monthly_Balance_Month]='September',9,IIf([Monthly_Balance_Month]='October',10,IIf([Monthly_Balance_Month]='November',11,IIf([Monthly_Balance_Month]='December',12,Null))))))))))))
AS Balance_Month_Number, Monthly_Balances.Monthly_Balance_Amount,
Monthly_Balances.Monthly_Balance_SchoolBoardID " & _
" FROM SchoolBoard RIGHT JOIN Monthly_Balances ON
SchoolBoard.SchoolBoard_Identifier =
Monthly_Balances.Monthly_Balance_SchoolBoardID ORDER BY
SchoolBoard.SchoolBoard_Name, Monthly_Balances.Monthly_Balance_Year DESC ,
IIf([Monthly_Balance_Month]='January',1,IIf([Monthly_Balance_Month]='February',2,IIf([Monthly_Balance_Month]='March',3,IIf([Monthly_Balance_Month]='April',4,IIf([Monthly_Balance_Month]='May',5,IIf([Monthly_Balance_Month]='June',6,IIf([Monthly_Balance_Month]='July',7,IIf([Monthly_Balance_Month]='August',8,IIf([Monthly_Balance_Month]='September',9,IIf([Monthly_Balance_Month]='October',10,IIf([Monthly_Balance_Month]='November',11,IIf([Monthly_Balance_Month]='December',12,Null))))))))))))
DESC", dbOpenSnapshot)
Else
' Set rstMonthlyBalances = dbs.OpenRecordset("SELECT * FROM
Monthly_Balances WHERE Monthly_Balance_SchoolBoardID = '" &
Monthly_Balance_SchoolBoardID.Column(1) & "'", dbOpenSnapshot)
Set rstMonthlyBalances = dbs.OpenRecordset("SELECT
Monthly_Balances.Monthly_Balance_Month, SchoolBoard.SchoolBoard_Name,
Monthly_Balances.Monthly_Balance_Year,
IIf([Monthly_Balance_Month]='January',1,IIf([Monthly_Balance_Month]='February',2,IIf([Monthly_Balance_Month]='March',3,IIf([Monthly_Balance_Month]='April',4,IIf([Monthly_Balance_Month]='May',5,IIf([Monthly_Balance_Month]='June',6,IIf([Monthly_Balance_Month]='July',7,IIf([Monthly_Balance_Month]='August',8,IIf([Monthly_Balance_Month]='September',9,IIf([Monthly_Balance_Month]='October',10,IIf([Monthly_Balance_Month]='November',11,IIf([Monthly_Balance_Month]='December',12,Null))))))))))))
AS Balance_Month_Number, Monthly_Balances.Monthly_Balance_Amount,
Monthly_Balances.Monthly_Balance_SchoolBoardID " & _
" FROM SchoolBoard RIGHT JOIN Monthly_Balances ON
SchoolBoard.SchoolBoard_Identifier =
Monthly_Balances.Monthly_Balance_SchoolBoardID WHERE
(((Monthly_Balances.Monthly_Balance_SchoolBoardID) = '" &
Select_SchoolBoard.Column(1) & "')) ORDER BY SchoolBoard.SchoolBoard_Name,
Monthly_Balances.Monthly_Balance_Year DESC ,
IIf([Monthly_Balance_Month]='January',1,IIf([Monthly_Balance_Month]='February',2,IIf([Monthly_Balance_Month]='March',3,IIf([Monthly_Balance_Month]='April',4,IIf([Monthly_Balance_Month]='May',5,IIf([Monthly_Balance_Month]='June',6,IIf([Monthly_Balance_Month]='July',7,IIf([Monthly_Balance_Month]='August',8,IIf([Monthly_Balance_Month]='September',9,IIf([Monthly_Balance_Month]='October',10,IIf([Monthly_Balance_Month]='November',11,IIf([Monthly_Balance_Month]='December',12,Null))))))))))))
DESC", dbOpenSnapshot)
End If

rstMonthlyBalances.Close
dbs.Close
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Mapping strings to integers 6
Report with disappearing subreports - null values 3
vba where Error 3131 8
Month days over a time period 5
Formula to pick up on future data when table is updated 3
Aggregate Function in a query 4
How to end Excel Process 4
Stand Deviation Expression 6

Top