Data type mismatch in criteria expression. Where?

[nine]

I am trying to set a recordset, and I keep getting this data type mismatch error, but I cannot see what the problem is. Here's my code:

Set rstAccounts = dtbCurrent.CreateQueryDef("", "SELECT [Account ID], [Balance] FROM tblAccounts WHERE [Account ID]='" & txtAccountID.Value & "';").OpenRecordset

txt.AccountID.Value is 1, and when I hard code the value as 1 it works, so I'm not sure what the problem is. I've tried using CStr() to convert it to a string, CInt() to convert it to a integer (even though it already is one), with using both quotes, with only double quotes, and with only single quotes. It's driving me crazy, because i'm sure it's something really simple, but I'm not sure what else to try.


Thanks in advance.

 

[MarkK]

Not sure if it matters, but you don't need to create the querydef to open the recordset. Try...

Set rst = CurrentDb.OpenRecordset( _
  "SELECT [Account ID], Balance " & _
  "FROM tblAccounts " & _
  "WHERE [Account ID] = " & Me.txtAccountID)

Also, for readability I avoid naming anything that requires square brackets. Go for "AccountID"
Type mismatch could also be that your recordset is not DAO. Make sure you use...

Dim rst As DAO.Recordset

 

[Pat Hartman]

In case you didn't notice, lagbolt removed the single quotes around the AccountID. Only TEXT fields are enclosed in single or double quotes. Date fields are enclosed in pound signs (#). Numeric fields are not enclosed.

CInt("1") wouldn't work because AccountID is actually a Long Integer. So, CLng("1") probably would work even though the quotes are what caused the original problem.

 

[MarkK]

Thanks Pat, the single quotes are the main issue.
Mark

 

[nine]

Wow, I diddn't expect such a speedy responce. I sorted out the quotes, and now it works fine. Thanks, guys! You really helped me out.