Update Query!!

[Guest]

I'm trying to update my table within a certain conditions. I want to replace a field with data for a certain condition, but I keep get a syntax error. Can someone help

Here is the code
UPDATE tblFirst SET year="2001" where dtOccDate=(between #01/01/2001# and #12/31/2001#)

 

[Ken Snell]

I'm guessing that your use of year as a field name is causing confusion for
ACCESS. Year is the name of a VBA function that returns the year portion of
a date -- and you're trying to set it equal to a value.

Try this (put [ ] around the year field name):

UPDATE tblFirst SET [year]="2001" where dtOccDate=(between #01/01/2001# and
#12/31/2001#)

For more info on which words should not be used as field names, control
names, etc., see this Knowledge Base article:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

--
Ken Snell
<MS ACCESS MVP>

I'm trying to update my table within a certain conditions. I want to

replace a field with data for a certain condition, but I keep get a syntax
error. Can someone help?

Here is the code:
UPDATE tblFirst SET year="2001" where dtOccDate=(between #01/01/2001# and

#12/31/2001#)

 

[Rachel]

Try;

UPDATE tblFirst SET year="2001" where dtOccDate between
#01/01/2001# and #12/31/2001#

-----Original Message-----
I'm trying to update my table within a certain

conditions. I want to replace a field with data for a
certain condition, but I keep get a syntax error. Can
someone help?

 

[John Vinson]

I'm trying to update my table within a certain conditions. I want to replace a field with data for a certain condition, but I keep get a syntax error. Can someone help?

Here is the code:
UPDATE tblFirst SET year="2001" where dtOccDate=(between #01/01/2001# and #12/31/2001#)

= is one type of operator; BETWEEN is a different type of operator.
Use either one or the other not both. If Year is a text field the
quotes are needed, but if it's numeric lose the quotes. Try

UPDATE tblFirst SET year=2001 where dtOccDate between #01/01/2001# and
#12/31/2001#

 

[Ken Snell]

Good catch, John, re: = and between....the eyes are among the first to
go....

< g >