Why doesn't this update work?

B

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

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

Brian

If I create the following query and run it I get no
records:

SELECT AVAILABILITY.BookingDate
FROM AVAILABILITY
WHERE AVAILABILITY.BookingDate=#07/09/2004# And
AVAILABILITY.Period=1 And AVAILABILITY.Room="H4";

Looks like it is a problem with the date. I have a form
setup whereby a user enter a date and runs a SELECT query
(DoCmd.OpenQuery "QueryAv") with the same WHERE as the
one that fails but that returns records which suggests
that it is a problem using the DoCmd.RunSQL strSQL
construct.

Any ideas?

-----Original Message-----
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.
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.
Click to expand...
.
Click to expand...
 
J

John Spencer (MVP)

OK, what happens if you drop the other criteria

SELECT AVAILABILITY.BookingDate
FROM AVAILABILITY
WHERE AVAILABILITY.BookingDate=#07/09/2004#

Do you still get no records? If so, let me ask what date is 7/9/2004 in words?

Is it July 9, 2004 or is it September 7, 2004? Access SQL (Jet) expects the
date to me in Month/Day/Year format.

If you do get a record or records, then try adding in the other criteria and see
which one is causing you to get no records back.

You can try the following query, which may be slow since you can't use any index
on the date field.

SELECT AVAILABILITY.BookingDate
FROM AVAILABILITY
WHERE DateValue(AVAILABILITY.BookingDate)=#07/09/2004# And
AVAILABILITY.Period=1 And AVAILABILITY.Room="H4";

If that works, you can modify the query to
SELECT AVAILABILITY.BookingDate
FROM AVAILABILITY
WHERE AVAILABILITY.BookingDate >= #07/09/2004# And
AVAILABILITY.BookingDate < #07/10/2004# And
AVAILABILITY.Period=1 And AVAILABILITY.Room="H4";
If I create the following query and run it I get no
records:

SELECT AVAILABILITY.BookingDate
FROM AVAILABILITY
WHERE AVAILABILITY.BookingDate=#07/09/2004# And
AVAILABILITY.Period=1 And AVAILABILITY.Room="H4";

Looks like it is a problem with the date. I have a form
setup whereby a user enter a date and runs a SELECT query
(DoCmd.OpenQuery "QueryAv") with the same WHERE as the
one that fails but that returns records which suggests
that it is a problem using the DoCmd.RunSQL strSQL
construct.

Any ideas?
-----Original Message-----
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.
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.
Click to expand...
.
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You need to explicitly format the date as mm/dd/yyyy in your query.

