Update SQLquery not updating table

G

Guest

Can anyone see why my Update query doesn't Update the table 'DiaryDates'
This function is called on the 'ON EXIT' event of each text box on my 8 day
diary form. Using a break point I can see that all the correct values are in
strNote and strDate but it still doesn't update my table. Code is as follows:

Public Function UpdateDiary()
On Error Resume Next
Dim strSQL As String
Dim strNote As String, strDate As Date
Dim db As DAO.Database, qd As DAO.QueryDef
Dim F%
Set db = CurrentDb
strSQL = "UPDATE Diarydates SET DiaryNote = strNote WHERE DiaryDate =
strDate"

For F = 1 To 8
strDate = Forms![Diary]("Dt" & F)
strNote = Forms![Diary]("Note" & F)
Set qd = db.CreateQueryDef("UpdtDiaryNte", strSQL)
qd.Execute
qd.Close
Next F

End Function
Any help would be much appreciated,
Many Thanks in advace, Andy
 
R

Rick Brandt

Andy6 said:
Can anyone see why my Update query doesn't Update the table
'DiaryDates' This function is called on the 'ON EXIT' event of each
text box on my 8 day diary form. Using a break point I can see that
all the correct values are in strNote and strDate but it still
doesn't update my table. Code is as follows:

Public Function UpdateDiary()
On Error Resume Next
Dim strSQL As String
Dim strNote As String, strDate As Date
Dim db As DAO.Database, qd As DAO.QueryDef
Dim F%
Set db = CurrentDb
strSQL = "UPDATE Diarydates SET DiaryNote = strNote WHERE DiaryDate =
strDate"

For F = 1 To 8
strDate = Forms![Diary]("Dt" & F)
strNote = Forms![Diary]("Note" & F)
Set qd = db.CreateQueryDef("UpdtDiaryNte", strSQL)
qd.Execute
qd.Close
Next F

End Function
Any help would be much appreciated,
Many Thanks in advace, Andy
Click to expand...

You cannot use a variable inside your SQL statement. You need to delimit the
string so that the VALUE of the variable is in the string rather than the NAME
of the variable. That also means you have to build the SQL String every time,
not just once.

strSQL = "UPDATE Diarydates " & _
"SET DiaryNote = '" & strNote & "' " & _
"WHERE DiaryDate = #" & strDate & "#"

If DiaryDate really is a DateTime field your strDate will ahve to be in either
US or a non-ambiguous format.
 
G

Guest

Thanks Rick, Your'e pointing me in the right direction but not yet
successful.below is my revised Function and the result of a Debug line. All
seems to be right but the underlying Table doesn't get updated if I change
values in the text boxes on the Diary Form.

Public Function UpdateDiary()
On Error Resume Next
Dim strSQL As String
Dim strNote As String, strDate As Date
Dim db As DAO.Database, qd As DAO.QueryDef
Dim F As Integer
Set db = CurrentDb

For F = 1 To 8
strDate = Format(Forms![Diary]("Dt" & F), "dd\/mm\/yy")
strNote = Forms![Diary]("Note" & F)
strSQL = "UPDATE Diarydates SET DiaryNote = '" & strNote & "' WHERE
DiaryDate = #" & strDate & "#"
Set qd = db.CreateQueryDef("UpdtDiaryNte", strSQL)
qd.Execute
Debug.Print strSQL
qd.Close
Next F

End Function

Debug print out:

UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #26/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #19/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #20/02/07#
UPDATE Diarydates SET DiaryNote = 'Lunch with John 12:00' WHERE DiaryDate =
#21/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #22/02/07#
UPDATE Diarydates SET DiaryNote = 'Dentist 3pm' WHERE DiaryDate = #23/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #24/02/07#
UPDATE Diarydates SET DiaryNote = 'Football 2:30 at Club with Brian' WHERE
DiaryDate = #25/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #26/02/07#
Any Thoughts???
Andy.
 
R

Rick Brandt

Andy6 said:
Thanks Rick, Your'e pointing me in the right direction but not yet
successful.below is my revised Function and the result of a Debug
line. All seems to be right but the underlying Table doesn't get
updated if I change values in the text boxes on the Diary Form.

Public Function UpdateDiary()
On Error Resume Next
Dim strSQL As String
Dim strNote As String, strDate As Date
Dim db As DAO.Database, qd As DAO.QueryDef
Dim F As Integer
Set db = CurrentDb

For F = 1 To 8
strDate = Format(Forms![Diary]("Dt" & F), "dd\/mm\/yy")
strNote = Forms![Diary]("Note" & F)
strSQL = "UPDATE Diarydates SET DiaryNote = '" & strNote & "' WHERE
DiaryDate = #" & strDate & "#"
Set qd = db.CreateQueryDef("UpdtDiaryNte", strSQL)
qd.Execute
Debug.Print strSQL
qd.Close
Next F

End Function

Debug print out:

UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #26/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #19/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #20/02/07#
Click to expand...

