If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update Query Questions
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")); |
#2
|
|||
|
|||
Update Query Questions
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" wrote: 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")); |
#3
|
|||
|
|||
Update Query Questions
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" wrote: 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" wrote: 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")); |
#4
|
|||
|
|||
Update Query Questions
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: -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Chuck W" wrote in message ... 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")); |
#5
|
|||
|
|||
Update Query Questions
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# -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Chuck W" wrote in message ... 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")); |
#6
|
|||
|
|||
Update Query Questions
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" wrote: 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" wrote: 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" wrote: 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")); |
#7
|
|||
|
|||
Update Query Questions
WHERE DISCHARGEDATE BETWEEN #1/1/2008# AND #12/31/2008#
doesn't require a function call for each row. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Klatuu" wrote in message ... 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" wrote: 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" wrote: 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" wrote: 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")); |
#8
|
|||
|
|||
Update Query Questions
This is true. Didn't think it through. Was thinking about readability
-- Dave Hargis, Microsoft Access MVP "Douglas J. Steele" wrote: WHERE DISCHARGEDATE BETWEEN #1/1/2008# AND #12/31/2008# doesn't require a function call for each row. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Klatuu" wrote in message ... 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" wrote: 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" wrote: 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" wrote: 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")); |
Thread Tools | |
Display Modes | |
|
|