Why doesn't this update work?

[Brian]

My update isn't updating the table. Why?

Code is:

strSQL = "Update AVAILABILITY " & _
"Set AVAILABILITY.[Booking ID] = " & varNewID & _
" WHERE AVAILABILITY.BookingDate= #" & _
Forms![SINGLE BOOKING AVAILABILITY]!BookingDate &
_
"# And AVAILABILITY.Period=" & Forms![SINGLE
BOOKING AVAILABILITY]!Combo8 & _
" And AVAILABILITY.Room=""" & Forms![SINGLE
BOOKING AVAILABILITY]!Combo10 & """"
DoCmd.RunSQL strSQL
MsgBox strSQL

where varNewID is declared as Long.
The MsgBox produces:

UPDATE AVAILABILITY SET AVAILABILITY.[Booking ID] = 86
WHERE AVAILABILITY.BookingDate = #07/09/2004# And
AVAILABILITY.Period = 1 And AVAILABILITY.Room = "H4"

Booking Table contains the record:

Booking Date = 07/09/2004 (date/time field)
Period = 1 (Number field)
Room = H4 (text field)
Day Number = 4 (Number field)
Booking ID = 1 (Number field)

I get an ACCESS popup box stating: You are about to
update 0 rows, etc. asking for a YES or No response

Why isn't the row in the table getting updated? Is it
something to do with the format of the date? Should I be
wrapping the Period field in something like the #?

Thanks for your help.

 

[Byron]

Seem that you may have the concatination a little wrong.
Try: (I have moved the lines over to the left to try to
keep them from wraping so you could see them better. Hope
it works.)

strSQL = "Update AVAILABILITY " _
& "Set AVAILABILITY.[Booking ID] = " & varNewID & " " _
& "WHERE AVAILABILITY.BookingDate= #" " _
& "Forms![SINGLE BOOKING AVAILABILITY]!BookingDate " _
& "# And AVAILABILITY.Period=" & Forms![SINGLE
BOOKING AVAILABILITY]!Combo8 " _
& " And AVAILABILITY.Room=""" & Forms![SINGLE
BOOKING AVAILABILITY]!Combo10 & """"

To split you code into more that one line, you need to
end the line with a double qoute, a space, and an
underscore. To start the next line you need the ampersand
sign, a space and then a double quote.

HTH

Byron

 

[John Spencer (MVP)]

If you change the query to a select query and run the select query by pasting it
into a query window are any records returned?

Is the BookingDate storing the time also? if you query for just the date of
7/9/04 do you get any records returned? If not, then I would suspect the
booking date field also has a time component in it.

Whatever the problem, Access is not finding a record to update.