Update Query Questions

C

Chuck W

Hi,
I have a table called AHRQ with a date/time field called DISCHARGEDATE and a
numeric field that is currently blank called DISCHARGEYEAR. The
DISCHARGEDATE field has normal date values (i.e. 6/1/2008). I want to run an
update query that will populate the DISCHARGEYEAR field with the value 2008
if the discharge date is a 2008 date. I keep getting an "Data Type mismatch
in criteria expression" error when I run the following query. I have changed
the format of the DISCHARGEYEAR field from text to numeric but get the same
error. Can someone help?

Thanks,



UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = "2008"
WHERE (([DISCHARGEDATE] Between "1/1/2008" And "12/31/2008"));
 
K

Klatuu

You are trying to update a numeric field with a text value.

UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = "2008"

Should be

UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = 2008

But, stop what you are doing and step away from the computer. What you are
doing is a bad idea. That is just creating redundant data. You can always
extract the year by using the either the Year() function:
Year( [DISCHARGEDATE])

You can also filter your data for the year you want like this:

WHERE Year([DISCHARGEDATE]) = 2008

Now if you are telling me this is to move the records to another table, I
would caution against that. It makes life more difficult when you have to go
back and find those records. It is better to leave the records in the table
and use queries to select the data you want to retrieve.
 
C

Chuck W

Hi,
Thanks for your help. I tried your suggestion and it worked. I also needed
# around my dates. I am actually creating a table that another program uses
by importing from access. It won't import a query but will import a table.
It requires the DISCHARGEYEAR field.

Thanks,

Chuck

Klatuu said:
You are trying to update a numeric field with a text value.

UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = "2008"

Should be

UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = 2008

But, stop what you are doing and step away from the computer. What you are
doing is a bad idea. That is just creating redundant data. You can always
extract the year by using the either the Year() function:
Year( [DISCHARGEDATE])

You can also filter your data for the year you want like this:

WHERE Year([DISCHARGEDATE]) = 2008

Now if you are telling me this is to move the records to another table, I
would caution against that. It makes life more difficult when you have to go
back and find those records. It is better to leave the records in the table
and use queries to select the data you want to retrieve.
--
Dave Hargis, Microsoft Access MVP


Chuck W said:
Hi,
I have a table called AHRQ with a date/time field called DISCHARGEDATE and a
numeric field that is currently blank called DISCHARGEYEAR. The
DISCHARGEDATE field has normal date values (i.e. 6/1/2008). I want to run an
update query that will populate the DISCHARGEYEAR field with the value 2008
if the discharge date is a 2008 date. I keep getting an "Data Type mismatch
in criteria expression" error when I run the following query. I have changed
the format of the DISCHARGEYEAR field from text to numeric but get the same
error. Can someone help?

Thanks,



UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = "2008"
WHERE (([DISCHARGEDATE] Between "1/1/2008" And "12/31/2008"));
Click to expand...
Click to expand...
 
D

Douglas J. Steele

While Dave's given you the correct answer (i.e.: you shouldn't store
DischargeYear as it's redundant), the other reason your query didn't work is
because dates need to be delimited with #, not quote:
 
D

Douglas J. Steele

Oops: sorry about that: hit Enter too soon.

While Dave's given you the correct answer (i.e.: you shouldn't store
DischargeYear as it's redundant), the other reason your query didn't work is
because dates need to be delimited with #, not quotes:

UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = 2008
WHERE [DISCHARGEDATE] Between #1/1/2008# And #12/31/2008#
 
K

Klatuu

Okay, you have a valid reason. You are excused :)

I would not use the WHERE clause as you have it written.
I would use WHERE Year([DISCHARGEDATE]) = 2008
--
Dave Hargis, Microsoft Access MVP


Chuck W said:
Hi,
Thanks for your help. I tried your suggestion and it worked. I also needed
# around my dates. I am actually creating a table that another program uses
by importing from access. It won't import a query but will import a table.
It requires the DISCHARGEYEAR field.

Thanks,

Chuck

Klatuu said:
You are trying to update a numeric field with a text value.

UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = "2008"

Should be

UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = 2008

But, stop what you are doing and step away from the computer. What you are
doing is a bad idea. That is just creating redundant data. You can always
extract the year by using the either the Year() function:
Year( [DISCHARGEDATE])

You can also filter your data for the year you want like this:

WHERE Year([DISCHARGEDATE]) = 2008

Now if you are telling me this is to move the records to another table, I
would caution against that. It makes life more difficult when you have to go
back and find those records. It is better to leave the records in the table
and use queries to select the data you want to retrieve.
--
Dave Hargis, Microsoft Access MVP


