Functions Error

[AC5FF]

I have looked and looked at this expression line and cannot find what is wrong.

Repaired: Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(date(forms![enterprise report card].[combo31])),Month(date(forms![enterprise report card].[combo31])),1) And DateSerial(Year(date(forms![enterprise report card].[combo31])),Month(date(forms![enterprise report card].[combo31]))+1,0),1,0))

This gives me an error "Expression entered has a function containing the wrong number of arguements"

However this one:

Repaired: Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0),1,0))

works fine... The only difference is I inserted forms![enterprise report card].[combo31] inside of the Date() function.

Can anyone see what I might be missing??

 

[pbaldy]

You've left the word "Date" in there, which hopefully I didn't do when I posted the sample for you earlier. You want to replace the Date() function in your original expression with the form reference.

 

[AC5FF]

I dont knwo what I did yesterday. Wierd.. Another day and everything works??
And no, it's not an April 1st joke.. LOL.. All I did was re-typed in the query formula from scratch and no errors and everything pulls like it is suppose to! Very Strange..

So; at the top of this report where all this data is going I would like to put the date. I have gotten that to work, but it's a 'short date'. I would prefer to format this as MMM YY.
This works to give the short date at the top of my report:
Forms![Enterprise Report Card].combo31

In a few of my queries I format the date from the tables (to group by month) using this:
Date: CDate("1 " & Format(Work![TIN Date],"mmm yy"))

But when I try to do this on my report w/the form field the report asks me for a value. Here's what I'm using to try to get just MMM YY (I used " " to display w/out the day value):

CDate(" " & Format(Forms![Enterprise Report Card].combo31,"mmm yy"))

Any Ideas?

 

[pbaldy]

Try it with just the Format function:

=Format(Forms![Enterprise Report Card].combo31,"mmm yy")

and of course the form must remain open.

 

[AC5FF]

Now why didn't I try that?!?! Worked