Adding some 'date' logic to my text field...

[brent01]

Firstly, to all who answered my query regarding truncated text in a formatted memo field. I have resolved this problem... Thanks for the help it was really appreciated!

Problem.. I have a form with a text field, and I would like a have single word comment added to the text field, if certain criteria are met. The criteria being two specific days of month (19th or 20th)/ day of week and the value of another field on the form, which is a combi box that can only have the value of MORNING OR EVENING.

To explain further...

The combi field must = EVENING. If the day of month is 19th and day of week is Fri, then automatically add a comment of *** STATEMENTS *** to the text field.

Else

The combi field must = EVENING. If the day of month is 20th
and day of week is NOT Saturday or Sunday, then automatically add the comment of *** STATEMENTS *** to the text field.

I know of the Day(Date) function and the Weekday(Date) function, but I don't know how to construct the full logic statement, along with checking for a field value of EVENING, also I don't know how to then get the comment added to the text field.


I hope I've explained things, okay.. not to good at this.

Any help would be most welcome.

 

[JerryDennison]

Are you saying you want to store the word "****STATEMENTS****" in your table when these conditions are met? I strongly encourage you NOT to do this. This violates the Second Normal Form (2NF). This does not mean that you can't have this be displayed on a form or report whenever and wherever you need it. One way is to create a function that will evaluate the criteria and return the desired result. Another is to simply use nested IIF() functions in a query field to do the same thing:

Statements: IIF([Morning/Evening] = "Evening" And ((DatePart("d", [DateField]) = 19 And DatePart("w", [DateField]) = 6) Or (DatePart("d", [DateField]) = 20 And Not (DatePart("w", [DateField]) = 7 Or DatePart("w", [DateField]) = 1)), "*****STATEMENTS*****", Null)