Type Mismatch error after compact and repair

[colintis]

Since I just use MS Access 2003 to compact and repair a corrupted database, more errors were shown from it that weren't existed before the corruption. There are 2 errors I'm currently having:

3421 Data type conversion error
The error highlights the following code

    fmtFrom = "cdate('" & Format(fromDate, "dd-mmm-yyyy") & "')"
    fmtTo = "cdate('" & Format(toDate, "dd-mmm-yyyy") & "')"

    fmtDateRange = "cdate(format([date],'dd-mmm-yyyy'))>=" & _
         fmtFrom & " And cdate(format([date],'dd-mmm-yyyy'))<= " & fmtTo

    WHERESQL = WHERESQL & " AND " & _
        fmtDateRange

mydb.Execute ("UPDATE Attendance SET ManfHours = IIF(isNull(ManfHours),0,ManfHours) , " & _
        " WhouseHours = IIF(isNull(WhouseHours),0,WhouseHours) , " & _
        " [Other Hrs] = IIF(isNull([Other Hrs]),0,[Other Hrs]) " & _
         WHERESQL)

WHERESQL is a string defined under [Option Explicit], it stores the date filter criteria with 2 input date variables.


3464 Data type mismatch in criteria expression
The error highlights this line of code

RptRS.Fields(rptFieldIndex) = QueryRS.Fields(queryFieldIndex)

RptRS and QueryRS is the recordset, and both sides are integers.

The errors are from 3 forms, the forms only have 2 date selection (From and To) and a preview button to create a report output. The database I'm using is a split database, where the corrupted one stores the tables. So is it still possible the problems locate in the database that stores the queries, forms and reports? Or what should I do with the database that stores the tables to fix the "type" errors.

[colintis]

I found the problem for type mismatch error, its actually the WHERESQL string is holding the SQL with VBA code. I put the quotes around to solve this issue...

    
    fmtDateRange = "[Date] BETWEEN #" & _
                    Format(fromDate, "mm/dd/yyyy") & "# And #" & _
                    Format(toDate, "mm/dd/yyyy") & "#"

But I'm still having the "Data Type Conversion Error". I'm placing a bigger code range here for this error.

Do While Not QueryRS.EOF
        With RptRS
            .AddNew
            !EmployeeNo = QueryRS!EmployeeNo
            !EmpName = QueryRS!EmpName
            !RosterCode = QueryRS!RosterCode
            !Area = QueryRS!Area
            !Shift = QueryRS!Shift
            !Duties = QueryRS!Duties
            !n = QueryRS!n
            !s = QueryRS!s
            !r = QueryRS!r
            
            Dim rptFieldIndex, queryFieldIndex As Integer
            rptFieldIndex = StartField
            queryFieldIndex = LastFixFieldsNo + 1
            For i = LastFixFieldsNo + 1 To RptFieldCount - 1
                ' if not in the query
                If (i < StartField) Then
                    .Fields(i) = "~"
                ElseIf (queryFieldIndex > QueryFieldCount - 1) Then
                    .Fields(rptFieldIndex) = "~"
                    rptFieldIndex = rptFieldIndex + 1
                Else
                    .Fields(rptFieldIndex) = QueryRS.Fields(queryFieldIndex)
                    rptFieldIndex = rptFieldIndex + 1
                    queryFieldIndex = queryFieldIndex + 1
                End If
            Next i
            
            .Update
            QueryRS.MoveNext
        End With
    Loop

StartField is = LastFixFieldsNo + 1, the number 1 represents the start date is Monday, and + 2 for Tuesday and so on to Friday.

LastFixFieldsNo is a const integer

[NeoPa]

See indented below for some tips.

In this case I suggest you get everything you pass to MyDB.Execute in line #10 and put it into a string variable. When you have this available print it to the Immediate Pane then execute it.

Note what occurs (any error messages) and then copy the contents of that variable (from the Immediate Pane) and paste it into a new post here (with full details of any error). With that we can get a better idea of what we're looking at.

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.