Combine IIf Than Statements?

[Jelloshot1]

Hi All,
Would like to say first that I have found this site and it's Q&A very helpfull! I'm learning more everyday.
My question today is about If Than Statements and can they be combined as in

If (IsNull(Forms!MyForm!FRI) And If(IsNull(Forms!MyForm!SAT) Then
RDO="F/S"
End If

I have this code in the On Current part of a form properties and it seems to work fine until I try to TAB to the next record then I get a RunTime error saying that I have referenced an invalid property form/report.

Any help provided is greatly appreciated.
Thanks, JelloShot

 

[JohnLowell]

Try This:

If IsNull(Forms!MyForm!FRI) And IsNull(Forms!MyForm!SAT) Then
RDO="F/S"
End If

 

[Jelloshot1]

Hi again,
Tried that and it didn't work. Here is the entire code I'm using. it works fine as long as I scroll from one record to the next but I get the error (at the bottom of the code) when I try to tab into the next record. I know it is probably very primitive and has no error code in it (I still haven't figured that part out yet) but it's the best I could do given limited experience. Hope someone can point me in the right direction!

Private Sub Form_Current()

If Forms!frmStaffInfo!frmBidSubform!Roto < &quot;V499&quot; Then
Forms!frmStaffInfo!Shift = &quot;1st Shift&quot;
ElseIf Forms!frmStaffInfo!frmBidSubform!Roto > &quot;V701&quot; Then
Forms!frmStaffInfo!Shift = &quot;3rd Shift&quot;
Else: Forms!frmStaffInfo!Shift = &quot;2nd Shift&quot;
End If

If (IsNull(Forms!frmStaffInfo!frmBidSubform!FRI)) And (IsNull(Forms!frmStaffInfo!frmBidSubform!SAT)) Then
Forms!frmStaffInfo!RDO = &quot; F/S &quot;
ElseIf (IsNull(Forms!frmStaffInfo!frmBidSubform!SAT)) And (IsNull(Forms!frmStaffInfo!frmBidSubform!SUN)) Then
Forms!frmStaffInfo!RDO = &quot; S/S &quot;
ElseIf (IsNull(Forms!frmStaffInfo!frmBidSubform!SUN)) And (IsNull(Forms!frmStaffInfo!frmBidSubform!MON)) Then
Forms!frmStaffInfo!RDO = &quot; S/M &quot;
ElseIf (IsNull(Forms!frmStaffInfo!frmBidSubform!MON)) And (IsNull(Forms!frmStaffInfo!frmBidSubform!TUE)) Then
Forms!frmStaffInfo!RDO = &quot; M/T &quot;
ElseIf (IsNull(Forms!frmStaffInfo!frmBidSubform!TUE)) And (IsNull(Forms!frmStaffInfo!frmBidSubform!WED)) Then
Forms!frmStaffInfo!RDO = &quot; T/W &quot;
ElseIf (IsNull(Forms!frmStaffInfo!frmBidSubform!WED)) And (IsNull(Forms!frmStaffInfo!frmBidSubform!THU)) Then
Forms!frmStaffInfo!RDO = &quot; W/TH &quot;
ElseIf (IsNull(Forms!frmStaffInfo!frmBidSubform!THU)) And (IsNull(Forms!frmStaffInfo!frmBidSubform!FRI)) Then
Forms!frmStaffInfo!RDO = &quot; TH/F &quot;
Else: Forms!frmStaffInfo!RDO = &quot; &quot;
End If

End Sub

Run-Time error '2455'

You entered an expression that has an invalid reference to the property Form/Report.
The property may not exist or may not apply to the object you specified.

 

[JohnLowell]

Change Else: Forms!frmStaffInfo!RDO = &quot; &quot;
to Else Forms!frmStaffInfo!RDO = &quot; &quot;

 

[genomon]

Yowch! First of all, why not use a Select...Case for the topmost structure:
Select Case Forms!frmStaffInfo!frmBidSubform!Roto
Case Is < &quot;V499&quot;
Forms!frmStaffInfo!Shift = &quot;1st Shift&quot;
Case Is > &quot;V701&quot;
etc...
Case Else
etc...
End Select

Using the > < operators to compare strings is not a great idea, but will probably work here since the preliminary alpha values are equal.
For the stuff that comes after, try removing the very last Else: statement and replace it with Forms!frmStaffInfo!RDO = &quot; &quot; BEFORE the initial &quot;IF&quot;. This will have the effect of initializing the field to an empty string (NOT a null) before you look for a match; if none is found it will just stay that way, and may eliminate the 2455 error. Otherwise do a search on &quot;Debug&quot; in help... Good luck!

 

[Jelloshot1]

OK, here goes again....

I changed the
Else: Forms!frmStaffInfo!RDO = &quot; &quot; to
Else Forms!frmStaffInfo!RDO = &quot; &quot;

Didn't work. BTW, I don't know why access placed the &quot;:&quot; after the original &quot;Else&quot;, but I tried to remove it and it kept coming back. Also tried moving the

Forms!frmStaffInfo!RDO = &quot; &quot;

to before the initial If Statement as suggested with no luck.
Also tried Select Case and this worked, but again, only as long as I didn't try to &quot;TAB&quot; to the next record. In which case I got the same error message as before. Maybe I'm going about this all wrong..I'll try to explain.

A separate subform contains an employees specific job. The &quot;Roto&quot; indicates what Shift they work as well as where they are found on a schedule. The specific job indicates what days off they have (by being null for those days). What I need is for this information, RDO (days Off) and Shift to appear on a main form. If more information is needed, let me know.

Thanks in advance for all your help!
Mary

 

[xeotech]

Mary,

>I really don't think this should be so complicated!!!

You are right! The following has a name .. it's called spaghetti code!!<g>

************************
If (IsNull(Forms!frmStaffInfo!frmBidSubform!FRI)) And (IsNull(Forms!frmStaffInfo!frmBidSubform!SAT)) Then
Forms!frmStaffInfo!RDO = &quot; F/S &quot;
ElseIf (IsNull(Forms!frmStaffInfo!frmBidSubform!SAT)) And (IsNull(Forms!frmStaffInfo!frmBidSubform!SUN)) Then
Forms!frmStaffInfo!RDO = &quot; S/S &quot;

etc, etc ..

*************************

.. and it's no wonder you are confused. Any one else would be .. including the computer!

If you must use If Then .. Else If Then statements in this way, remember to close the statements properly ( this is why colcons are appearing as the interpreter is trying to make sense of what you have written ) and also comment your code so you know where you are.

However, you would be very wise to redo this code from scratch possibly using Select Case Statements as genomon has suggested. You'll be very, very glad you did .. and save on headaches, keyboard wear and tear, eye strain ...

Regards,


Chris
Xeotech

 

[Jelloshot1]

Hi Chris,

I get the spaghetti code. In fact I recently read about it in a Good Coding Practice post in this forum. I'm not new to Access, but am relatively new to coding. This all started out as a &quot;hobby&quot; for me. I work for a state institution and while I was on 3rd shift I did a lot of updating to the db we use. While playing with the program I found ways of making my job easier and was given the leway to make whatever changes I needed. After 2 years, and countless hours of self-education I now have a day shift job (yippy) editing/revising and writing new db's. While this is wonderful and I love it, there is still soooooo much I don't know or understand.

I'm going to start over as you suggested. Select Case is new to me and maybe when I used it before I didn't write it correctly, but I'm willing to give it a try again. I have a couple of questions....actually I have tons of them <g>. First, is the On Current event the correct place for this code? And second, what do you mean by closing the If Then Else Statements correctly? And how do you add comments to code? I'm sure I'll have more questions by the time I get home today.......

Thanks so much,
Mary

 

[xeotech]

Hi Mary,

>I'm going to start over as you suggested.

Good girl!<g> You are in good comapny with spaghetti coding, we are all guilty of this at some stage.
The trick is to realise when it's getting totally out of control .. If you are trying to do something that on the face of it appears simple and you end up wriing line upon line upon line of code ... then it's time to mentally tell
yourseldf of for being stupid and start over.

>Select Case is new to me and maybe when I used it before I >didn't write it correctly, but I'm willing to give it a >try again.

Only way to learn.

>I have a couple of questions....actually I have tons of >them <g>. First, is the On Current event the correct place >for this code?

That depends on the effect you are after. Look up On Current Event in Access Help if not sure.

>And second, what do you mean by closing the If Then Else >Statements correctly?

If you open an If statement then you close it with End if

If a=21 then
'do this statement;
'do this statement;
End If

It's not always necessary but it can add readability to your code.

And how do you add comments to code?

As above.. precede a line of text with the apostrophe,i.e '
The interpreter ignores the line(s) Helps you know what you did and why!

Hope that helps.

>I'm sure I'll have more questions by the time I get home >today.......

I have questions all day everysay on a variety of topics..
just wish I had all the answers.

Cheers.


Chris.

PS I don't always log on to the forums regularly .. I have an aol screen name you can use if you wish ( chromium salt ), or a standard pop3: support@xeotech.co.uk

 

[Jelloshot1]

Well Chris,

I started over. Then I gave up! LOL I decided to use a different method to acheive my goal. Not the one I wanted, but it works.

I did not give up on the Select Case statements though and have used them in place of several longer IIf Then statements.

Thanks so much for your help! Don't be surprised if I email you with other nerve racking, brain numbing problems.

Thanks again,
Mary