Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

IIF AND Statement

Status
Not open for further replies.

Prufrock

Instructor
Sep 4, 2002
77
AU
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?
 
The syntax error:
=IIF(([Service]=True)AND([LastVisit]=<Date()-42),"Yes","No[!]"[/!])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Sorry that was my oversight in typing it in. Still not operational at the moment if there are any further ideas.
 
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
 
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.
 
You may try this (in the ControlSource):
Code:
=IIf([Service]=True And [LastVisit]<=(Date()-42),"Yes","No")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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")
 

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


Randy
 
You could try:
Code:
=IIF(Service = True,IIF(LastVisit<=(Date()-42)), "YES", "NO"), "NO")
djj
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top