1 day from becoming overdue and or past due records at the top

  • Thread starter Thread starter Iram
  • Start date Start date
I

Iram

Hello.
I have a continuous form with about 10 fields. Of those there are three
fields in particular that I need help on...

"Date Created"
"Field1DueDate"
"Field2DueDate"

I need to sort the continous form by "Date Created"-Ascending.

However I need the two due date fields above to go to the top of the list
where they can be visible. At which point I plan on putting conditional
formatting to change their background colors to make them really really
visible. The problem is that the continous form can grow 40 to 50 records per
day but we need to keep an eye on the records that are One day from being
past due and or records that are already past due.

Can you help me figure this out?

Your help is greatly appreciated.

Thanks.
Iram/mcp
 
Create a query using your table.

Type an expression like this into the Field row (as one line):

IsDue: IIf(Field1DueDate Is Null AND Field2DueDate Is Null, False,
(Field1DueDate <= Date()) OR (Field2DueDate <= Date())

Move it to the left of the [Date Created] column (so it's your first sorting
column), and sort on both.

The expression yields True (-1) if either date has been reached, otherwise
False (0.) You can add +1 or -1 after the Date() (both instances) if you
need to adjust it by a day.

If one of these records could have multiple due dates (as suggested by your
Field1DueDate, Field2DueDate), it might be better to use a related table to
hold the reminder dates. That's usually the right approach when you see
repeating field names like you have here.
 
Here's a progress report...

1. With this I get an error "The expression you entered has an invalid .
(dot) or ! operator or invalid parentheses.
IsDue: IIf(Action Due Date Is Null And Contact CP/NP Due Date Is Null,
False, (Action Due Date <=Date()) Or (Contact CP/NP Due Date <=Date())

2. So then I removed the "IsDue:" then I got the error: The expression you
entered is missing a closing parenthesis, bracket (]), or vertical bar (|).
So I added a closing perenthesis at the end.
IIf("Action Due Date" Is Null And "Contact CP/NP Due Date" Is
Null,False,("Action Due Date"<=Date()) Or ("Contact CP/NP Due Date"<=Date()))

3. When I try to run the query I get the error "You can't set criteria
before you add a field or expression, or delete the criteria". It won't let
me save and close the query.

What do I do now?

Iram/mcp


Allen Browne said:
Create a query using your table.

Type an expression like this into the Field row (as one line):

IsDue: IIf(Field1DueDate Is Null AND Field2DueDate Is Null, False,
(Field1DueDate <= Date()) OR (Field2DueDate <= Date())

Move it to the left of the [Date Created] column (so it's your first sorting
column), and sort on both.

The expression yields True (-1) if either date has been reached, otherwise
False (0.) You can add +1 or -1 after the Date() (both instances) if you
need to adjust it by a day.

If one of these records could have multiple due dates (as suggested by your
Field1DueDate, Field2DueDate), it might be better to use a related table to
hold the reminder dates. That's usually the right approach when you see
repeating field names like you have here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Iram said:
Hello.
I have a continuous form with about 10 fields. Of those there are three
fields in particular that I need help on...

"Date Created"
"Field1DueDate"
"Field2DueDate"

I need to sort the continous form by "Date Created"-Ascending.

However I need the two due date fields above to go to the top of the list
where they can be visible. At which point I plan on putting conditional
formatting to change their background colors to make them really really
visible. The problem is that the continous form can grow 40 to 50 records
per
day but we need to keep an eye on the records that are One day from being
past due and or records that are already past due.

Can you help me figure this out?

Your help is greatly appreciated.

Thanks.
Iram/mcp
Click to expand...
Click to expand...
 
Since your field name has spaces and other "special" characters in it you must
surround the name with square brackets. Also, it appears that you have
unbalanced parentheses in the expression you must have an equal number of (
and ) and they must be properly paired.

Try the following

IsDue: IIf([Action Due Date] Is Null And [Contact CP/NP Due Date] Is Null,
False, ([Action Due Date] <=Date()) Or ([Contact CP/NP Due Date] <=Date()))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Here's a progress report...

1. With this I get an error "The expression you entered has an invalid .
(dot) or ! operator or invalid parentheses.
IsDue: IIf(Action Due Date Is Null And Contact CP/NP Due Date Is Null,
False, (Action Due Date <=Date()) Or (Contact CP/NP Due Date <=Date())

2. So then I removed the "IsDue:" then I got the error: The expression you
entered is missing a closing parenthesis, bracket (]), or vertical bar (|).
So I added a closing perenthesis at the end.
IIf("Action Due Date" Is Null And "Contact CP/NP Due Date" Is
Null,False,("Action Due Date"<=Date()) Or ("Contact CP/NP Due Date"<=Date()))

3. When I try to run the query I get the error "You can't set criteria
before you add a field or expression, or delete the criteria". It won't let
me save and close the query.

What do I do now?

Iram/mcp


Allen Browne said:
Create a query using your table.

Type an expression like this into the Field row (as one line):

IsDue: IIf(Field1DueDate Is Null AND Field2DueDate Is Null, False,
(Field1DueDate <= Date()) OR (Field2DueDate <= Date())

Move it to the left of the [Date Created] column (so it's your first sorting
column), and sort on both.

The expression yields True (-1) if either date has been reached, otherwise
False (0.) You can add +1 or -1 after the Date() (both instances) if you
need to adjust it by a day.

If one of these records could have multiple due dates (as suggested by your
Field1DueDate, Field2DueDate), it might be better to use a related table to
hold the reminder dates. That's usually the right approach when you see
repeating field names like you have here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Iram said:
Hello.
I have a continuous form with about 10 fields. Of those there are three
fields in particular that I need help on...

"Date Created"
"Field1DueDate"
"Field2DueDate"

I need to sort the continous form by "Date Created"-Ascending.

However I need the two due date fields above to go to the top of the list
where they can be visible. At which point I plan on putting conditional
formatting to change their background colors to make them really really
visible. The problem is that the continous form can grow 40 to 50 records
per
day but we need to keep an eye on the records that are One day from being
past due and or records that are already past due.

Can you help me figure this out?

Your help is greatly appreciated.

Thanks.
Iram/mcp
Click to expand...
Click to expand...
Click to expand...
 
I still get error #1 (see previous postings) so I took out the IsDue: but I
still can't save the query because of error #3
Also I copied your code to a clean Field row that is not related to any
field in the table in question, and I did put a Check in the "Show" checkbox.

Iram/mcp


John Spencer said:
Since your field name has spaces and other "special" characters in it you must
surround the name with square brackets. Also, it appears that you have
unbalanced parentheses in the expression you must have an equal number of (
and ) and they must be properly paired.

Try the following

IsDue: IIf([Action Due Date] Is Null And [Contact CP/NP Due Date] Is Null,
False, ([Action Due Date] <=Date()) Or ([Contact CP/NP Due Date] <=Date()))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Here's a progress report...

1. With this I get an error "The expression you entered has an invalid .
(dot) or ! operator or invalid parentheses.
IsDue: IIf(Action Due Date Is Null And Contact CP/NP Due Date Is Null,
False, (Action Due Date <=Date()) Or (Contact CP/NP Due Date <=Date())

2. So then I removed the "IsDue:" then I got the error: The expression you
entered is missing a closing parenthesis, bracket (]), or vertical bar (|).
So I added a closing perenthesis at the end.
IIf("Action Due Date" Is Null And "Contact CP/NP Due Date" Is
Null,False,("Action Due Date"<=Date()) Or ("Contact CP/NP Due Date"<=Date()))

3. When I try to run the query I get the error "You can't set criteria
before you add a field or expression to the Field row". It won't let
me save and close the query.

What do I do now?

Iram/mcp


Allen Browne said:
Create a query using your table.

Type an expression like this into the Field row (as one line):

IsDue: IIf(Field1DueDate Is Null AND Field2DueDate Is Null, False,
(Field1DueDate <= Date()) OR (Field2DueDate <= Date())

Move it to the left of the [Date Created] column (so it's your first sorting
column), and sort on both.

The expression yields True (-1) if either date has been reached, otherwise
False (0.) You can add +1 or -1 after the Date() (both instances) if you
need to adjust it by a day.

If one of these records could have multiple due dates (as suggested by your
Field1DueDate, Field2DueDate), it might be better to use a related table to
hold the reminder dates. That's usually the right approach when you see
repeating field names like you have here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello.
I have a continuous form with about 10 fields. Of those there are three
fields in particular that I need help on...

"Date Created"
"Field1DueDate"
"Field2DueDate"

I need to sort the continous form by "Date Created"-Ascending.

However I need the two due date fields above to go to the top of the list
where they can be visible. At which point I plan on putting conditional
formatting to change their background colors to make them really really
visible. The problem is that the continous form can grow 40 to 50 records
per
day but we need to keep an eye on the records that are One day from being
past due and or records that are already past due.

Can you help me figure this out?

Your help is greatly appreciated.

Thanks.
Iram/mcp
Click to expand...
Click to expand...
Click to expand...
 
Back
Top