Expression Problem

  • Thread starter Thread starter JH
  • Start date Start date
J

JH

I have the following set up as an expression in a query.
ETConvert is a Time (Date/Time field).

NewShift: IIf([ETConvert]<=#6:59:59 AM# And [Shift]
="B","A") Or IIf([ETConvert]<=#6:59:59 AM# And [Shift]
="C","B") Or IIf([ETConvert]<=#6:59:59 AM# And [Shift]
="A","B")

If I use the expression upto the first OR by itself, it
works fine. However, adding the rest produces a -1 in the
result. That shows me that something is occuring, but I
need the appropriate letter instead.

I tried replacing the OR's with a ',' but that doesn't
work either. What do I have wrong?
 
I have the following set up as an expression in a query.
ETConvert is a Time (Date/Time field).

NewShift: IIf([ETConvert]<=#6:59:59 AM# And [Shift]
="B","A") Or IIf([ETConvert]<=#6:59:59 AM# And [Shift]
="C","B") Or IIf([ETConvert]<=#6:59:59 AM# And [Shift]
="A","B")

If I use the expression upto the first OR by itself, it
works fine. However, adding the rest produces a -1 in the
result. That shows me that something is occuring, but I
need the appropriate letter instead.

I tried replacing the OR's with a ',' but that doesn't
work either. What do I have wrong?
Click to expand...

You are giving each IIF statement *two* arguments - IIF has *three*.
The first argument is a logical expression (which can contain multiple
AND and OR clauses; the second argument is returned if the expression
is true; the third expression is returned if it is false.

I'm not sure what result you want here... could you provide a "truth
table" showing the desired inputs and the proper output for each?
 
I apologize. I should done better explaining the idea.
Yet, what you proposed is exactly what I was looking for.

Many thanks.
-----Original Message-----
I'm not exactly sure what you're trying to do, but this may help you:

NewShift: IIf([ETConvert]<=#6:59:59 AM#, Switch([Shift] = "A", "B", [Shift]
= "B", "A", [Shift] = "C", "B"), "Not a new shift")

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


I have the following set up as an expression in a query.
ETConvert is a Time (Date/Time field).

NewShift: IIf([ETConvert]<=#6:59:59 AM# And [Shift]
="B","A") Or IIf([ETConvert]<=#6:59:59 AM# And [Shift]
="C","B") Or IIf([ETConvert]<=#6:59:59 AM# And [Shift]
="A","B")

If I use the expression upto the first OR by itself, it
works fine. However, adding the rest produces a -1 in the
result. That shows me that something is occuring, but I
need the appropriate letter instead.

I tried replacing the OR's with a ',' but that doesn't
work either. What do I have wrong?
Click to expand...


.
Click to expand...
 
Back
Top