SQL statement problem (update query)

  • Thread starter Thread starter George Papadopoulos
  • Start date Start date
G

George Papadopoulos

I have written the code below :

Private Sub btnUpdate_Click()
Dim strUpdate As String

On Error GoTo Err_btnUpdate

strUpdate = "UPDATE EPISKEYH SET EPISKEYH.Kwdikos_texnikou = '" &
Me.[Kwdikos texnikou] & "'"
strUpdate = strUpdate & ", EPISKEYH.Kwdikos_mhxanhmatos = '" & Me.[Kwdikos
mhxanhmatos] & "'"
strUpdate = strUpdate & ", EPISKEYH.Kwdikos_klinikhs = '" & Me.[Kwdikos
klinikhs] & "'"
strUpdate = strUpdate & ", EPISKEYH.Hmeromhnia = #" & Me.[Hmeromhnia] &
"#"
strUpdate = strUpdate & ", EPISKEYH.Wra_enarjhs = #" & Me.[Wra enarjhs] &
"#"
strUpdate = strUpdate & ", EPISKEYH.Wra_lhjhs = #" & Me.[Wra lhjhs] & "#"
strUpdate = strUpdate & ", EPISKEYH.Aitia_blabhs = " & Me.[Aitia blabhs]
strUpdate = strUpdate & ", EPISKEYH.Katastash = " & Me.Katastash
strUpdate = strUpdate & ", EPISKEYH.Ek8esh_texnikou = '" & Me.[Ek8esh
texnikou] & "'"
strUpdate = strUpdate & " WHERE EPISKEYH.Kwdikos_episkeyhs = '" &
Me.[Kwdikos episkeyhs] & "';"

MsgBox (strUpdate)
DoCmd.RunSQL (strUpdate)
'CurrentDb.Execute (strUpdate)
Exit Sub

Err_btnUpdate:
MsgBox Err.Description
End Sub

The form referenced, contains the values of fields from an already
existing record in TABLE EPISKEYH. The user is supposed to edit data and
then press on a button to update the database with the edited record.
Unfortunately when the code runs, I get the error message :

Syntax error in the expression '#1:00:00 ìì#'

All fields are string except Hmeromhnia, Wra_enarjhs, Wra_lhjhs which are
DATE. Ek8esh_texnikou is a memo field.

The strUpdate string contains the value (after all string concatenations are
performed)

UPDATE EPISKEYH SET EPISKEYH.Kwdikos_texnikou = '590',
EPISKEYH.Kwdikos_mhxanhmatos = 'OceYouii', EPISKEYH.Kwdikos_klinikhs =
'A202', EPISKEYH.Hmeromhnia = #3/5/2004#, EPISKEYH.Wra_enarjhs = #1:00:00
ii#, EPISKEYH.Wra_lhjhs = #1:15:00 ii#, EPISKEYH.Aitia_blabhs = False,
EPISKEYH.Katastash = True, EPISKEYH.Ek8esh_texnikou = '?aeaoiYii switch,
aeeU?ecea a?u ?aeaeU ?eaeYoa' WHERE EPISKEYH.Kwdikos_episkeyhs = '150';

Any suggestions?

thx, in advance

George Papadopoulos
 
George,

I think you should remove the hashes around the date / time fields in the
SQL expression, if both the fields in the table and the controls on the form
are of the same type (date/time) then the hashes are redundant and you get
rid of the format implications. Worth a shot?

HTH,
Nikos
 
The code works fine if I omit the two time fields concatenation
expressions. The working code is :

strUpdate = "UPDATE EPISKEYH SET EPISKEYH.Kwdikos_texnikou = '" &
Me.[Kwdikos texnikou] & "'"
strUpdate = strUpdate & ", EPISKEYH.Kwdikos_mhxanhmatos = '" & Me.[Kwdikos
mhxanhmatos] & "'"
strUpdate = strUpdate & ", EPISKEYH.Kwdikos_klinikhs = '" & Me.[Kwdikos
klinikhs] & "'"
strUpdate = strUpdate & ", EPISKEYH.Hmeromhnia = " & Me.[Hmeromhnia]
'strUpdate = strUpdate & ", EPISKEYH.Wra_enarjhs = #" & Me.[Wra enarjhs] &
"#"
'strUpdate = strUpdate & ", EPISKEYH.Wra_lhjhs = # " & Me.[Wra lhjhs] & "
# "
strUpdate = strUpdate & ", EPISKEYH.Aitia_blabhs = " & Me.[Aitia blabhs]
strUpdate = strUpdate & ", EPISKEYH.Katastash = " & Me.Katastash
strUpdate = strUpdate & ", EPISKEYH.Ek8esh_texnikou = '" & Me.[Ek8esh
texnikou] & "'"
strUpdate = strUpdate & " WHERE EPISKEYH.Kwdikos_episkeyhs = " &
Me.[Kwdikos episkeyhs] & ";"

I have found no way to make the update work with the two time expressions
included. This problem has been lingering for a long time with no solution
and I`m running out of options. I know that the solution will be trivial,
but I seem unable to come up with it.

Ï "Nikos Yannacopoulos said:
George,

I think you should remove the hashes around the date / time fields in the
SQL expression, if both the fields in the table and the controls on the form
are of the same type (date/time) then the hashes are redundant and you get
rid of the format implications. Worth a shot?

HTH,
Nikos

George Papadopoulos said:
I have written the code below :

Private Sub btnUpdate_Click()
Dim strUpdate As String

On Error GoTo Err_btnUpdate

strUpdate = "UPDATE EPISKEYH SET EPISKEYH.Kwdikos_texnikou = '" &
Me.[Kwdikos texnikou] & "'"
strUpdate = strUpdate & ", EPISKEYH.Kwdikos_mhxanhmatos = '" & Me.[Kwdikos
mhxanhmatos] & "'"
strUpdate = strUpdate & ", EPISKEYH.Kwdikos_klinikhs = '" & Me.[Kwdikos
klinikhs] & "'"
strUpdate = strUpdate & ", EPISKEYH.Hmeromhnia = #" & Me.[Hmeromhnia] &
"#"
strUpdate = strUpdate & ", EPISKEYH.Wra_enarjhs = #" & Me.[Wra
Click to expand...
enarjhs]
&
"#"
strUpdate = strUpdate & ", EPISKEYH.Wra_lhjhs = #" & Me.[Wra lhjhs] & "#"
strUpdate = strUpdate & ", EPISKEYH.Aitia_blabhs = " & Me.[Aitia blabhs]
strUpdate = strUpdate & ", EPISKEYH.Katastash = " & Me.Katastash
strUpdate = strUpdate & ", EPISKEYH.Ek8esh_texnikou = '" & Me.[Ek8esh
texnikou] & "'"
strUpdate = strUpdate & " WHERE EPISKEYH.Kwdikos_episkeyhs = '" &
Me.[Kwdikos episkeyhs] & "';"

MsgBox (strUpdate)
DoCmd.RunSQL (strUpdate)
'CurrentDb.Execute (strUpdate)
Exit Sub

Err_btnUpdate:
MsgBox Err.Description
End Sub

The form referenced, contains the values of fields from an already
existing record in TABLE EPISKEYH. The user is supposed to edit data and
then press on a button to update the database with the edited record.
Unfortunately when the code runs, I get the error message :

Syntax error in the expression '#1:00:00 ìì#'

All fields are string except Hmeromhnia, Wra_enarjhs, Wra_lhjhs which are
DATE. Ek8esh_texnikou is a memo field.

The strUpdate string contains the value (after all string concatenations are
performed)

UPDATE EPISKEYH SET EPISKEYH.Kwdikos_texnikou = '590',
EPISKEYH.Kwdikos_mhxanhmatos = 'OceYouii', EPISKEYH.Kwdikos_klinikhs =
'A202', EPISKEYH.Hmeromhnia = #3/5/2004#, EPISKEYH.Wra_enarjhs = #1:00:00
ii#, EPISKEYH.Wra_lhjhs = #1:15:00 ii#, EPISKEYH.Aitia_blabhs = False,
EPISKEYH.Katastash = True, EPISKEYH.Ek8esh_texnikou = '?aeaoiYii switch,
aeeU?ecea a?u ?aeaeU ?eaeYoa' WHERE EPISKEYH.Kwdikos_episkeyhs = '150';

Any suggestions?

thx, in advance

George Papadopoulos
Click to expand...
Click to expand...
 
George,

So field [Kwdikos episkeyhs] is numeric, and removing the hashes around the
date control made that work as well; That's two down.

What type are your time fields in your table ([Wra enarjhs] and [Wra lhjhs]
for non Greek readers)? If they are date/time (which they should), then you
must remove the hashes there as well. Also make sure you use a time
formatting on the form controls.

HTH,
Nikos


George Papadopoulos said:
The code works fine if I omit the two time fields concatenation
expressions. The working code is :

strUpdate = "UPDATE EPISKEYH SET EPISKEYH.Kwdikos_texnikou = '" &
Me.[Kwdikos texnikou] & "'"
strUpdate = strUpdate & ", EPISKEYH.Kwdikos_mhxanhmatos = '" & Me.[Kwdikos
mhxanhmatos] & "'"
strUpdate = strUpdate & ", EPISKEYH.Kwdikos_klinikhs = '" & Me.[Kwdikos
klinikhs] & "'"
strUpdate = strUpdate & ", EPISKEYH.Hmeromhnia = " & Me.[Hmeromhnia]
'strUpdate = strUpdate & ", EPISKEYH.Wra_enarjhs = #" & Me.[Wra enarjhs] &
"#"
'strUpdate = strUpdate & ", EPISKEYH.Wra_lhjhs = # " & Me.[Wra lhjhs] & "
# "
strUpdate = strUpdate & ", EPISKEYH.Aitia_blabhs = " & Me.[Aitia blabhs]
strUpdate = strUpdate & ", EPISKEYH.Katastash = " & Me.Katastash
strUpdate = strUpdate & ", EPISKEYH.Ek8esh_texnikou = '" & Me.[Ek8esh
texnikou] & "'"
strUpdate = strUpdate & " WHERE EPISKEYH.Kwdikos_episkeyhs = " &
Me.[Kwdikos episkeyhs] & ";"

I have found no way to make the update work with the two time expressions
included. This problem has been lingering for a long time with no solution
and I`m running out of options. I know that the solution will be trivial,
but I seem unable to come up with it.

