IIF LEN throws too many arguments

[detrie]

Access 2003

I am trying to derive the Quarter and two digit year from a date field

Logic: If the toCSHipDate is not empty then Ship:= toCSHipDate formatted as Q3 13 (Q# yy). If toCSHipDate is empty return a blank

Ship: IIf((LEN([toCSHipDate]>1, ("Q" & Format(([toCSHipDate]),"q") & " " & Right(Year([toCSHipDate]),2)),""))

 

[jdraw]

It seems you are working against yourself to some degree.

Why check the length of a date? I can understand checking for IsNull(YourDate)

You can format a Date in many ways.

You should check out the DatePart function to see how to determine the Quarter from a Date,
see http://www.techonthenet.com/access/functions/date/datepart.php

You should also check the Year function; and the Format function.

Year(yourDate)

 

[detrie]

jdraw

thanks for your reply

This also works but only gets me half way there..
If the toCShipDate is empty, I get a "Q" in the filed.. Instead I'd like it to be blank.

Ship: "Q" & DatePart("q",[toCSHipDate]) & " " & DatePart("yyyy",[toCSHipDate])

 

[jdraw]

Try

Ship: iif( NOT isNull(toCShipDate),"Q" & DatePart("q",[toCSHipDate]) & " " & DatePart("yyyy",[toCSHipDate])," ")

 

[detrie]

RESOLVED!

jdraw.. thanks for your help.. the final bit I was struggling with was the not isNull part

Thank you again

 

[jdraw]

Glad to help.