date fields

[Jorgitopeter]

Hi people... how do you do?

I have a situation here and I can't realize how to solve it. May you help me please.

The thing is: I have a customer table where I write down the date when I give my customers "warnings". I give them the first warning if they forget calling me... if they don't call me during the next 7 days, I give them the second warning; if they don't call me during the next 7 days, I banned them from my list. In the table I attached here, I have 4 fields: the customer ID, the "1st warning" date field, the "2do warning" date field and the "banned" date field.

I want to write 2 different scripts to find:

1)which customers haven't called me during the week after I gave them the 1st warning;

2)which customers haven't called me during the week after I gave them the 2nd warning.

The first problem I have is that I don't realize how to find an empty date field, because with "" doesn't work, does it?

I hope I am clear with my bad English. Thanks for all your help people, you're the best.

base.zip

[Lee Smith]

Some questions first.

You are showing Warning 1, Warning 2, and Banned dates.

Do you have an initial contact date? Do you have a second date for saying that they have contacted you too?

Or, is the Warning one date the contact date, and you want to do a search for the 7 days based on that?

i.e. if today is the Warning date, you would want to call on next Sat to tell them "get with it", if they haven't contacted you?

Lee

Edited April 6, 2008 by Guest

[Jorgitopeter]

Hi Lee, thanks for your help.

First of all, I'm sorry but yesterday I attached an old version of the file : . Here I attach the new one. It does have an 'initial contact date' field and a 'costumer final contact' field too.

The example: I called my costumer today April 6th to let him know that he has to call me in 7 days (1st warning)... if he doesn't call me on April 13th or before, on April 14th I call him again, and I give him 7 more days... if he doesn't call on April 21st or before, on April 22nd I put him in my 'banned list'.

So I'd want to write scripts to find which costumer need the 1st warning (because the 7 days since the first contact call, past away), which need the 2nd warning (because the 7 days since the 1st warning, past away), and which must be included into the banned list (because the 7 days since the 2nd warning past away).

Well, thanks for all your help. I wouldn't be able to do this whithout fellows like you! :

Jorge

PD: an extra question: how do you refer to an 'empty date field'? I mean, if you want to write into a calculation formula that when a datefield is empty do something, how do you write 'empty date field'? Because with "" doesn't work,does it?

base.zip

[Lee Smith]

See if the two calculations that I added will do what you want.

base1.fp7.zip

[Jorgitopeter]

Thanks a lot Lee... In fact the calculations you gave me were part of the solution... I use the function 'IsEmpty' you showed me, and I defined differents variable to be set up with the find-script. It works great. Thanks a lot.