Type Mismatch in an SQL stmt

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Could someone tell me where and why there is a "Type Mismatch" error in the
following? I believe it is in the "strSQL = ..." stmt, but I don't know
where or why. Thanks in advance.

Private Sub cmdFirstServed_Click()
On Error GoTo Err_cmdFirstServed_Click
Dim strSQL As String

Forms!frmSilTrans![txtFirst] = "Served"
strSQL = "UPDATE tblSilentLunchDet " & _
"SET datServed = Date() " & _
"WHERE txtName = '" & Forms!frmSilTrans!cboName.Column(1)
& "'" And datServed <> ""

CurrentDb.Execute strSQL, dbFailOnError

Exit_cmdFirstServed_Click:
Exit Sub

Err_cmdFirstServed_Click:
MsgBox Err.Description
Resume Exit_cmdFirstServed_Click

End Sub
 
Assuming that datServed is a Date column, it looks like your first problem is
that you're not delimiting the date. If you're in Access, you need to use the
pound sign (#) around the date. Also, date fields without a value are Null,
not zero-length string. Try this.

"UPDATE tblSilentLunchDet " & _
"SET datServed =#" & Date() & "# " & _
"WHERE txtName = '" & Forms!frmSilTrans!cboName.Column(1)
& "'" AND datServed Is Not Null"
 
At a guess it's because you've enclosed the expression
Date()
in the quotes, making it a literal part of the SQL statement. Try
something like
"SET datServed = #" & Format(Date("yyyy-mm-dd")) & "#" & _


Could someone tell me where and why there is a "Type Mismatch" error in the
following? I believe it is in the "strSQL = ..." stmt, but I don't know
where or why. Thanks in advance.

Private Sub cmdFirstServed_Click()
On Error GoTo Err_cmdFirstServed_Click
Dim strSQL As String

Forms!frmSilTrans![txtFirst] = "Served"
strSQL = "UPDATE tblSilentLunchDet " & _
"SET datServed = Date() " & _
"WHERE txtName = '" & Forms!frmSilTrans!cboName.Column(1)
& "'" And datServed <> ""

CurrentDb.Execute strSQL, dbFailOnError

Exit_cmdFirstServed_Click:
Exit Sub

Err_cmdFirstServed_Click:
MsgBox Err.Description
Resume Exit_cmdFirstServed_Click

End Sub
Click to expand...
 
You've got an unnecessary double quote before the And keyword. As well,
assuming you're trying to compare datServed to an empty string, you need a
total of 5 double quotes to end up with datServed <> "":

strSQL = "UPDATE tblSilentLunchDet " & _
"SET datServed = Date() " & _
"WHERE txtName = '" & _
Forms!frmSilTrans!cboName.Column(1) & _
"' And datServed <> """""

However, if datServed is a date field, it can never be equal to an empty
string: dates are numeric, so you need to check for Null:

strSQL = "UPDATE tblSilentLunchDet " & _
"SET datServed = Date() " & _
"WHERE txtName = '" & _
Forms!frmSilTrans!cboName.Column(1) & _
"' And datServed Is Null"
 
The FORMAT() shouldn't be neccessary as JET SHOULD evaluate the DATE()
function prior to executing the SQL statement.

I suspect that the problem is with the reference to the comboBox. Try
changing the statement to

strSQL = "UPDATE tblSilentLunchDet " & _
"SET datServed = Date() " & _
"WHERE txtName = '" & "sampleName" & "';"

Where sampleName is a valid value in tblSilentLunchDet. If the query
runs, then the problem IS with the comboBox. Mostly likely that the
..Column(1) value is not valid. (Keep in mind that the columns are
numbered starting with 0)

If the query above works then, change the statement back to the
reference the comboBox and ADD...

DEBUG.PRINT strSQL
STOP

immediatley after the strSQL statement. Then post the SQL statement
here. I suspect that as soon as you see the SQL statement you'll see
that the problem is in the WHERE statement.

John said:
At a guess it's because you've enclosed the expression
Date()
in the quotes, making it a literal part of the SQL statement. Try
something like
"SET datServed = #" & Format(Date("yyyy-mm-dd")) & "#" & _


Could someone tell me where and why there is a "Type Mismatch" error in the
following? I believe it is in the "strSQL = ..." stmt, but I don't know
where or why. Thanks in advance.

Private Sub cmdFirstServed_Click()
On Error GoTo Err_cmdFirstServed_Click
Dim strSQL As String

Forms!frmSilTrans![txtFirst] = "Served"
strSQL = "UPDATE tblSilentLunchDet " & _
"SET datServed = Date() " & _
"WHERE txtName = '" & Forms!frmSilTrans!cboName.Column(1)
& "'" And datServed <> ""

CurrentDb.Execute strSQL, dbFailOnError

Exit_cmdFirstServed_Click:
Exit Sub

Err_cmdFirstServed_Click:
MsgBox Err.Description
Resume Exit_cmdFirstServed_Click

End Sub
Click to expand...
Click to expand...
 
At a guess it's because you've enclosed the expression
Date()
in the quotes, making it a literal part of the SQL statement. Try
something like
"SET datServed = #" & Format(Date("yyyy-mm-dd")) & "#" & _
Click to expand...

I hate to contradict a MVP, but Date() is perfectly valid for a Jet-SQL
statement -- it's also data-type safe and gets around all that regional
stuff. This works fine for me:

Public Sub Hello()
Dim jetSQL As String
Dim rst As Recordset

jetSQL = "select date() as today"
MsgBox CurrentDb().OpenRecordset( _
jetSQL, dbOpenSnapshot, dbForwardOnly _
)!Today

End Sub


All the best

Tim F
 
My first guess would be that no records matched your WHERE clause criteria.
Try this:

MsgBox Forms!frmSilTrans!cboName.Column(1)
Exit Sub

Make sure that the value you want to use for txtName actually appears
somewhere. Also, I'd check that the column datServed actually contains Null
values.

SELECT * FROM tblSilentLunchDet WHERE datServed Is Null
--
Hmm...they have the Internet on COMPUTERS now!


ndunwoodie said:
That got rid of the errors, but nothing is written to the field datServed in
the table. Any thoughts?

MDW said:
Assuming that datServed is a Date column, it looks like your first problem is
that you're not delimiting the date. If you're in Access, you need to use the
pound sign (#) around the date. Also, date fields without a value are Null,
not zero-length string. Try this.

"UPDATE tblSilentLunchDet " & _
"SET datServed =#" & Date() & "# " & _
"WHERE txtName = '" & Forms!frmSilTrans!cboName.Column(1)
& "'" AND datServed Is Not Null"
--
Hmm...they have the Internet on COMPUTERS now!


ndunwoodie said:
Could someone tell me where and why there is a "Type Mismatch" error in the
following? I believe it is in the "strSQL = ..." stmt, but I don't know
where or why. Thanks in advance.

Private Sub cmdFirstServed_Click()
On Error GoTo Err_cmdFirstServed_Click
Dim strSQL As String

Forms!frmSilTrans![txtFirst] = "Served"
strSQL = "UPDATE tblSilentLunchDet " & _
"SET datServed = Date() " & _
"WHERE txtName = '" & Forms!frmSilTrans!cboName.Column(1)
& "'" And datServed <> ""

CurrentDb.Execute strSQL, dbFailOnError

Exit_cmdFirstServed_Click:
Exit Sub

Err_cmdFirstServed_Click:
MsgBox Err.Description
Resume Exit_cmdFirstServed_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
That got rid of the errors, but nothing is written to the field datServed in
the table. Any thoughts?

MDW said:
Assuming that datServed is a Date column, it looks like your first problem is
that you're not delimiting the date. If you're in Access, you need to use the
pound sign (#) around the date. Also, date fields without a value are Null,
not zero-length string. Try this.

"UPDATE tblSilentLunchDet " & _
"SET datServed =#" & Date() & "# " & _
"WHERE txtName = '" & Forms!frmSilTrans!cboName.Column(1)
& "'" AND datServed Is Not Null"
--
Hmm...they have the Internet on COMPUTERS now!


ndunwoodie said:
Could someone tell me where and why there is a "Type Mismatch" error in the
following? I believe it is in the "strSQL = ..." stmt, but I don't know
where or why. Thanks in advance.

Private Sub cmdFirstServed_Click()
On Error GoTo Err_cmdFirstServed_Click
Dim strSQL As String

Forms!frmSilTrans![txtFirst] = "Served"
strSQL = "UPDATE tblSilentLunchDet " & _
"SET datServed = Date() " & _
"WHERE txtName = '" & Forms!frmSilTrans!cboName.Column(1)
& "'" And datServed <> ""

CurrentDb.Execute strSQL, dbFailOnError

Exit_cmdFirstServed_Click:
Exit Sub

Err_cmdFirstServed_Click:
MsgBox Err.Description
Resume Exit_cmdFirstServed_Click

End Sub
Click to expand...
Click to expand...
 
My first guess would be that no records matched your WHERE clause criteria.
Try this:

MsgBox Forms!frmSilTrans!cboName.Column(1)
Exit Sub

Make sure that the value you want to use for txtName actually appears
somewhere. Also, I'd check that the column datServed actually contains Null
values.

SELECT * FROM tblSilentLunchDet WHERE datServed Is Null
--
Hmm...they have the Internet on COMPUTERS now!


ndunwoodie said:
That got rid of the errors, but nothing is written to the field datServed in
the table. Any thoughts?

MDW said:
Assuming that datServed is a Date column, it looks like your first problem is
that you're not delimiting the date. If you're in Access, you need to use the
pound sign (#) around the date. Also, date fields without a value are Null,
not zero-length string. Try this.

"UPDATE tblSilentLunchDet " & _
"SET datServed =#" & Date() & "# " & _
"WHERE txtName = '" & Forms!frmSilTrans!cboName.Column(1)
& "'" AND datServed Is Not Null"
--
Hmm...they have the Internet on COMPUTERS now!


ndunwoodie said:
Could someone tell me where and why there is a "Type Mismatch" error in the
following? I believe it is in the "strSQL = ..." stmt, but I don't know
where or why. Thanks in advance.

Private Sub cmdFirstServed_Click()
On Error GoTo Err_cmdFirstServed_Click
Dim strSQL As String

Forms!frmSilTrans![txtFirst] = "Served"
strSQL = "UPDATE tblSilentLunchDet " & _
"SET datServed = Date() " & _
"WHERE txtName = '" & Forms!frmSilTrans!cboName.Column(1)
& "'" And datServed <> ""

CurrentDb.Execute strSQL, dbFailOnError

Exit_cmdFirstServed_Click:
Exit Sub

Err_cmdFirstServed_Click:
MsgBox Err.Description
Resume Exit_cmdFirstServed_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Add

Debug.print strSQL
STOP

somewhere in the code to see what the SQL statement is evaluating to. I
and a few others suspect that the problem lies with the comboBox. If the
value in cboName.Column(1) does not match any records in the database,
none of them will be updated. Also, did you account for the fact that
the columns in a listBox or comboBox are numbered starting at (0).
column(1) would reference the 2nd physical column not the first.
That got rid of the errors, but nothing is written to the field datServed in
the table. Any thoughts?

:

Assuming that datServed is a Date column, it looks like your first problem is
that you're not delimiting the date. If you're in Access, you need to use the
pound sign (#) around the date. Also, date fields without a value are Null,
not zero-length string. Try this.

"UPDATE tblSilentLunchDet " & _
"SET datServed =#" & Date() & "# " & _
"WHERE txtName = '" & Forms!frmSilTrans!cboName.Column(1)
& "'" AND datServed Is Not Null"
--
Hmm...they have the Internet on COMPUTERS now!


:

Could someone tell me where and why there is a "Type Mismatch" error in the
following? I believe it is in the "strSQL = ..." stmt, but I don't know
where or why. Thanks in advance.

Private Sub cmdFirstServed_Click()
On Error GoTo Err_cmdFirstServed_Click
Dim strSQL As String

Forms!frmSilTrans![txtFirst] = "Served"
strSQL = "UPDATE tblSilentLunchDet " & _
"SET datServed = Date() " & _
"WHERE txtName = '" & Forms!frmSilTrans!cboName.Column(1)
& "'" And datServed <> ""

CurrentDb.Execute strSQL, dbFailOnError

Exit_cmdFirstServed_Click:
Exit Sub

Err_cmdFirstServed_Click:
MsgBox Err.Description
Resume Exit_cmdFirstServed_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Tim. Serve me right for not checking.

I hate to contradict a MVP, but Date() is perfectly valid for a Jet-SQL
statement -- it's also data-type safe and gets around all that regional
stuff. This works fine for me:

Public Sub Hello()
Dim jetSQL As String
Dim rst As Recordset

jetSQL = "select date() as today"
MsgBox CurrentDb().OpenRecordset( _
jetSQL, dbOpenSnapshot, dbForwardOnly _
)!Today

End Sub


All the best

Tim F
Click to expand...
 
Back
Top