Year Expression

[joshandsony]

Hello, I am trying to figure out why my year expression won't work. I have this expression in a query and it keeps saying undefined function 'Year' in expression.

I have also disable sandbox to see if that would release the function.

IIf(Year([EXPEDITE RAW]![PO Date])=Year(Date()),Format([EXPEDITE RAW]![PO Date],"mmm-yy"),Year([EXPEDITE RAW]![PO Date]))

Here is a previous post that got me to this point. http://www.access-programmers.co.uk/forums/showthread.php?t=166233

 

[boblarson]

I'm guessing that somewhere you have a field in a table or query (possibly in a procedure, but less likely) that is named "YEAR" and that is causing you problems.

 

[joshandsony]

I just checked and I don't see anywhere that I have a "Year" name or function other than this one, unless it shows up in vb.

 

[joshandsony]

Can I send you what I am working with so you can take a look at it?

 

[boblarson]

Can I send you what I am working with so you can take a look at it?

Sure, but I won't see it for about another 5-6 hours as I can't do it here at work. I'll PM you my email address.

 

[boblarson]

The problem lies in that you are putting the stuff in the CRITERIA and not up in the field area like you should.

Replace the field [PO Date] with this:

PODate: IIf(Year([EXPEDITE RAW]![PO Date])=Year(Date()),Format([EXPEDITE RAW]![PO Date],"mmm-yy"),Year([EXPEDITE RAW]![PO Date]))

See screenshot below:

I modified some of your data so that there would be current year info so you could see how it shows up.

 

[joshandsony]

Thank you so much for your help! I really appreciate it. I have been working on this one issue for many hours.

 

[joshandsony]

Did you use the exact same expression as my original post? I put it where you have it and it is still giving me the same error. Undefined expression 'Year' in function.

Could I be missing a reference table in VB?

 

[boblarson]

Did you use the exact same expression as my original post? I put it where you have it and it is still giving me the same error. Undefined expression 'Year' in function.

Could I be missing a reference table in VB?

Yeah, it works fine for me. Go into the VBA window and to the References and, if there is a MISSING reference, uncheck it and then close and reopen. If there isn't one marked as missing then see if there is a DAO reference checked so uncheck it and close and reopen. You might have to manually recheck any that you uncheck if they don't come back and you find you need them.

 

[joshandsony]

Looks like I am missing service pack 3. I am going to update and see what happens. I will let you know.

 

[joshandsony]

Ok I finally figured out why it was not working for me.

First I was running Windows on Parallels through my mac. I created a partition using bootcamp, installed windows xp on the partition, then reinstalled Office 2007 there.

I tried doing the service pack 3 updated, but that didn't work (I have to figure that one out in the morning).

I decided I was getting tired, so I just tried running the query with the previously discussed code, and I did not get the error, even without installing the service pack. My guess is, in the background of Access through my virtual machine, I was missing some kind of code that was not allowing Access to recognize the Reference Libraries.

Anyway it is working flawlessly now. Thank you kindly for your help and patience.