Type Mis Match 2

[ninash]

Hi All,

Stuck on a type mismatch error generated by the following

curKey = Forms![Report Writing Form]![ReportKey]
Set rstObj = CurrentDb.OpenRecordset("SELECT * FROM Objectives WHERE ReportKey = '" & curKey & "'&quot

The Initial Report Key is a use of Cstr(now())
My understanding of this is that a long format date will be pasted as a sting into whatever field I assign it to.
All other refs to report key are a copy of that initial field.

Another thing I have noticed is even calling the record set without a where statement I still get the type mis match error.

If there is anyone out there that can help PLEASE Do and save me from going nuts

Thanks in advance
Tony

 

[wjmsm]

It is difficult to tell from here but how are rstObj and curKey defined.....

for example
dim rstObj as recordset
dim curKey as string

 

[Databaseguy]

Well, using Hungarian, curKey would denote currency. If you want Date/Time, the proper name would be dtmKey.

You're using the cstr function, which converts the variable into a string. A string is not a date.

If the underlying table field ReportKey is a date, that would be you're problem.

Lastly, date/time can be tricky. If you're storing date/time, the actual value is something like #2003/03/04 11:46:00 AM#. In a query, that will not match #03/04/2003#.



Tyrone Lumley
augerinn@gte.net

 

[PaulBricker]

You don't show your declarations for the recordset variable, but try this
Dim rstObj as DAO.Recordset

Then proceed from there with the rest of your code. That may do it for you.

Paul

 

[ninash]

Sorry Guys
Declarations are
rstObj as Recordset
curKey as String

But I have change the key now to get away from the date problem, It now has a client code attached to it to make it a string. CurKey is now directly referenced from the active form.

However even though I am now looking at a string to string comparision I still am getting a type mismatch error

Code as it stands ( please ignore the other dims)

Dim db As Database
Dim rstObj As Recordset
Dim rstObjListed As Recordset
Dim objWord As Word.Application
Dim SaveNameAs As String
Dim tbl As Table
Dim rng 'Dim rng As Range
Dim TitleRow As Row
Dim rText
Dim cCells As Integer
Dim cColumns As Integer
Dim cRows As Integer
Dim iNextCol As Integer
Dim iNextRow As Integer
Dim iNextCell As Integer
Dim RowsNeeded As Integer
Dim ObjListed(45) As Variant
Dim CountTrue As Integer

db = CurrentDb
'Create Record Set for Objectives Table
Set rstObj = db.OpenRecordset("SELECT * from Objectives where [ReportKey] = '" & Forms![Report Writing Form]![ReportKey] & "'&quot

Even if I remove the where I again get a type mismatch

Any further thoughts????

 

[PaulBricker]

I don't mean to harp on the issue, but you will get a Type 13 Mismatch if you are using A2000 and you don't have the declarations set up properly(don't know about A2002). You should have the DAO 3.6 Object Library selected in your References and then you should use
Dim db As DAO.Database
Dim rstObj As DAO.Recordset
Dim rstObjListed As DAO.Recordset
for your first 3 declarations.

Paul

 

[ninash]

Sorry Paul
I have done that and the problem has been sorted out.

I was wondering if you might be able to explain why I have not had to do that in a Access 97 Database in the past.

Regards
Tony

Ps. Thanks for the help have a Star

 

[PaulBricker]

Well I can tell you what I know and maybe it will help. When you create a database in Access 2000, the ADO library is included in the References by default and the DAO library isn't selected. In 97 I think it's just the opposite. DAO is included and the ADO library is excluded. Now in 2000, if you select the DAO library and it's Priority level in the References list is lower than the ADO library, Access looks in the ADO library to reference Objects before it looks in the DAO library. If you declare your Objects as DAO objects (i.e. DAO.Recordset) then Access doesn't look in the ADO library at all. Here's where I'm going to guess, I think the error is generated because of the way ADO looks at Recordsets. I'm not that well versed in ADO yet but I assume ADO wants to see something else included with the Declaration that causes the Type Mismatch error when it doesn't see it. By not declaring your Objects DAO, you send them to ADO and end up with the error.
Hope that doesn't make things worse for you.

Paul