Resolved Error - Data type mismatch

[gdshih]

I am getting the following error when I execute this code: "Data type mismatch in criteria expression." Thanks in advance.

Dim WhereUsed As Boolean = False
        Dim WhereString

        If Len(Me.cboCaller.Text) > 0 Then
            WhereString = "WHERE CALLER=?"
            WhereUsed = True
        Else
        End If

        Dim SQL As String
        SQL = "SELECT tblFirms.ORG_PK, tblFirms.ORG_NAME, tblStatus_Type.Status_TX, tblFirms.AA_NM, tblFirms.CONTACT_NM, tblFirms.CONTACT_PH, tblFirms.AA_PH, tblFirms.ASSESSED_AMOUNT, tblFirms.REN_BAL, tblFirms.DAILY_BAL, tblCallStatus.Call_Status, tblCallResults.Call_Result, tblCallDetails.caller, tblCallDetails.call_date " & _
            "FROM (((tblFirms INNER JOIN tblCallDetails ON tblFirms.ORG_PK = tblCallDetails.ORG_PK) INNER JOIN tblStatus_Type ON tblFirms.STATUS_ID = tblStatus_Type.Status_ID) INNER JOIN tblCallStatus ON tblCallDetails.CSTAT_ID = tblCallStatus.CStat_ID) INNER JOIN tblCallResults ON tblCallDetails.CRESULT_ID = tblCallResults.CResult_ID "

        Dim StartDt As Date = Now
        'start date is 7 days ago
        StartDt = StartDt.AddDays(-7)

        If WhereUsed = True Then
            SQL = SQL & WhereString & " and call_date>? "
        Else
            SQL = SQL & "where call_date>? "
        End If

        SQL = SQL & "ORDER BY tblCallDetails.call_date DESC "

        Dim myList As New List(Of String)
        Dim TryAttempt As Integer = 0

Retry:
        'Try
        Using conLocal As New OleDb.OleDbConnection(myConnString)
            Using commandLocal As New OleDb.OleDbCommand(SQL, conLocal)
                If WhereUsed = True Then
                    commandLocal.Parameters.AddWithValue("FirstName", Me.cboCaller.Text)
                    commandLocal.Parameters.AddWithValue("FirstName", StartDt)
                Else
                    commandLocal.Parameters.AddWithValue("FirstName", StartDt)
                End If
                conLocal.Open()
                Using dataReader As OleDb.OleDbDataReader = commandLocal.ExecuteReader
                    While dataReader.Read

 

[AndrewdAzotus]

Fair enough, that also makes sense.

I should have been using the VBA equivalent to parameter.addwithvalue following a query.

It doesn't help with a DLOOKUP but I was only using them for laziness.

Perhaps it would have been even lazier to used a Select statement and passed the date in as a parm rather than the DLOOKUP

I always seem to forget who powerful and efficient SQL is and try to do my 'thing' without it...

 

[jmcilhinney]

Sorry to open an old wound, but going back to one of my earlier posts. Given that literals must be M/dd/yyyy (which, now that I understand why, is fine) that does not explain why



did not work since it's not a literal...

Post #15 was actually in answer to post #13. In answer to this question, it actually is a literal from the point of view of the SQL code. You are relying on the Date being implicitly converted to a String and then concatenating that with the other String containing the rest of your SQL code. That whole statement is passed to the database as is, containing the literal value. The aforementioned implicit conversion will be done differently on different systems, which is part of the problem. When you use parameters the values are sent to the database in binary form and used in binary form by the database, so formatting text is never an issue because there's no text to format.

 

[AndrewdAzotus]

Of Course!

It wasn't a literal for me, but it was by the time it was processed in SQL - It's obvious ... now!



I suppose what was confusing was that it managed to make sense of 21/2/2012 whereas since it was expecting M/dd/yyyy it should have reported an invalid date - too clever for it's own good...