Chuck W said:
Hi,
I have a table called AHRQ with a date/time field called DISCHARGEDATE and a
numeric field that is currently blank called DISCHARGEYEAR. The
DISCHARGEDATE field has normal date values (i.e. 6/1/2008). I want to run an
update query that will populate the DISCHARGEYEAR field with the value 2008
if the discharge date is a 2008 date. I keep getting an "Data Type mismatch
in criteria expression" error when I run the following query. I have changed
the format of the DISCHARGEYEAR field from text to numeric but get the same
error. Can someone help?

Thanks,



UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = "2008"
WHERE (([DISCHARGEDATE] Between "1/1/2008" And "12/31/2008"));
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

WHERE DISCHARGEDATE BETWEEN #1/1/2008# AND #12/31/2008#

doesn't require a function call for each row.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Klatuu said:
Okay, you have a valid reason. You are excused :)

I would not use the WHERE clause as you have it written.
I would use WHERE Year([DISCHARGEDATE]) = 2008
--
Dave Hargis, Microsoft Access MVP


Chuck W said:
Hi,
Thanks for your help. I tried your suggestion and it worked. I also
needed
# around my dates. I am actually creating a table that another program
uses
by importing from access. It won't import a query but will import a
table.
It requires the DISCHARGEYEAR field.

Thanks,

Chuck

Klatuu said:
You are trying to update a numeric field with a text value.

UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = "2008"

Should be

UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = 2008

But, stop what you are doing and step away from the computer. What you
are
doing is a bad idea. That is just creating redundant data. You can
always
extract the year by using the either the Year() function:
Year( [DISCHARGEDATE])

You can also filter your data for the year you want like this:

WHERE Year([DISCHARGEDATE]) = 2008

Now if you are telling me this is to move the records to another table,
I
would caution against that. It makes life more difficult when you have
to go
back and find those records. It is better to leave the records in the
table
and use queries to select the data you want to retrieve.
--
Dave Hargis, Microsoft Access MVP


:

Hi,
I have a table called AHRQ with a date/time field called
DISCHARGEDATE and a
numeric field that is currently blank called DISCHARGEYEAR. The
DISCHARGEDATE field has normal date values (i.e. 6/1/2008). I want
to run an
update query that will populate the DISCHARGEYEAR field with the
value 2008
if the discharge date is a 2008 date. I keep getting an "Data Type
mismatch
in criteria expression" error when I run the following query. I have
changed
the format of the DISCHARGEYEAR field from text to numeric but get
the same
error. Can someone help?

Thanks,



UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = "2008"
WHERE (([DISCHARGEDATE] Between "1/1/2008" And "12/31/2008"));
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

This is true. Didn't think it through. Was thinking about readability
--
Dave Hargis, Microsoft Access MVP


Douglas J. Steele said:
WHERE DISCHARGEDATE BETWEEN #1/1/2008# AND #12/31/2008#

doesn't require a function call for each row.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Klatuu said:
Okay, you have a valid reason. You are excused :)

I would not use the WHERE clause as you have it written.
I would use WHERE Year([DISCHARGEDATE]) = 2008
--
Dave Hargis, Microsoft Access MVP


Chuck W said:
Hi,
Thanks for your help. I tried your suggestion and it worked. I also
needed
# around my dates. I am actually creating a table that another program
uses
by importing from access. It won't import a query but will import a
table.
It requires the DISCHARGEYEAR field.

Thanks,

Chuck

:

You are trying to update a numeric field with a text value.

UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = "2008"

Should be

UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = 2008

But, stop what you are doing and step away from the computer. What you
are
doing is a bad idea. That is just creating redundant data. You can
always
extract the year by using the either the Year() function:
Year( [DISCHARGEDATE])

You can also filter your data for the year you want like this:

WHERE Year([DISCHARGEDATE]) = 2008

Now if you are telling me this is to move the records to another table,
I
would caution against that. It makes life more difficult when you have
to go
back and find those records. It is better to leave the records in the
table
and use queries to select the data you want to retrieve.
--
Dave Hargis, Microsoft Access MVP


:

Hi,
I have a table called AHRQ with a date/time field called
DISCHARGEDATE and a
numeric field that is currently blank called DISCHARGEYEAR. The
DISCHARGEDATE field has normal date values (i.e. 6/1/2008). I want
to run an
update query that will populate the DISCHARGEYEAR field with the
value 2008
if the discharge date is a 2008 date. I keep getting an "Data Type
mismatch
in criteria expression" error when I run the following query. I have
changed
the format of the DISCHARGEYEAR field from text to numeric but get
the same
error. Can someone help?

Thanks,



UPDATE AHRQ SET AHRQ.[DISCHARGEYEAR] = "2008"
WHERE (([DISCHARGEDATE] Between "1/1/2008" And "12/31/2008"));
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

Show Date Difference only in a certain range 1
Find records where value is out of a range 3
Append Crosstab Query to Existing Table 0
Validation Rule On Form 2
Problem with Subquery 2
Problem with Subquery 0
Date Restrictor no longer works when linking to SQL Server table 5
Update of Existing Table Numeric Value Using DCount from Another T 3

Top