Recordsource Coding Help (1 Viewer)

[MNM]

Greetings again,
Well now that the Months in Service coding is working properly (99.999%), I would like to use this calculation a bit further.
I've set up another textbox, Current Contract (Textbox name: CCN), with the coding:

=Int((MIS/12)+1)

This works fine as well.
It's using this result that's causing the trouble.
I want to generate the current contract's start and end dates.

So far this is what I have as coding for the start date:

=IFF(IsNull(Instructor!ResignationDate),(Format(Instructor!NichiiGakkanStart,"yyyy")+[CCN]-1) & "/" & (Format(Instructor!NichiiGakkanStart,"mm/dd"),"N/A"))

What I'm trying to accomplish is this:
If no resignation date exists, then modify the year of the original start date by the number of contracts minus one(1). The moth and day part will remain the same. If there is a resignation date, then display 'N/A'.
The end date would add the full CCN value to the year and subtract one(1) from the day.

Looking at the partial screen shot, it displays '#NAME?'
Any assistance is appreciated.
MNM

 

[Uncle Gizmo]

Try:

=IFF(IsNull(Instructor!ResignationDate),"YES","N/A"))

What results do you get?

 

[CazB]

try replacing
+CCN-1
with
+Int(MIS/12)
in your code.... what does that do?

... sometimes it doesn't like you referring to calculated text boxes in another calculation...

 

[MNM]

Try:

=IFF(IsNull(Instructor!ResignationDate),"YES","N/A"))

What results do you get?

First was a message box about too many closing parenthesis.
Once that was fixed, the result was the same, #NAME?.

This would also rule out CazB's suggestion as well.

From SS64.com, I know the IFF and IsNull are correct. The ResignationDate is formatted as Short Date (yyyy/mm/dd in Japan)

MNM

 

[MNM]

try replacing
+CCN-1
with
+Int(MIS/12)
in your code.... what does that do?

... sometimes it doesn't like you referring to calculated text boxes in another calculation...

See my reply to Uncle Gizmo, If his idea didn't help, neither would yours, But I appreciate it nonetheless.

MNM

 

[ChrisO]

Is there such a thing as an IFF function?

Chris.

 

[MNM]

Is there such a thing as an IFF function?

Chris.

Chris,
Go here:
http://ss64.com/access/iif.html

The site has Access, SQL, and VB script commands
MNM

 

[MNM]

Is there such a thing as an IFF function?

Chris.

Oh! A reply in another forum was more direct about the typo.

FYI: in mathematics, there is an 'iff' statement, which means 'if and only if'.

I'll correct it and report back
Cheers,
MNM

 

[MNM]

Oh! A reply in another forum was more direct about the typo.

FYI: in mathematics, there is an 'iff' statement, which means 'if and only if'.

I'll correct it and report back
Cheers,
MNM

OK. Tried Uncle Gizzmo's test.

=IIf(IsNull([Instructor]![ResignationDate]),"YES","N/A")

I get '#NAME?'

MNM

 

[CazB]

Question: why do you need the 'instructor' bit in the iif statement?

Is it not just
=IIf(IsNull([ResignationDate]),"YES","N/A")
as the test?

 

[MNM]

Question: why do you need the 'instructor' bit in the iif statement?

Is it not just
=IIf(IsNull([ResignationDate]),"YES","N/A")
as the test?

Thanks CazB.
That was it. The test worked correctly in both cases.
It ended up there because I navigated to it in the expression builder.
Now that the test works, I can focus on creating the actual contract start and end dates.

Cheers,
MNM

 

[MNM]

Forum Members,
This little problem is solved. I'd like to thank everyone who contributed to the final solution.

Below are the codes used to calculate the start and and dates of the Instructor's current contract, which is 365 (366 for leap year) days long.

Current Contract Start Date

=IIf(IsNull([ResignationDate]),Year([NichiiGakkanStart])+([CCN]-1) & "/" & Month([NichiiGakkanStart]) & "/" & Day([NichiiGakkanStart]),"N/A")

Current Contract End Date

=IIf(IsNull([ResignationDate]),Year([NichiiGakkanStart])+([CCN]) & "/" & IIf(Day([NichiiGakkanStart])=1,Month([NichiiGakkanStart])-1,Month([NichiiGakkanStart])) & "/" & Day([NichiiGakkanStart]-1),"N/A")

As a side note: There are two times in which Instructors started on the first of the month (first day of training), one on April 1st and the other on July 1st. The end date displays as March 31st and June 30th respectively. This is correct. I assume that the Day() function also accounts for the month as well, so and calculations will result in the correct day.

Regards (and TIA for the future) and have a good weekend,
MNM

 

[MNM]

Epologue
A "Duh!" Moment

The current contract start date shouldn't be "N/A", but if the Instructor resigns, the contract end date can. With this in mind, the start date code has been modified to:

=Year([NichiiGakkanStart])+Int([MIS]/12) & "/" & Month([NichiiGakkanStart]) & "/" & Day([NichiiGakkanStart])

As an asside, the Current Contract Number (CCN) needed modification

=Int(([MIS]/12)+1)

This code increments Instructors who finish a contract, but don't renew by one, which inturn increases the year of the 'current contract' by one, which is wrong.
This code fixes this glitch.

=IIF(IsNull([ResignationDate]),Int(([MIS]/12)+1), Int((([MIS]-1)/12)+1))

This is maddeningly fun!
MNM