Question Problems with automatic update

teresamichele

Registered User.
Local time
Today, 13:31
Joined
Oct 18, 2010
Messages
13
I am using Access 2007 on Windows XP Professional.

That out of the way, here is my issue. It involves forms and reports and tables so the General forum seemed as good as any! :)

I have a form that people use to enter data to a "Project Entry" table. All of my forms, reports, etc, feed from this table.

On my Project Entry form, I have the following code that executes when someone types in the due date:

=IIf(DateDiff("d",Date(),[Date Due])<14,"A",IIf(DateDiff("d",Date(),[Date Due])<30,"B",IIf(DateDiff("d",Date(),[Date Due])<60,"C","D")))

It works perfectly - or so I thought.

Let's say when the project is entered, it's a D priority. When it becomes a C, my reports (which also use that code for the text box of priority), will update, but the actual "Project Entry" table does not.

This is now a problem, because I want people to be able to sort/search by priority code. Is there a way to fix this short of going in every morning and manually pushing the Project Entry form to update that field? :confused:

Thank you! :)

Teresa
 
You are using a bound form based on a query or table?

Let's say your query looks like this:

select id, due_date
from TableA

You can add the priority field:

SELECT ID, [Date Due], IIf(DateDiff('d',Date(),[Date Due])<14,'A',IIf(DateDiff('d',Date(),[Date Due])<30,'B',IIf(DateDiff('d',Date(),[Date Due])<60,'C','D'))) AS Priority
FROM Table1;

The priority is determined in the query instead of the form. Since it is now part of the query you can select on it.

Store the query and use it as a basis for the form instead of using it as a rowsource for your form. A query is easier to change than a rowsource.

HTH:D
 
I'm trying to understand what you're saying, but I really don't. I added the "SELECT ID" bit where I thought it went but it's saying that it's invalid syntax and I may be missing something.

If I'm in Query Design View, where would it go? :) Thank you!
 
Show me what your query looks like and we'll work from there.

Code:
SELECT ID, [Date Due], [COLOR="Blue"]IIf(DateDiff('d',Date(),[Date Due])<14,'A',IIf(DateDiff('d',Date(),[Date Due])<30,'B',IIf(DateDiff('d',Date(),[Date Due])<60,'C','D'))) AS Priority[/COLOR]
FROM Table1;
You will only have to add the blue part.

I used the ID because every table should have one, or something similar.

HTH:D
 

Users who are viewing this thread

Back
Top Bottom