iif Statement Help

[smc678]

I am trying to write an iff statement for iff [Time Arrived] = 0400-1459 it would display 1 or [Time Arrived] = 1500-0359 it would display 2.

any Ideas?

[hawkdriver]

I am not sure what your fields are like, but it seems fairly straight forward.

IIF([table].[Time Arrived]="0400-1459", "1", IIF([table].[Time Arrived]="1500-0359", "2", Nothing))

The thing that I don't know is whether your 0400-1459 is a string or not.

Another way to do this is to set the 0400-1459 as a value or the 1 you refer to in your question. Just create a table that has your values listed. Key your 1 value equal to 0400-1459 and 2 to 1500-0359. Then your IIF works well.

That's the beauty with databases, they are very flexible in design.

[smc678]

Thank you for your response. It comes up with a #name? This is put within a form and i placed this expressing in the default value is that right?

[smc678]

and the 0400-1459 is a in between military time i dont know if you knew that or not.

[hawkdriver]

Post your code and I will help you get this resolved. Where are you wanting the 1 displayed? I understand you are wanting to translate the 0400-1459 to a 1, but where is this happening and how do you want the IIF to do this. I am only seeing a partial indication of what you want. If you are only doing two choices, why don't you just put a selection in a drop down and send the value back to the table using the 1 or 2? It looks like you have a text box above and there are easier methods to accomplish your goal. I just need a starting place to help.

[ssanfu]

iff [Time Arrived] = 0400-1459 it would display 1 or [Time Arrived] = 1500-0359 it would display 2.

You cannot do a comparison like that. You understand what it means, but Access would try to subtract the two numbers, then compare the result to the time or treat it like a text field, comparing a date/time type to a text string.

First, it is IIF, not IFF. (computers can never spell correctly)
Second, you should only use letters, numbers and the underscore in object names. No spaces.
Next, you have to check if a time is less than the time field, then check if the time field is greater than a time (or use the beween keyword).

I would put this in the query for the form, but you can use it in the control source of the control.

For a control source:

=IIf([TimeArrived]>=TimeValue("4:00 AM") And [TimeArrived]<=TimeValue("02:59 PM"),1,2) 

or

= IIf([TimeArrived] Between TimeValue("4:00 AM") And TimeValue("02:59 PM"),1,2)

In a query, you would use

Shift: Shift: IIf([TimeArrived]>=TimeValue("4:00 AM") And [TimeArrived]<=TimeValue("02:59 PM"),1,2)

Or

ShiftCalc: IIf([TimeArrived] Between TimeValue("4:00 AM") And TimeValue("02:59 PM"),1,2)

------
Also, if the field "Transaction" has meaning, it shouldn't be an automunber type. You should not use an Autonumber if the field is meant to have meaning for the users.
Autonumbers are not guaranteed to be sequential, consecutive or even positive. They are only guaranteed to be unique.
If you need a sequential, consecutive & positive number, you will have to "roll your own". There are many examples to do this.

[hawkdriver]

I will never understand people that try to teach by being "superior" to others. An IIF could absolutely work by comparing to literal text values. The problem remains that we are guessing his/her intentions. That's why I asked for more information.

[ssanfu]

@hawkdriver

If I have hurt your feelings, sorry. I do not think I am, nor am I trying to be "superior to others". Should I have been more verbose in my post? Apparently so.

Having read your post and the OP's reply, I made a table, added 8 records, created a query and pasted in your solution. It didn't work. I tried several ways to modify your expression, but couldn't get the required result. So I went another direction that seemed much simpler.

I could have created a UDF to do the comparison, but felt that was overkill when a simpler expression would suffice. I tested my expressions, posted both.

An IIF could absolutely work by comparing to literal text values

Yes, but it would not have been simple to do it.

The problem remains that we are guessing his/her intentions

Maybe... but it seemed clear to me, so I posted my solution.

If I post a solution that I have not tested, I always try to add "Air Code" or "Warning - untested" so the OP understands there might be problems with my solution.


I will try to be more clear in my writing in the future.

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

[hawkdriver]

No feelings hurt guy. Thanks for the more loquacious response. I was referring to your

First, it is IIF, not IFF.

, to which you later added

(computers can never spell correctly)

Regardless, thanks for trying.