Ï "Nikos Yannacopoulos said:
George,

I think you should remove the hashes around the date / time fields in the
SQL expression, if both the fields in the table and the controls on the form
are of the same type (date/time) then the hashes are redundant and you get
rid of the format implications. Worth a shot?

HTH,
Nikos

George Papadopoulos said:
I have written the code below :

Private Sub btnUpdate_Click()
Dim strUpdate As String

On Error GoTo Err_btnUpdate

strUpdate = "UPDATE EPISKEYH SET EPISKEYH.Kwdikos_texnikou = '" &
Me.[Kwdikos texnikou] & "'"
strUpdate = strUpdate & ", EPISKEYH.Kwdikos_mhxanhmatos = '" & Me.[Kwdikos
mhxanhmatos] & "'"
strUpdate = strUpdate & ", EPISKEYH.Kwdikos_klinikhs = '" & Me.[Kwdikos
klinikhs] & "'"
strUpdate = strUpdate & ", EPISKEYH.Hmeromhnia = #" &
Click to expand...
Click to expand...
Me.[Hmeromhnia]
&
"#"
strUpdate = strUpdate & ", EPISKEYH.Wra_enarjhs = #" & Me.[Wra
Click to expand...
enarjhs]
&
"#"
strUpdate = strUpdate & ", EPISKEYH.Wra_lhjhs = #" & Me.[Wra lhjhs]
Click to expand...
&
"#"
strUpdate = strUpdate & ", EPISKEYH.Aitia_blabhs = " & Me.[Aitia blabhs]
strUpdate = strUpdate & ", EPISKEYH.Katastash = " & Me.Katastash
strUpdate = strUpdate & ", EPISKEYH.Ek8esh_texnikou = '" & Me.[Ek8esh
texnikou] & "'"
strUpdate = strUpdate & " WHERE EPISKEYH.Kwdikos_episkeyhs = '" &
Me.[Kwdikos episkeyhs] & "';"

MsgBox (strUpdate)
DoCmd.RunSQL (strUpdate)
'CurrentDb.Execute (strUpdate)
Exit Sub

Err_btnUpdate:
MsgBox Err.Description
End Sub

The form referenced, contains the values of fields from an already
existing record in TABLE EPISKEYH. The user is supposed to edit data and
then press on a button to update the database with the edited record.
Unfortunately when the code runs, I get the error message :

Syntax error in the expression '#1:00:00 ìì#'

All fields are string except Hmeromhnia, Wra_enarjhs, Wra_lhjhs which are
DATE. Ek8esh_texnikou is a memo field.

The strUpdate string contains the value (after all string
Click to expand...
concatenations
are
performed)

UPDATE EPISKEYH SET EPISKEYH.Kwdikos_texnikou = '590',
EPISKEYH.Kwdikos_mhxanhmatos = 'OceYouii', EPISKEYH.Kwdikos_klinikhs =
'A202', EPISKEYH.Hmeromhnia = #3/5/2004#, EPISKEYH.Wra_enarjhs = #1:00:00
ii#, EPISKEYH.Wra_lhjhs = #1:15:00 ii#, EPISKEYH.Aitia_blabhs = False,
EPISKEYH.Katastash = True, EPISKEYH.Ek8esh_texnikou = '?aeaoiYii switch,
aeeU?ecea a?u ?aeaeU ?eaeYoa' WHERE EPISKEYH.Kwdikos_episkeyhs = '150';

Any suggestions?

thx, in advance

George Papadopoulos
Click to expand...
Click to expand...
Click to expand...
 
Back
Top