strSQL = "Update AVAILABILITY " & _
"Set AVAILABILITY.[Booking ID] = " & varNewID & _
" WHERE AVAILABILITY.BookingDate= " & _
Format$(Forms![SINGLE BOOKING AVAILABILITY]!BookingDate,
"\#mm\/dd\/yyyy\#") & _
" And AVAILABILITY.Period=" & Forms![SINGLE BOOKING
AVAILABILITY]!Combo8 & _
" And AVAILABILITY.Room=""" & Forms![SINGLE BOOKING
AVAILABILITY]!Combo10 & """"
DoCmd.RunSQL strSQL


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Brian said:
Date is 7th September. The 9th July does not exist in the
table.

If I drop the other parameters it still returns no rows.

I tried putting in #09/07/2004# and it updates my 7th
September row. In my ACCESS query via DoCmd.OpenQuery my
field Forms![SINGLE BOOKING AVAILABILITY]!BookingDate
passes format dd/mm/yyyy but in my DoCmd.RunSQL it passes
format mm/dd/yyyy!

So the diagnosis is right but how do I get around the
problem?
-----Original Message-----
OK, what happens if you drop the other criteria

SELECT AVAILABILITY.BookingDate
FROM AVAILABILITY
WHERE AVAILABILITY.BookingDate=#07/09/2004#

Do you still get no records? If so, let me ask what date is 7/9/2004 in words?

Is it July 9, 2004 or is it September 7, 2004? Access SQL (Jet) expects the
date to me in Month/Day/Year format.

If you do get a record or records, then try adding in the other criteria and see
which one is causing you to get no records back.

You can try the following query, which may be slow since you can't use any index
on the date field.

SELECT AVAILABILITY.BookingDate
FROM AVAILABILITY
WHERE DateValue(AVAILABILITY.BookingDate)=#07/09/2004# And
AVAILABILITY.Period=1 And AVAILABILITY.Room="H4";

If that works, you can modify the query to
SELECT AVAILABILITY.BookingDate
FROM AVAILABILITY
WHERE AVAILABILITY.BookingDate >= #07/09/2004# And
AVAILABILITY.BookingDate < #07/10/2004# And
AVAILABILITY.Period=1 And AVAILABILITY.Room="H4";
If I create the following query and run it I get no
records:

SELECT AVAILABILITY.BookingDate
FROM AVAILABILITY
WHERE AVAILABILITY.BookingDate=#07/09/2004# And
AVAILABILITY.Period=1 And AVAILABILITY.Room="H4";

Looks like it is a problem with the date. I have a form
setup whereby a user enter a date and runs a SELECT query
(DoCmd.OpenQuery "QueryAv") with the same WHERE as the
one that fails but that returns records which suggests
that it is a problem using the DoCmd.RunSQL strSQL
construct.

Any ideas?

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

Brian wrote:

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.
.
Click to expand...
.
Click to expand...
Click to expand...
 
B

Brian

Date is 7th September. The 9th July does not exist in the
table.

If I drop the other parameters it still returns no rows.

I tried putting in #09/07/2004# and it updates my 7th
September row. In my ACCESS query via DoCmd.OpenQuery my
field Forms![SINGLE BOOKING AVAILABILITY]!BookingDate
passes format dd/mm/yyyy but in my DoCmd.RunSQL it passes
format mm/dd/yyyy!

So the diagnosis is right but how do I get around the
problem?
-----Original Message-----
OK, what happens if you drop the other criteria

SELECT AVAILABILITY.BookingDate
FROM AVAILABILITY
WHERE AVAILABILITY.BookingDate=#07/09/2004#

Do you still get no records? If so, let me ask what date is 7/9/2004 in words?

Is it July 9, 2004 or is it September 7, 2004? Access SQL (Jet) expects the
date to me in Month/Day/Year format.

If you do get a record or records, then try adding in the other criteria and see
which one is causing you to get no records back.

You can try the following query, which may be slow since you can't use any index
on the date field.

SELECT AVAILABILITY.BookingDate
FROM AVAILABILITY
WHERE DateValue(AVAILABILITY.BookingDate)=#07/09/2004# And
AVAILABILITY.Period=1 And AVAILABILITY.Room="H4";

If that works, you can modify the query to
SELECT AVAILABILITY.BookingDate
FROM AVAILABILITY
WHERE AVAILABILITY.BookingDate >= #07/09/2004# And
AVAILABILITY.BookingDate < #07/10/2004# And
AVAILABILITY.Period=1 And AVAILABILITY.Room="H4";
If I create the following query and run it I get no
records:

SELECT AVAILABILITY.BookingDate
FROM AVAILABILITY
WHERE AVAILABILITY.BookingDate=#07/09/2004# And
AVAILABILITY.Period=1 And AVAILABILITY.Room="H4";

Looks like it is a problem with the date. I have a form
setup whereby a user enter a date and runs a SELECT query
(DoCmd.OpenQuery "QueryAv") with the same WHERE as the
one that fails but that returns records which suggests
that it is a problem using the DoCmd.RunSQL strSQL
construct.

Any ideas?
-----Original Message-----
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
Click to expand...
query
for just the date of
7/9/04 do you get any records returned? If not, then
Click to expand...
I
would suspect the
booking date field also has a time component in it.

Whatever the problem, Access is not finding a record
Click to expand...
to
update.
Brian wrote:

My update isn't updating the table. Why?

Code is:

strSQL = "Update AVAILABILITY " & _
"Set AVAILABILITY.[Booking ID] = " &
Click to expand...
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
Click to expand...
I
be
wrapping the Period field in something like the #?

Thanks for your help.
.
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

Update not working 1
Why no update? Help! 2
DoCmd.RunSQL error Help 5
Syntax error 1
Help Very frustrated with RunSQL 2
Too few parameters error. Help needed 1
Process multiple records 1
Help passing parameters to a query 1

Top