More Yes/No Help

[Rbutts25]

Is there a way to take a yes/no field and have it be changed from yes to no, and visaversa based on the date? I have developed an sql for this, but it is not working properly, does anyone have any suggestions?


Here is the sql:

SELECT tblMBUSAResourceList.Active
FROM tblMBUSAResourceList
WHERE ((([tblMBUSAResourceList]![Start Date])>1/1/2003) AND (([tblMBUSAResourceList]![End Date])>1/31/2003));


Ryan

 

[Fuga]

Do you think you could use an update query for this?

Fuga.

 

[Pat Hartman]

You haven't indicated how you want the value to be determined. However, the answer is yes but do it in the query. Do NOT save the yes/no value as that would be redundant.

 

[Rbutts25]

I am not sure how thatwould work. I am not familiar with an update query.

 

[Rbutts25]

Pat,

I do not follow what you are saying about not saving it as well as ding it in the query. I thought I had done it in the query.

Ryan

 

[Fuga]

I think the SQL looks like this. (I usually cheat and use the design view for these queries)
UPDATE yourtable SET yourtable.yourfield = Yes
WHERE........etc

I thought I had done it in the query

The update query will change the values in the table.

Fuga.

 

[Rbutts25]

Thank you for your help, I will let you know if I figure it out.

 

[Pat Hartman]

I do not follow what you are saying about not saving it as well as ding it in the query. I thought I had done it in the query.

- Fuga is advising you to save the calculated value and I am advising just the opposite. If you can calculate a value in a query, there is no need to store that value in the table. I really don't want to go into all the reasons why it is wrong to store calculated values. Search the archives and the knowledge base for articles on database design and normalization. All I can say is that it is especially wrong to store a calculated value that will change based on date. If you are using a date range to identify Active and you store that calculated value, the stored value will be inaccurate as soon as the date is outside the date range.

Your query is not setting a value for the Active field. Remove the Active field from your table definition and always calculate it in a query.

SELECT tblMBUSAResourceList.*, IIf([tblMBUSAResourceList]![Start Date] > "1/1/2003" AND [tblMBUSAResourceList]![End Date] > "1/31/2003", True, False) As ActiveFlg
FROM tblMBUSAResourceList;

Rather than using hard-coded dates you should probably be using parameters.