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

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
 
A

Allen Browne

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.
 
I

Iram

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...
 
J

John Spencer

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

Iram

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query for Month and Day against Date() 2
sort tasks as overdue, then due, then no date? 1
Calculate records with dates from past week and month 4
Select query to display records closest to today's date 1
Change font on past due date selected from ocxCalendar to appear r 1
Long Date - Can't pull records accurately 3
adding new records from one table to an existing table 3
Select row by ID to nearest date, future (or past if no future dat 4

Top