Does your DiaryNote field allow zero length strings? If not you need to set
them to Null rather than ''. That will complicate your SQL statement
construction because for Null you do not want the quote marks in the statement.
UPDATE Diarydates SET DiaryNote = 'Lunch with John 12:00' WHERE
DiaryDate = #21/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #22/02/07#
UPDATE Diarydates SET DiaryNote = 'Dentist 3pm' WHERE DiaryDate =
#23/02/07# UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate =
#24/02/07#
UPDATE Diarydates SET DiaryNote = 'Football 2:30 at Club with Brian'
WHERE DiaryDate = #25/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #26/02/07#
Any Thoughts???
Andy.
Click to expand...

Once you have a valid SQL Update statement and executing it produces no errors
(but also updates zero rows) then the only logical conclusion is that your WHERE
clause is matching zero rows. If you paste each of those debug output lines
into a new query and run that what happens?

Reminder that I stated that you MUST use US or a non-ambiguous date format for
date literals in a query. Your dd/mm/yy should work as long as the "day" is
greater than 12 because then it is non-ambiguous. However; when the day is 12
or less Access will treat that as the month, not the day.

Best practice is to use ISO format yyyy-mm-dd.
 
G

Guest

I've changed the function and date formating to the following:
Public Function UpdateDiary()
On Error Resume Next
Dim strSQL As String
Dim strNote As String, strDate As Date
Dim db As DAO.Database, qd As DAO.QueryDef
Dim F As Integer
Set db = CurrentDb

For F = 1 To 8
strDate = Format(Forms![Diary]("Dt" & F), "yyyy\/mm\/dd")
strNote = Forms![Diary]("Note" & F)
strSQL = "UPDATE DiaryNotes SET DiaryNote = '" & strNote & "' WHERE
DiaryDate = #" & strDate & "#"
Set qd = db.CreateQueryDef("UpdtDiaryNte", strSQL)
qd.Execute
Debug.Print strSQL
qd.Close
Next F

End Function

The result of this debug line:
UPDATE DiaryNotes SET DiaryNote = 'Football 2:30 at Club With Graham' WHERE
DiaryDate = #25/02/07#

when pasted into a blank query returns no update to the table 'DiaryNotes'

When I look at it in Design Mode, it has switched the date to #07/02/25#

I've tried different formats on the line:
strDate = Format(Forms![Diary]("Dt" & F), "yyyy\/mm\/dd")
all to no avail.
Andy
 
R

Rick Brandt

Andy6 said:
I've changed the function and date formating to the following:
Public Function UpdateDiary()
On Error Resume Next
Dim strSQL As String
Dim strNote As String, strDate As Date
Dim db As DAO.Database, qd As DAO.QueryDef
Dim F As Integer
Set db = CurrentDb

For F = 1 To 8
strDate = Format(Forms![Diary]("Dt" & F), "yyyy\/mm\/dd")
strNote = Forms![Diary]("Note" & F)
strSQL = "UPDATE DiaryNotes SET DiaryNote = '" & strNote & "' WHERE
DiaryDate = #" & strDate & "#"
Set qd = db.CreateQueryDef("UpdtDiaryNte", strSQL)
qd.Execute
Debug.Print strSQL
qd.Close
Next F

End Function

The result of this debug line:
UPDATE DiaryNotes SET DiaryNote = 'Football 2:30 at Club With Graham'
WHERE DiaryDate = #25/02/07#

when pasted into a blank query returns no update to the table
'DiaryNotes'

When I look at it in Design Mode, it has switched the date to
#07/02/25#

I've tried different formats on the line:
strDate = Format(Forms![Diary]("Dt" & F), "yyyy\/mm\/dd")
all to no avail.
Andy
Click to expand...

It makes no sense that you would have this line...

strDate = Format(Forms![Diary]("Dt" & F), "yyyy\/mm\/dd")

....and end up with a query where the date is formatted like #25/02/07#.

Try...

strDate = Format(CDate(Forms![Diary]("Dt" & F)), "yyyy-mm-dd")

By the way, if you create the following in a query and run it does it update
anything?

UPDATE DiaryNotes
SET DiaryNote = 'Football 2:30 at Club With Graham'
WHERE DiaryDate = #2007-02-25#
 
G

Guest

Rick many thanks for your patience and excelent help and thanks to Gary in
QUERIES section: Between you I came up with this version and it works
perfectly.
Public Function UpdateDiary()
On Error Resume Next
Dim strSQL As String
Dim strNote As String
Dim db As DAO.Database, qd As DAO.QueryDef
Dim F As Integer
Set db = CurrentDb

For F = 1 To 8
strNote = Forms![Diary]("Note" & F)
strSQL = "UPDATE DiaryNotes SET DiaryNote = '" & strNote & "' WHERE
DiaryDate = #" & Format(Forms![Diary]("Dt" & F), "mm\/dd\/yyyy") & "#"
db.Execute strSQL
Next F
End Function

now on to the next problem!! Don't you just love it.
Many Many Thanks.
Andy.
 

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 Query doesn't Update Table 5
assigning a query to an array 5
removing trailing returns 11
UPDATE Field in Table using SQL & VBA 5
Access code error - Too few parametres. Expect 1. 7
vba 14
Update query issue 0
Something wrong with my code. 1

Top