Something wrong with this IIF statement?

[UniqueTII]

I am trying to make a field in a query that is based on another field. In my reports, I just used an IIF statement to get the output I desired, but now I want to put it right into the query so that I can merge with Word a whole lot easier. The field I want to add is [Term], and it's based on the field [Start Date]. If the start date is between May 16th and August 15th, the term is summer...otherwise, it's academic year. It works find in my reports, but when I put it in the query, I get an error message telling me that there is an extra ")". The statement is as follows:
Term : IIf(((DatePart("m",[Start Date])=5) And (DatePart("d",[Start Date])>15)) Or ((DatePart("m",[Start Date])=8) And (DatePart("d",[Start Date])<16)) Or ((DatePart("m",[Start Date])>5) And (DatePart("m",[Start Date])<8)),"summer","academic year")

Is there anything wrong with that?

 

[cpod]

:IIf((DatePart("m",[start date])=5 And DatePart("d",[start date])>15) Or (DatePart("m",[start date])=8 And DatePart("d",[start date])<16) Or (DatePart("m",[start date])>5 And DatePart("m",[start date])<8),"summer","academic year")

 

[UniqueTII]

I am still getting the same error.

 

[RJS]

I tried you formula (copy and paste with no changes) with some of my data, and it worked perfectly.

I'm not sure what your problem is, but I don't think it is your formula.

Rob S.

 

[UniqueTII]

Are you using 97 or 2000? I don't know if it makes any difference, but I am using 2000.

 

[UniqueTII]

I did a search on that error and this is what I found:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Extra ) in query expression '(MemberID = )'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default values for all form fields that are used in the query.


// End paste
There are no empty fields in the query, so I still don't know how to fix this problem, and I don't know how to set default values. Can anyone help out?

 

[UniqueTII]

More info:
The reason it is saying I have empty fields is because three of the fields in the table have default values, which creates a blank record at the bottom of the table. I can't change the default value of [Start Date] because it is a linked table, and I can't take away the defaults of the other tables. Am I out of luck?

I went and changed the default value on the table it's linked to, but I can't get the table I'm using to change. I tried updating linked tables....what should I be doing?

On a side note, don't you guys and gals love inheriting someone elses work with no explanations?

[This message has been edited by UniqueTII (edited 05-01-2002).]

 

[UniqueTII]

I added a criteria of [Start Date]>1-1-85 to the field so that the null value wouldn't be included. It's a b.s. fix, but it's a fix.

EDIT: That wasn't the fix...the fix was that I didn't tell it what form to look for [Start Date] in.

[This message has been edited by UniqueTII (edited 05-02-2002).]