Code to change record data returns "data type mismatch in criteria"

[CRobledo]

Has anyone had this happen to them? I made a form to make edits to a access table that is linked to an SQl server. I have a few text boxes in my form where I can set my parameters of where I want the changes to be made. example (date range) and then a few text boxes where I type the data that I want to replace the old data and click run to make the changes

for some reason I keep getting "data type mismatch in criteria" but only when I'm trying to edit the data from the SQL server. if I export the data from the server and import to a table in access I can make those changes with no problem.

here is the code that I am using

Option Compare Database
Option Explicit


Private Sub Command129_Click()
Dim myStr As String
Dim mySQL As String

On Error GoTo Err_Command129_Click


If IsNull(Me.[StartdateText]) Or Len(Me.[StartdateText]) = 0 Then
MsgBox "Please enter Start Date.", vbExclamation, "Error"
Me.[StartdateText].SetFocus
Exit Sub
ElseIf IsNull(Me.[EndDateText]) Or Len(Me.[EndDateText]) = 0 Then
MsgBox "Please enter End Date.", vbExclamation, "Error"
Me.[EndDateText].SetFocus
Exit Sub


ElseIf IsNull(Me.[LocationCMB]) Or Len(Me.[LocationCMB]) = 0 Then
MsgBox "Please enter Location.", vbExclamation, "Error"
Me.[LocationCMB].SetFocus
Exit Sub
ElseIf IsNull(Me.[CrewCMB]) Or Len(Me.[CrewCMB]) = 0 Then
MsgBox "Please enter Crew Name.", vbExclamation, "Error"
Me.[CrewCMB].SetFocus
Exit Sub
End If

' -------------------------------------------------------------
myStr = ""
If Not IsNull(txtActivityRate) Then
myStr = "[ActivityRate] = " & txtActivityRate
End If

If Not IsNull(txtActivity) Then
If myStr = "" Then
myStr = "[ActivityDesc] = """ & CStr(txtActivity.Column(1)) & """"
Else
myStr = myStr & ", [ActivityDesc] = """ & CStr(txtActivity.Column(1)) & """"
End If
End If

If Not IsNull(txtVariety) Then
If myStr = "" Then
myStr = "[Variety] = """ & CStr(txtVariety) & """"
Else
myStr = myStr & ", [Variety] = """ & CStr(txtVariety) & """"
End If
End If

If Not IsNull(txtCommodity) Then
If myStr = "" Then
myStr = "[Commodity] = """ & CStr(txtCommodity) & """"
Else
myStr = myStr & ", [Commodity] = """ & CStr(txtCommodity) & """"
End If
End If

If Not IsNull(txtTransTimeIn) Then
If myStr = "" Then
myStr = "[Trans_TimeIn] = #" & txtTransTimeIn & "#"
Else
myStr = myStr & ", [Trans_TimeIn] = #" & txtTransTimeIn & "#"
End If
End If

If Not IsNull(txtTransTimeOut) Then
If myStr = "" Then
myStr = "[Trans_TimeOut] = #" & txtTransTimeOut & "#"
Else
myStr = myStr & ", [Trans_TimeOut] = #" & txtTransTimeOut & "#"
End If
End If

If Len(myStr) > 0 Then
mySQL = "UPDATE [dbo_T_Transactions_Calculated (Payroll)] SET " & myStr & " WHERE ( [LocationID] = """ & Me.LocationCMB.Column(1) & """ AND [GroupName] = """ & Me.CrewCMB.Column(1) & """ AND ([Trans_TimeIn] BETWEEN #" & Format(Me.StartdateText, "yyyy-mm-dd hh:nn:ss") & "# AND #" & Format(Me.EndDateText, "yyyy-mm-dd hh:nn:ss") & "#))"
CurrentDb.Execute (mySQL)
Forms![Payroll Edit Form].Controls![subdbo_T_Transactions_Calculated (Payroll) subform].Form.Requery
End If


Exit_Command129_Click:
Exit Sub

Err_Command129_Click:
MsgBox Err.Description
Resume Exit_Command129_Click
End Sub

[pbaldy]

What line errors? In general, Access requires quotes around text values, # around date values, nothing around numeric values. This might help:

http://www.baldyweb.com/ImmediateWindow.htm

[andy49]

what is your local date separator?

if you debug.print

Format(now(), "yyyy-mm-dd hh:nn:ss")

what do you get?