Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    110

    Unanswered: Memo field Access SQL prefix

    This is an older post, reposted in case no one noticed the reply to older thread.
    I have the code below :

    Private Sub btnUpdate_Click()
    Dim strUpdate As String

    On Error GoTo Err_btnUpdate

    strUpdate = "Update EPISKEYH SET Kwdikos_texnikou = '" & Me.Kwdikos_texnikou & "'" _
    & ", Kwdikos_mhxanhmatos = '" & Me.Kwdikos_mhxanhmatos & "'" _
    & ", Hmeromhnia = #" & Me.Hmeromhnia & "#" _
    & ", Wra_enarjhs = #" & Me.Wra_enarjhs & "#" _
    & ", Wra_lhjhs = #" & Me.Wra_lhjhs & "#" _
    & ", Aitia_blabhs = " & Me.Aitia_blabhs _
    & ", Katastash =" & Me.Katastash _
    & ", Ek8esh_texnikou = " & Me.Ek8esh_texnikou _
    & " WHERE Kwdikos_episkeyhs = '" & Me.Kwdikos_episkeyhs & "'"

    CurrentDb.Execute (strUpdate)
    Exit Sub

    Err_btnUpdate:
    MsgBox Err.Description
    End Sub

    where Ek8esh_texnikou is a memo field, Wra_lhjhs, Wra_enarjhs and Hmeromhnia are Date (Time) fields. I get an syntax at the memo field clause of the statement. Do I need to use some specific prefix.

  2. #2
    Join Date
    Dec 2002
    Location
    Prverenges, Switzerland
    Posts
    3,740
    same as string: surround it with '
    & ", Ek8esh_texnikou = '" & Me.Ek8esh_texnikou & "'" _

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2004
    Posts
    110
    The full code is :

    strUpdate = "Update EPISKEYH SET Kwdikos_texnikou = '" & Me.Kwdikos_texnikou & "'" _
    & ", Kwdikos_mhxanhmatos = '" & Me.Kwdikos_mhxanhmatos & "'" _
    & ", Hmeromhnia = #" & Me.Hmeromhnia & "#" _
    & ", Wra_enarjhs = #" & Me.Wra_enarjhs & "#" _
    & ", Wra_lhjhs = #" & Me.Wra_lhjhs & "#" _
    & ", Aitia_blabhs = " & Me.Aitia_blabhs _
    & ", Katastash =" & Me.Katastash _
    & ", Ek8esh_texnikou = '" & Me.Ek8esh_texnikou & "'" _
    & " WHERE Kwdikos_episkeyhs = '" & Me.Kwdikos_episkeyhs & "'"

    CurrentDb.Execute (strUpdate)

    where Hmeromhnia is a date filed (works),
    Wra_enarjhs is a time field
    Wra_lhjhs is a time field

    I still get a syntax error which I think is due to erroneous syntax of the time fields clause.

    Any suggestions?

  4. #4
    Join Date
    Dec 2002
    Location
    Prverenges, Switzerland
    Posts
    3,740
    show us strUpdate via
    Debug.Print strUpdate
    just before your dbexecute line & copy/paste to this site
    in any case you should have this line in your code whilst you are trying to develop your SQL query... it really will help you to understand what is going on.

    also try
    docmd.runsql strUpdate
    instead of dbexecute (just to see what happens)

    also: A is not too fussy about the terminating ";" but some systems insist. it doesn't cost you a lot to have:
    & " WHERE Kwdikos_episkeyhs = '" & Me.Kwdikos_episkeyhs & "';"

    izy
    Last edited by izyrider; 05-10-04 at 14:05.
    currently using SS 2008R2

  5. #5
    Join Date
    Apr 2004
    Posts
    110
    Here is what I get from Debug.Print strUpdate

    Update EPISKEYH SET Kwdikos_texnikou = '590', Kwdikos_mhxanhmatos = ' Philip', Hmeromhnia = #2/4/2004#, Wra_enarjhs = #9:00:00 #, Wra_lhjhs = #9:20:00 #, Aitia_blabhs = False, Katastash =True, Ek8esh_texnikou = ' ' WHERE Kwdikos_episkeyhs = '69';

    Wra_enarjhs, Wra_lhjhs are time fields

    I think there are some gibberish at the end of these time clauses.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •