Help--Tab Form and formula

[Madmax]

I have two problems,

The first is a tab form. I have a main personnel form that is broken up into five tabs. the form itself has nothing but a field that calculates first name last name ssn and rank. In each tab I drag and dropped a form to create a sub form. Everything linked up fine. Till I got to the fifth tab. On the fifth tab the info is blank. However if I type in a field it pulls up the SSN automatically and leaves everything else blank. then if I try to close it it wont let me because one of the fields is showing blank that on another field (first put in field) there office symbol is entered and required because it is attached to another field. It is like it isnt loading the information for some reason. The child and master fields are correct. I cant figure out what is going wrong. I even tried recreating the form from scratch. Just dont know what to do. Suggestions?

Second thing is. What is wrong with the below formula? I think it is the math.


If([PRP Position]="Postured to PRP Position" or "Certified" or "Temp Decert", If([Closed Date]+year(4)+Month(10)<Today(),"[Security Clearance Needed]=1,"[Security Clearance Needed]=0),If([Closed Date]+year(4)+Month(10)<Today(),"[Security Clearance Needed]=1,"[Security Clearance Needed]=0))

It probable has something to do with Year and month never tried this before.

[alansidman]

Firstly, in Access the if statement requires two i's. =IIF

Secondly, an or statement requires that you repeat the field name so that the first part of your statement should read:
IIf([PRP Position]="Postured to PRP Position" or [PRP Position]="Certified" or [PRP Position]= "Temp Decert"

Thirdly, a normal IIF statement takes on the structure:

IIF(Criteria, result if true, result if false). Yours does not give a result for the first criteria, but moves directly to a new IIF statement. You can nest iif statements but they must follow this format.

Ref: http://www.techonthenet.com/access/f...vanced/iif.php


Alan

[Madmax]

The result is in the second IIF Statement and third IIf Statement.

The literal reading I am looking for is this.


If they are PRP (the status) then check against the sec clear close date and if it is older than 4 years 10 months check it as 1 (needed) if it isnt then check it as 0(Not Needed). If they are not PRP then check against the sec clear close date and if it is older than 9 years 10 months check it as 1 if it isnt then check it as 0. Lol I am missing two Quotes in it just realized. So basically, this will work?


IIf([PRP Position]="Postured to PRP Position" or [PRP Position]="Certified" or [PRP Position]="Temp Decert", IIf([Closed Date]+year(4)+Month(10)<Today(),"[Security Clearance Needed]=1","[Security Clearance Needed]=0"),IIf([Closed Date]+year(4)+Month(10)<Today(),"[Security Clearance Needed]=1","[Security Clearance Needed]=0"))


looks alot better lol is it right?

[alansidman]

looks alot better lol is it right?

A better question is: When applied does it work and give the expected results? Only you can answer that.

Alan

[Madmax]

Lol answer was no

first I had this
=IIf([Personnel Information]![PRP Position]="Postured to PRP Position" Or [Personnel Information]![PRP Position]="Certified" Or [Personnel Information]![PRP Position]="Temp Decert",IIf([Personnel Information]![Security Clearance Closed Date]+Year(4)+Month(10)<Today(),"[Personnel Information]![Security Clearance Needed]=1","[Personnel Infromation]![Security Clearance Needed]=0"),IIf([Me]![Security Clearance Closed Date]+Year(4)+Month(10)<Today(),"[Personnel information]![Security Clearance Needed]=1","[Personnel Information]![Security Clearance Needed]=0"))

Then

=IIf([Me]![PRP Position]="Postured to PRP Position" Or [Me]![PRP Position]="Certified" Or [Me]![PRP Position]="Temp Decert",IIf([Me]![Security Clearance Closed Date]+Year(4)+Month(10)<Today(),"[Personnel Information]![Security Clearance Needed]=1","[Personnel Infromation]![Security Clearance Needed]=0"),IIf([Me]![Security Clearance Closed Date]+Year(4)+Month(10)<Today(),"[Personnel information]![Security Clearance Needed]=1","[Personnel Information]![Security Clearance Needed]=0"))


Its on a subform but so are the controls to change. I am trying to put this in expression builder no luck yet. Personnel Information is my table BTW with the field. Would it be easier to just update the checkmark on the subform maybe? thoughts?

[Madmax]

I attached the JPeg snip of the error if it helps

[Madmax]

Okay this is the latest still stumped


Private Sub Text91_GotFocus()
If Me![PRP Position Status] = "Postured to PRP Position" Or Me![PRP Position Status] = "Certified" Or Me![PRP Position Status] = "Temp Decert" = True Then
If Me![Security Clearance Closed Date] + Year(4) + Month(10) < mydate = True Then
Me![Security Clearance Needed] = 1
Else
Me![Security Clearance Needed] = 0
End If
Else
If Me![Security Clearance Closed Date] + Year(9) + Month(10) < mydate = True Then
Me![Security Clearance Needed] = 1
Else
Me![Security Clearance Needed] = 0
End If
End If
End Sub


Also Tried this
IIf(Me![PRP Position Status] = "Postured to PRP Position" Or Me![PRP Position Status] = "Certified" Or Me![PRP Position Status] = "Temp Decert" = True,IIf(DateAdd(Month,58,Me![Security Clearance Closed Date])< mydate = True,Me![Security Clearance Needed] = 1, Me![Security Clearance Needed] = 0), IIf (DateAdd(Month,58,Me![Security Clearance Closed Date])< mydate = True,Me![Security Clearance Needed] = 1,Me![Security Clearance Needed] = 0))

As VBA

Still wont work.

Also Tried this
IIf(Me![PRP Position Status] = "Postured to PRP Position" Or Me![PRP Position Status] = "Certified" Or Me![PRP Position Status] = "Temp Decert", IIf(DateAdd(Month,58,Me![Security Clearance Closed Date])< mydate,Me![Security Clearance Needed] = 1, Me![Security Clearance Needed] = 0), IIf (DateAdd(Month,58,Me![Security Clearance Closed Date])< mydate,Me![Security Clearance Needed] = 1,Me![Security Clearance Needed] = 0))