If statement in Query

[Jojojo]

The following statement is what I have in an IF statment in my query. This exact statement is also a form - where it works perfectly. In the query, it just gives me an #Error in the field.



RnwlDate: IIf([FeeScheduleID]="9",[RegistrationDate],IIf([FeeScheduleID]="10",[RegistrationDate]+[AddDays],DateAdd("ww",[MembershipLength],[RegistrationDate])+[AddDays]))

When I add the following to the criteria, it tells me that it's too complex
Between [forms]![Membership Expirey Form].[StartRegistrationDate] And [forms]![Membership Expirey Form].[EndRegistrationDate]

The query is also pulling the Fee Description (ex. Drop In) as opposed to the actual ID (ex 9).

FeeScheduleID is the FK in the Registration Table
FeeScheduleID is the PK in the Fee Schedule Table

[alansidman]

Is the feescheduleID a number or text in your table? Would you post your entire SQL statement. It will make it easier to analyze.

[Jojojo]

The Fee Schedules table - FeeScheduleID is the PK and is autonumber
The Registration table - FeeScheduleID is the FK and is number

I looked at the data stored in the actual Registration Table and it's storing the Fee Description even though the bound column is the Key. I've tried replacing the 9 and 10 in the FeeScheduleID="" below with the corresponding text and it still doesn't work. I also tried adding the Fee Schedules table and pulling the key from there - and it tells me I have a circular reference (it doesn't know which table, Registration or Fee Schedules to pull from). I tried adding the table name into the code below (if code), but I don't think I did it right because that didn't work either.

SELECT EventRegistration.RegistrationID, EventRegistration.EventID, Members.AttendeeFirstName, Members.AttendeeLastName, Registration.FeeScheduleID, Events.EventName, Events.ClassDay, Events.StartTime, Registration.MembershipLength, Registration.AddDays, Registration.RegistrationDate, IIf([FeeScheduleID]="9",[RegistrationDate],IIf([FeeScheduleID]="10",[RegistrationDate]+[AddDays],DateAdd("ww",[MembershipLength],[RegistrationDate])+[AddDays])) AS RnwlDate, Registration.OnHold
FROM (Members INNER JOIN Registration ON Members.AttendeeID = Registration.AttendeeID) INNER JOIN (Events INNER JOIN EventRegistration ON Events.EventID = EventRegistration.EventID) ON Registration.RegistrationID = EventRegistration.RegistrationID
WHERE (((IIf([FeeScheduleID]="9",[RegistrationDate],IIf([FeeScheduleID]="10",[RegistrationDate]+[AddDays],DateAdd("ww",[MembershipLength],[RegistrationDate])+[AddDays]))) Between [Forms]![Membership Expirey Form].[StartRegistrationDate] And [Forms]![Membership Expirey Form].[EndRegistrationDate]));

[Jojojo]

I believe I solved it - Thanks for your effort.. This seems to work:

SELECT EventRegistration.RegistrationID, EventRegistration.EventID, Members.AttendeeFirstName, Members.AttendeeLastName, [Fee Schedules].FeeScheduleID, Events.EventName, Events.ClassDay, Events.StartTime, Registration.MembershipLength, Registration.AddDays, Registration.RegistrationDate, IIf([Fee Schedules.FeeScheduleID]=9,[RegistrationDate],IIf([Fee Schedules.FeeScheduleID]=10,[RegistrationDate]+[AddDays],DateAdd("ww",[MembershipLength],[RegistrationDate])+[AddDays])) AS RnwlDate, Registration.OnHold
FROM (Members INNER JOIN ([Fee Schedules] RIGHT JOIN Registration ON [Fee Schedules].FeeScheduleID = Registration.FeeScheduleID) ON Members.AttendeeID = Registration.AttendeeID) INNER JOIN (Events INNER JOIN EventRegistration ON Events.EventID = EventRegistration.EventID) ON Registration.RegistrationID = EventRegistration.RegistrationID;

[alansidman]

Glad that you figured it out. Looks like there was a datatype issue.

Alan

[Jojojo]

I had to remove the quotes from around the 9 and 10 in the Iif statement and had to bring in the FeeScheduleID from the FeeSchedule table and not the registration table