Dealing with a Null date field in an expression

[Tom via AccessMonster.com]

I have a query that I recieved alot of help with from this discussion board.
It works great, and Im greatfull for the help I got. The problem now is that
if the field its using is empty it expression will not work. Can anyone help
me change it so it will deal with null fields correctlly?

This is what the query looks like:
LetterOrVisitThisyear: IIf((DateDiff("yyyy",Date(),[LastVisitDate]) Mod 3)=0,
"Visit","Letter")

It checks the LastVisitDate field against the current date to see if it has
been three years since the last visit, if it hasn't then it tells the user to
send a letter, if it has then it tells the user to visit the site.

What I need is to add to it so that it deals with no entry in the
LastVisitDate field. If the LastVisitDate field is null then it should use
the lowest, (oldest date), record in the [DateSent] field out of the all the
records in the table that deal with that particular business. (Im not sure
how to isolate just the records with one business, eash business has several
[DateSent] records in the business table)

The problem is that the way it is now, if the [LastVisitDate] field in empty
then it only returns "Letter". I need it to return letter for the first two
years and then return "Visit" on the third year just as it would if the field
were not empty. The [DateSent] field will always have a entry.

Sorry its so hard for me to explain, thanks for helping.

Tom

 

[Chaim]

Wrap LastVisitDate in an Nz() function that uses DLookup to get the earliest
DateSent for the business in question. As in:

LetterOrVisitThisyear: IIf((DateDiff("yyyy",Date(),Nz([LastVisitDate],
DLookup("min(DateSent)", "YourTable", "busID = [A Business ID]")) Mod 3)=0,
"Visit","Letter")

Note 1. I am not guaranteeing that the parentheses in the above are all
paired properly.
Note 2. The last argument of the DLookup() would substitute the value of the
business ID being considered at the time. I.e., if BusinessID is one of the
fields that you are displaying, replace [A Business ID] with that field
name.

Good Luck!

 

[Tom via AccessMonster.com]

Chaim
Thanks, once again, for all your help. I owe you. I cant help you with
Access but if you ever need help with international fire code issues, let me
know.

I played with it and this is what I got to work;

LetterOrVisitThisyear: IIf((DateDiff("yyyy",Date(),Nz([LastVisitDate],
(Select Min([DateSent]) From TblSIP))) Mod 3)=0,"Visit","Letter")

Im having trouble with the DLookup portion. The problem is that of course the
expression looks at the whole table (all the DateSent records) and not just
the records that deal with that particular business. I tried putting in "From
TblSIP Where BusinessID = TblSIP.BusinessID, but I could not get it to work.

I tried this;

LetterOrVisitThisyear: IIf((DateDiff("yyyy",Date(),Nz([LastVisitDate],DLookup
((Select Min([DateSent]) From TblSIP Where BusinessID = TblBusiness.
BusinessID)))) Mod 3)=0,"Visit","Letter")

but it would not work.

Again thanks for all your help.

Tom

 

[Chaim]

Tom,

Checkout the DLookup page in Access Help. I have not used it often but it
sometimes is a convenient 'shorthand', rather than all the typing for a
'select' statement, especially in the Criteria line of the query grid.

Anyway, thanks for your kind words. I am not owed anything, either. This
newsgroup is much too much fun and so very educational. I am in the debt of
everyone who asks or answers.