[RESOLVED] SQL question

[tc101]

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.

[leinad31]

Something like...
"UPDATE table SET date1 = #2/2/1952# WHERE date1 = #1/1/1950#"

[kxcntry99]

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

[kxcntry99]

ahh..beat me to it leinad31

[tc101]

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.

[brucevde]

Use the IIF function

Select Name1, IIf(Date1=#01-Jan-1950#,#02-Feb-1952#, Date1)

[techgnome]

Does access support the Case construct?

If so you can do this:

select name1, 
  CASE 
    WHEN date1 = '1/1/1950' THEN '2/2/1952'
    ELSE date1
  END as NewDate2
from table

But I don't work with Access, so I don't know if it'll support it or not.

If not, then look to see if it supports IIF() .... I bet it does.

-tg

[leinad31]

And use an alias for the IIF expression.

[disruptivehair]

Does access support the Case construct?

If so you can do this:

select name1, 
  CASE 
    WHEN date1 = '1/1/1950' THEN '2/2/1952'
    ELSE date1
  END as NewDate2
from table

But I don't work with Access, so I don't know if it'll support it or not.

If not, then look to see if it supports IIF() .... I bet it does.

-tg

Access doesn't do CASE but it does do IIF().

[tc101]

iff works.

Thanks folks. You guys are life savers.