iif statement involving a check box

[jadira]

Hi everyone I'm new to Access, and I'm having issues. I'm trying to get a column in my table to either calculate a length of stay or enter a "0" if a check box indicating never arrived is checked on my form.

I've tried a few different things with no success.

Example:
If [Never Arrived] ="true" Then
[Length of Stay] = "0"

ElseIf [Never Arrived] ="false" Then
[Length of Stay]= "[End of Billing Cycle]-[Begin Billing Date]"

End If

Any thoughts?

[PhilOfWalton]

First thing is you will make life a lot easier for yourself if you don't have spaces in field names.

As you have correctly done, you have to surround then with squre bracketes [Length of Stay], as that is 3 words.

Much better is LengthOfStay. Brackets are then optional and personally I don't put them in, but often Access does.

OK to your question:

True & False are words recognised by Access, so they don't need the quotes round them.
I assume [Length of Stay] is a number and therefor if you put quotes round it you are passing a string to a numeric field. No can do.

Try

 
If [Never Arrived] = True Then
    [Length of Stay] = 0
Else 
[Length of Stay]= "[End of Billing Cycle]-[Begin Billing Date]"

[PhilOfWalton]

Sorry, pressed the Tab key and the message went before I had finished.

Try

If [Never Arrived] = True Then
    [Length of Stay] = 0
Else 
   [Length of Stay]= [End of Billing Cycle] - [Begin Billing Date]
End If
 

Much cleaner is without the spaces in the field names and just as readable

If NeverArrived = True Then
    LengthOfStay = 0
Else 
   LengthOfStay= EndOfBillingCycle - BeginBillingDate
End If
 

Phil

[jadira]

Thanks I actually figured it out before I got a reply. I used a simple function in the table.

IIf([Never Arrived],0,([End of Billing Cycle]-[Begin Billing Date]))

I was over complicating things.