Type mismatch and I havent a clue why

[jofarrell]

I have this SQl String that has worked on other ocassions but for some reason is giving me "Error 13 Type mismatch"

I have checked the database to make sure all variables i am handing in are same as database .. any clues?

Dim LastDay As Date
Dim FirstDay As Date
Dim rsAdmin As Recordset

MyYear = Right(Year(Date), 2)
MyName = "Tester"
LastDay = "01/" & cboMonth.Text + 1 & "/" & cboYear.Text
LastDay = Format(DateAdd("d", -1, LastDay), "dd/mm/yy&quot
FirstDay = "01/" & cboMonth.Text & "/" & MyYear
SQL = "Select * " _
& "FROM tblAdminDetails " _
& "Where tblAdminDetails.[CRA] = '" & myName & "'" _
& " AND tblAdminDetails.[Date1] BETWEEN #" & FirstDay & "# and #" & LastDay & "#"
Set rsAdmin = DbsNew.OpenRecordset(SQL, dbOpenDynaset)

Thanks for any help,

Joanne

 

[Scoty]

Joanne,
From what it looks like on LastDay variant you are using string concantiation. This makes this variant a string not a date try surrounding you assingment like you did on the next line after that with both the FirstDay and LastDay variables.

Scotty :

 

[woyler]

I assume that you are getting the error on this line:Set rsAdmin = DbsNew.OpenRecordset(SQL, dbOpenDynaset)
Is myName declared as a string?

 

[scorpio66]

Jo, which line is failing?

Something to check might be your international settings, as well as the international settings on the database server if it is different. I've been stung by US date formats before ...

Also, in your first LastDay line you add 1 to cboMonth.Text - which means you could have a date of 1/13/year, which might fail as well.

Perhaps using the following might help:

If Not IsDate ("01/" & cboMonth.Text + 1 & "/" & cboYear.Text) Then
  ' handle error and exit function
End If

might add some robustness to your code as well.

Chaz

 

[jofarrell]

*scuff* never thought about the 13th month ... and yes its failing at the set ... the SQL statement looks good in the debugger giving me ..

Select * FROM tblAdminDetails Where tblAdminDetails.[CRA] = 'Joanne Farrell' AND tblAdminDetails.[Date1] BETWEEN #01/6/01# and #30-Jun-01#

Now date formats are different but I did manually hard code them in and got the same error .. think that may be it?

Joanne

 

[jofarrell]

Ok its not the dates because I commented that line out .. I did dim MyName as String so its the simple SQL with no "and" that is causing a problem .... its the same way I have always done them so I dont see anything too obvious yet. I will get rid oh where I guess and see what happens ...

Joanne

 

[scorpio66]

Jo, it could be your SQL - have your tried it in the Query Analyzer (sic)?

Generally, when I include dates in SQL queries, I use the format 'yyyy-mm-dd', n.b. without the # characters surrounding the date.

Chaz

 

[woyler]

How about DbsNew. Where/How are you setting it?

 

[davidcawthray]

This may be a long shot but make sure that the references in your project refer to the latest version of data access components. We had this problem with ADO, with MDAC 2.5 being installed but we were referencing 2.1 in the project. This caused a type mismatch error which took us ages to track down.

DC

 

[jofarrell]

My connection to the Database is set on form load .. this is only a little tester project to make it easy to test export to excel ... so maybe it is teh project references .. but it worked Friday .... I hate gremlins ... and Access likes the silly #'s around dates .. I should have probably mentioned its an Access database


Ohh the joys of programming!!

Joanne

 

[jofarrell]

I checked references .. I even changed them .. still no luck and even a straighforward SQl of Select * from Table gives me the error ....

Joanne

 

[woyler]

Change your recorset declaration to:
Dim rsAdmin As DAO.Recordset
Make sure you have a reference to one of the Microsoft DAO 3.x Object Libray