SQL statement problem (update query)

  • Thread starter George Papadopoulos
  • 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
 
N

Nikos Yannacopoulos

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
 
G

George Papadopoulos

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...
 
N

Nikos Yannacopoulos

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL update statement error 3

Top