SQL string sytax error

[RipperT]

The following generates an error: "Too few parameters. Expected: 1"
=========start code=============
Dim myFilter as String
Dim strSQL As String
Dim inputDate As String
inputDate = InputBox("Please enter the date in 00/00/00 format: ","Enter
Date")
myFilter = "[HTDate] = Forms![frmPrintHT_SCC]![txtHTDate]"
strSQL = "UPDATE tblInmates INNER JOIN tblSCC ON
tblInmates.InmateId=tblSCC.InmateID " _
& "SET tblSCC.[HTDate] = " & inputDate & " " _
& "WHERE tblSCC.ReleaseDate Is Null And " & myFilter & ";"
Debug.Print "Input date is: " & inputDate
Debug.Print Forms![frmPrintHT_SCC]![txtHTDate]
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
=========end code==============
The 3 debug statements yield:
Input date is: 10/18/07 '(any test date typed into input box)
10/10/2007
UPDATE tblInmates INNER JOIN tblSCC ON
tblInmates.InmateId=tblSCC.InmateID SET tblSCC.[HTDate] = 10/18/07 WHERE
tblSCC.ReleaseDate Is Null And [HTDate] =
Forms![frmPrintHT_SCC]![txtHTDate];

I've tried putting #'s around the date so it reads #10/18/07#, but
still the error. If I copy this into a query, it works fine, but it
won't work from my code. Help! And thanx!
Rip

 

[Douglas J. Steele]

Yes, you need the octothorpe delimiters (#) around the date:

strSQL = "UPDATE tblInmates INNER JOIN tblSCC ON " _
& "tblInmates.InmateId=tblSCC.InmateID " _
& "SET tblSCC.[HTDate] = #" & inputDate & "# " _
& "WHERE tblSCC.ReleaseDate Is Null And " & myFilter & ";"

However, your myFilter requires the content of the text box:

myFilter = "[HTDate] = #" & FForms![frmPrintHT_SCC]![txtHTDate] & "#"

Note that regardless of what the user's Short Date format has been set to
through Regional Settings, force them to input the date in mm/dd/yyyy
format: using dd/mm/yyyy format will result in Access misinterpretting the
date for the first 12 days of each month.

Alternatively, if you want to allow them to input the date in a format with
which they're comfortable, try:

Dim myFilter as String
Dim strSQL As String
Dim inputDate As String
Dim desiredDate As Date


inputDate = InputBox("Please enter the date in 00/00/00 format: ","Enter
Date")
desiredDate = CDate(inputDate)

myFilter = "[HTDate] = " & _
Format(CDate(Forms![frmPrintHT_SCC]![txtHTDate]), "\#yyyy\-mm\-dd\#")

strSQL = "UPDATE tblInmates INNER JOIN tblSCC ON " _
&tblInmates.InmateId=tblSCC.InmateID " _
& "SET tblSCC.[HTDate] = " & Format(desiredDate, "\#yyyy\-mm\-dd\#") & "
" _
& "WHERE tblSCC.ReleaseDate Is Null And " & myFilter & ";"
Debug.Print "Input date is: " & desiredDate
Debug.Print Forms![frmPrintHT_SCC]![txtHTDate]
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError