Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Forms _ Syntax

Posted by: mike60smart Feb 14 2020, 04:16 PM

Hi Everyone

I am getting the following error when I try to run the following On Click Event.



CODE
Private Sub cmdUpdateCourse_Click()

10        On Error GoTo cmdUpdateCourse_Click_Error

          Dim dtCourseDate As Date
20        dtCourseDate = Me.dtCourseDate
      Dim strSQL As String
30    strSQL = "UPDATE jtblPositionTraining SET CourseDate = " & Me.dtCourseDate & " WHERE EmployeeNr = " & Me.EmpNr & " AND TrainingDetails = " & Me.txtCourse & ";"
40    Debug.Print strSQL
50    CurrentDb.Execute strSQL, dbFailOnError

55      MsgBox "AllDates Updated", vbInformation
60        On Error GoTo 0
70        Exit Sub

cmdUpdateCourse_Click_Error:

80        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdateCourse_Click, line " & Erl & "."

End Sub


The Immediate windows displays the following correct values:-

UPDATE jtblPositionTraining SET CourseDate = 14/02/2020 WHERE EmployeeNr = 81768 AND TrainingDetails = Knife Safety;

Can anyone point out where I am going wrong?

Any help appreciated.


Posted by: cheekybuddha Feb 14 2020, 04:26 PM

Hi Mike,

Single quotes required around the string value 'knife safety'

hth,

d

Posted by: projecttoday Feb 14 2020, 04:43 PM

And #'s around the date.

Posted by: mike60smart Feb 14 2020, 04:45 PM

Hi David

Changed code to this:-

CODE
Private Sub cmdUpdateCourse_Click()

10        On Error GoTo cmdUpdateCourse_Click_Error

          Dim dtCourseDate As Date
20        dtCourseDate = Me.dtCourseDate
      Dim strSQL As String
30    strSQL = "UPDATE jtblPositionTraining SET CourseDate = " & Me.dtCourseDate & " WHERE EmployeeNr = " & Me.EmpNr & " AND TrainingDetails = '" & Me.txtCourse & "';"
40    Debug.Print strSQL
50    CurrentDb.Execute strSQL, dbFailOnError

55      MsgBox "AllDates Updated", vbInformation
60        On Error GoTo 0
70        Exit Sub

cmdUpdateCourse_Click_Error:

80        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdateCourse_Click, line " & Erl & "."

End Sub


Now get this error:-


Immediate Window shows this:-
UPDATE jtblPositionTraining SET CourseDate = 14/02/2020 WHERE EmployeeNr = 81768 AND TrainingDetails = 'Knife Safety';


Posted by: cheekybuddha Feb 14 2020, 04:52 PM

I think the error message is misleading, and that Robert is bang on the money. I missed the date part.
Try:

CODE
30    strSQL = "UPDATE jtblPositionTraining SET CourseDate = " & Format(Me.dtCourseDate, "\#yyyy\-mm\-ss hh:nn:ss\#") & " WHERE EmployeeNr = " & Me.EmpNr & " AND TrainingDetails = '" & Me.txtCourse & "';"


hth,

d

Posted by: tina t Feb 14 2020, 05:05 PM

QUOTE
... "\#yyyy\-mm\-ss ...

hi David, just wondering, is that supposed to be dd?

hth
tina

Posted by: cheekybuddha Feb 14 2020, 05:12 PM

Yes, definitely! It's been a long day ....!

thanks.gif Tina,

d

Posted by: tina t Feb 15 2020, 03:16 PM

you're welcome, no problem...long week... :) tina

Posted by: fkegley Feb 17 2020, 07:57 PM

I cannot write syntax for you, but I would suggest using the EVAL function around each of the suspect entries.

EVAL('expressongoeshere')---Note the 's around the expression. This forces the particular expression to be evaluated first, then the value is placed in the SQL statement.