IIF AND Statement

[Prufrock]

I have a preview form based on a query in a database. I have added the fields from two tables I want to the query. In the form I have the results for each unique client number appear when I put the client number in. I need a text field (unbound) with a calculation in it to determine whether a client is a current one or not. This is based on whther they use a service within the last six weeks. I am trying to establish this with an IIf statement.

If the client was getting a particular service (this is indicated by a Yes/No field called Service in one area of the database)and the date they last used that service (indicated by a date field called LastVisit in another area of the database) was less than 6 weeks I want the unbound text box to say YES.

Current attempts at IIF include
=IIF(([Service]=True)AND([LastVisit]=<Date()-42),"Yes","No)

This says there is a syntax error and I have checked a few other ideas on Net. Can anyone assist please?

 

[PHV]

The syntax error:
=IIF(([Service]=True)AND([LastVisit]=<Date()-42),"Yes","No[!]"[/!])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Prufrock]

Sorry that was my oversight in typing it in. Still not operational at the moment if there are any further ideas.

 

[PHV]

Hopefully Service and LastVisit are the name of fields in the form's underlaying query, aren't they ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Prufrock]

Yes they are I have checked all thoroughly. It simply states there is a syntax error in the statement as typed
=IIF(([Service]=True)AND([LastVisit]=<Date()-42),"Yes","No")
But I can't see where it is.

 

[PHV]

You may try this (in the ControlSource):

=IIf([Service]=True And [LastVisit]<=(Date()-42),"Yes","No")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ttscanada]

Try adding brackets around the AND statement

=IIf(([Service]=True And [LastVisit]<=(Date()-42)),"Yes","No")

or
=IIf(([Service]=True) And ([LastVisit]<=(Date()-42)),"Yes","No")

 

[randy700]

Your sample post shows no space before or after the AND.


Randy

 

[djj55]

You could try:

=IIF(Service = True,IIF(LastVisit<=(Date()-42)), "YES", "NO"), "NO")

djj