UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Syntax, Access 2016    
 
   
mike60smart
post Feb 14 2020, 04:16 PM
Post#1


UtterAccess VIP
Posts: 13,928
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

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

Attached File  error.PNG ( 4.97K )Number of downloads: 2


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.


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Feb 14 2020, 04:26 PM
Post#2


UtterAccess Moderator
Posts: 12,849
Joined: 6-December 03
From: Telegraph Hill


Hi Mike,

Single quotes required around the string value 'knife safety'

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Feb 14 2020, 04:43 PM
Post#3


UtterAccess VIP
Posts: 12,232
Joined: 10-February 04
From: South Charleston, WV


And #'s around the date.

--------------------
Robert Crouser
Go to the top of the page
 
mike60smart
post Feb 14 2020, 04:45 PM
Post#4


UtterAccess VIP
Posts: 13,928
Joined: 6-June 05
From: Dunbar,Scotland


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:-
Attached File  error.PNG ( 3.2K )Number of downloads: 0


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


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Feb 14 2020, 04:52 PM
Post#5


UtterAccess Moderator
Posts: 12,849
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
tina t
post Feb 14 2020, 05:05 PM
Post#6



Posts: 6,613
Joined: 11-November 10
From: SoCal, USA


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

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

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
cheekybuddha
post Feb 14 2020, 05:12 PM
Post#7


UtterAccess Moderator
Posts: 12,849
Joined: 6-December 03
From: Telegraph Hill


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

thanks.gif Tina,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
tina t
post Feb 15 2020, 03:16 PM
Post#8



Posts: 6,613
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 
fkegley
post Feb 17 2020, 07:57 PM
Post#9


UtterAccess VIP
Posts: 23,787
Joined: 13-January 05
From: Mississippi


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.

--------------------
Frank Kegley, Microsoft Access 2010 MVP
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    4th June 2020 - 10:10 PM