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