Code won't run

[nayfeh]

Hi,
Can someone tell me why I can't get this code to run through a command button. For some reason, I get a "Data Type Mismatch in criteria expression" error. I think it has to do with UPDATE line in my code. I think I have all my def'n set up. I've already declared my connection.

Private Sub cmdViewReport_Click()

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

txtEndDate.SetFocus
strEndDate = txtEndDate.Text
txtStartDate.SetFocus
strStartDate = txtStartDate.Text

If IsDate(strStartDate) = False Or _
IsDate(strEndDate) = False Then
MsgBox "Ship date fields must be valid dates", vbCritical + vbOKOnly
Else
gblShipDateFrom = strStartDate
gblShipDateTo = strEndDate
End If

cmdViewReport.Enabled = False

strSql = "sp_NOPickList " & "'" & strStartDate & "', '" & strEndDate & "'"

Set qd = CurrentDb.QueryDefs("sp_NOPickList&quot
qd.SQL = strSql
qd.Close

cnn.Execute "UPDATE tbl_NOHeader SET Printed = (Printed + 1) WHERE ShipDate BETWEEN '" & strStartDate & "' AND '" & strEndDate & "'"

DoCmd.RunMacro "mcr_NOPickList.ViewReport"
cmdViewReport.Enabled = True

Exit Sub

 

[nealvenables]

JET expects dates to be delimited by 'HASH' signs ('pounds' if you're a yank) so try...


cnn.Execute "UPDATE tbl_NOHeader SET Printed = (Printed + 1) WHERE ShipDate BETWEEN #" & format(strStartDate, "dd/mmm/yyyy&quot & "# AND #" & format(strEndDate, "dd/mmm/yyyy&quot & "'"

 

[JeremyNYC]

True about the hash/pound, but just so's ya know, you don't have to format a date you pass in--Jet is pretty good at reading a variety of date formats.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[nealvenables]

True, but being based in the UK I've got into the habit of explicitly formatting the month component of dates to account for the inevitable mix of region settings on client PCs. i.e. 01/10/03 is ambiguous, 01/Oct/03 isn't.