Query based on field name

[rkbatts]

I'm trying to copy data from one table to another based on a user selected date. The starting table has eight fields. The first is an ID number the other 7 are named for each day of the week. So based a user entered date in a text box, I'd like to copy all data from the ID field and all the data from the matching day field to another table. Ex. user enters 05/27/06 - copy all data from ID and all data from the field named Saturday.

If anyone could point me in the right direction, I'd appreciate it.

 

[r937]

insert
  into othertable
     ( ID
     , theDay )
select ID
     , iif(weekday([testbox])=1,SunColumn
     , iif(weekday([testbox])=2,MonColumn
     , iif(weekday([testbox])=3,TueColumn
     , iif(weekday([testbox])=4,WedColumn
     , iif(weekday([testbox])=5,ThuColumn
     , iif(weekday([testbox])=6,FriColumn
     , SatColumn)))))) as theDay
  from startingtable

r937.com | rudy.ca

 

[rkbatts]

Awesome. Thanks so much!