Question Need help with iif/left statement not returning false argument. (1 Viewer)

[crjackson]

I'm a self taught novice so I'm really not sure what I'm doing wrong. Any suggestions are welcome. I'm trying to derive the month in digits from a date/time stamp field. Any month between 10 and 12, I need the first 2 digits but anything <=9 is only 1 digit.

MONTH: IIf(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],2)="10" Or "11" Or "12",(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],2),(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],1))))

 

[plog]

There's already a function in Access for that: Month() http://www.techonthenet.com/access/functions/date/month.php

This is how you would use it:

MonthNumber: Month([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS])

 

[crjackson]

Plog, Thank you
Typical me, overcomplicating things. Just for kicks and giggles, how can I get the original formula to work. In case I wanted to use it in a different context.

 

[plog]

To fix what your code you need to explicitly state a comparison, you can't just type 'OR's and hope the system knows what to compare:

IIf(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],2)="10" Or "11" Or "12"...

In the above code you would need to retype out the variable you are comparing to "11" and "12".

 

[crjackson]

Yep, I do remember doing that in Excel just the other day. Don't know why I didn't remember it for Access. Thanks again Plog!!!

 

[Pat Hartman]

Using string functions on numeric fields leads to strange problems. Technically if you wanted to use string functions on a date, you would need to convert the date to a string first by using Format(YourDate, "mm/dd/yyyy") or whatever your preferred format is. Then you could use Left(), Right(), and Mid() to slice and dice. But why would you ever do this when you have simple date functions available such as Month(), Day(), and Year().