hi all,

I have an Excel formula that I was trying to convert to an Access expression for a report. It basically converts a date into a lot code ("L" followed by the year's last digit and then the day of the year... kinda like the julian day, but Mar 1st is always day 061 whether or not it's a leap year.)

Here's the formula I have in excel:
=IF(I4=0,"","L"&RIGHT(TEXT(I4,"yy"))+0&TEXT(DATE(0,MONTH(I4),DAY(I4)),"000"))

where I4 is the date field. This is the expression I tried to use in a Text box in an access report, but the properties box wouldn't even let me enter it and throw a value, it just would not respond when I hit enter :confused::

=IIF(IsNull([DateChoice]),"","L"&RIGHT(TEXT([DateChoice],"yy"))+0&TEXT(DATE(0,MONTH([DateChoice]),DAY([DateChoice])),"000"))

Any clue?

Thanks in advance...

Try again with valid Access functions. TEXT is not in Access.

http://www.techonthenet.com/access/functions/

DatePart("y", [datefield]) gives the day of the year but you will have to fudge the way the leap year is ignored in your data.

Your Right syntax is incorrect.
Right(expression, x)
x = number of characters