Printable View
Is there some way I can write a SQL select so that if a date field
contains a certain date it is changed to another date, but all the
other date fields remain the same?
In other words:
select name1, date1 from table
With (If date1 = 1/1/1950 then date1 = 2/2/1952) somehow embeded in the
SQL select.
Something like...
"UPDATE table SET date1 = #2/2/1952# WHERE date1 = #1/1/1950#"
tc101,
There could be two ways of doing this. The first would be to use an update query to replace a certain date with another, something like:
update table
set datefield = '10/26/06'
where datefield like '10/25/06'
This would replace any cells where the date is 10/25/06 to 10/26/06. However, this would only occur once when you run the query. Which brings me to the second way of doing this...if you would like to have the date automatically swithced as a user imputs the data then you would need to use a trigger.
Hope this helps
ahh..beat me to it leinad31 :)
I can't do an update because I have to keep the original date in the table. I just want the results changes in one query.
This is in Access (Jet) sql. I don't think you can use triggers.
I may have to look for another solution if I can't make the change within a SQL query.
Use the IIF function
Select Name1, IIf(Date1=#01-Jan-1950#,#02-Feb-1952#, Date1)
Does access support the Case construct?
If so you can do this:
But I don't work with Access, so I don't know if it'll support it or not.Code:select name1,
CASE
WHEN date1 = '1/1/1950' THEN '2/2/1952'
ELSE date1
END as NewDate2
from table
If not, then look to see if it supports IIF() .... I bet it does.
-tg
And use an alias for the IIF expression.
Access doesn't do CASE but it does do IIF().Quote:
Originally Posted by techgnome
iff works.
Thanks folks. You guys are life savers.