Statement too Complex

[HearSay]

I am getting a statement too complex error in the where clause of my query.
Access 2000 is my version
I am not going to include the whole statement, just the erroring out part.

Parameters strBDayDate Text (255);

Select .......

where
(((CDate([Staff_Birthday_Month] & "/" & [Staff_Birthday_Day] & "/" &
IIf([staff_Birthday_Month]<Month(Date()),Year(DateAdd("yyyy",1,Date())),Year(Date()))))>=CDate([strBDayDate])))

Any ideas why this would just stop working?

 

[kingston via AccessMonster.com]

When queries stop working, most likely it is because of a change in the data.
Look for recently added or changed data. You may even have a corrupt record.

I am getting a statement too complex error in the where clause of my query.
Access 2000 is my version
I am not going to include the whole statement, just the erroring out part.

Parameters strBDayDate Text (255);

Select .......

where
(((CDate([Staff_Birthday_Month] & "/" & [Staff_Birthday_Day] & "/" &
IIf([staff_Birthday_Month]<Month(Date()),Year(DateAdd("yyyy",1,Date())),Year(Date()))))>=CDate([strBDayDate])))

Any ideas why this would just stop working?

 

[HearSay]

No changes in the data, that has already been verified, the only thing that
changed is the version of access.

When queries stop working, most likely it is because of a change in the
data.
Look for recently added or changed data. You may even have a corrupt
record.

I am getting a statement too complex error in the where clause of my
query.
Access 2000 is my version
I am not going to include the whole statement, just the erroring out part.

Parameters strBDayDate Text (255);

Select .......

where
(((CDate([Staff_Birthday_Month] & "/" & [Staff_Birthday_Day] & "/" &
IIf([staff_Birthday_Month]<Month(Date()),Year(DateAdd("yyyy",1,Date())),Year(Date()))))>=CDate([strBDayDate])))

Any ideas why this would just stop working?

 

[Allen Browne]

"Too complex" just means Access doesn't understand.
That's often because of data type mismatches.

You have a real hotch-potch of data types in that expression. Seems like you
have some numeric values (Staff_Birthday_Month etc) that you are
contatenating together into a string, and then attempting to typecast into a
Date, with a bunch of other things going on to try to adjust for people who
have not had their birthday this year, and the possibility of Nulls making
the expression invalid to typecast (which Access calls "too complex"), mixed
up with a text expression that may or may not have been valid as a date
being thrown into the mix. There are too many places where that whole thing
can fall apart.

 

[kingston via AccessMonster.com]

Are there any other changes that you didn't mention? Have you performed a
compact and repair? Try the query on a small set of data (one record) first
to make sure that the query logic is the problem. Hopefully, it is just a
data point or a corrupted record. Otherwise, unless you publish the entire
query with all of the field properties and relationships, it's going to be
kind of hard to troubleshoot.

No changes in the data, that has already been verified, the only thing that
changed is the version of access.

When queries stop working, most likely it is because of a change in the
data.

[quoted text clipped - 15 lines]

 

[Jerry Whittle]

Concur. I've learned the hard way that when using CDate it's an extremely
good idea to check the data with IsDate to ensure that it can be evaluated as
a date. CDate will bomb out with a 13-Type Mismatch on something like
13/13/2006 or 2/30/2006 or and empty string or a 94-Invalid use of Null is a
null is presented.

I always check first then handle those strings that CDate can't handle.

IIf(IsDate([strBDayDate]) = True, CDate([strBDayDate]), #1/1/1950#)
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Too complex" just means Access doesn't understand.
That's often because of data type mismatches.

You have a real hotch-potch of data types in that expression. Seems like you
have some numeric values (Staff_Birthday_Month etc) that you are
contatenating together into a string, and then attempting to typecast into a
Date, with a bunch of other things going on to try to adjust for people who
have not had their birthday this year, and the possibility of Nulls making
the expression invalid to typecast (which Access calls "too complex"), mixed
up with a text expression that may or may not have been valid as a date
being thrown into the mix. There are too many places where that whole thing
can fall apart.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am getting a statement too complex error in the where clause of my query.
Access 2000 is my version
I am not going to include the whole statement, just the erroring out part.

Parameters strBDayDate Text (255);

Select .......

where
(((CDate([Staff_Birthday_Month] & "/" & [Staff_Birthday_Day] & "/" &
IIf([staff_Birthday_Month]<Month(Date()),Year(DateAdd("yyyy",1,Date())),Year(Date()))))>=CDate([strBDayDate])))

Any ideas why this would just stop working?

 

[HearSay]

Turns out one of the dates was 02/29, thus the type mismatch error. Thanks
for all your suggestions.

Concur. I've learned the hard way that when using CDate it's an extremely
good idea to check the data with IsDate to ensure that it can be evaluated
as
a date. CDate will bomb out with a 13-Type Mismatch on something like
13/13/2006 or 2/30/2006 or and empty string or a 94-Invalid use of Null is
a
null is presented.

I always check first then handle those strings that CDate can't handle.

IIf(IsDate([strBDayDate]) = True, CDate([strBDayDate]), #1/1/1950#)
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Too complex" just means Access doesn't understand.
That's often because of data type mismatches.

You have a real hotch-potch of data types in that expression. Seems like
you
have some numeric values (Staff_Birthday_Month etc) that you are
contatenating together into a string, and then attempting to typecast
into a
Date, with a bunch of other things going on to try to adjust for people
who
have not had their birthday this year, and the possibility of Nulls
making
the expression invalid to typecast (which Access calls "too complex"),
mixed
up with a text expression that may or may not have been valid as a date
being thrown into the mix. There are too many places where that whole
thing
can fall apart.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am getting a statement too complex error in the where clause of my
query.
Access 2000 is my version
I am not going to include the whole statement, just the erroring out
part.

Parameters strBDayDate Text (255);

Select .......

where
(((CDate([Staff_Birthday_Month] & "/" & [Staff_Birthday_Day] & "/" &
IIf([staff_Birthday_Month]<Month(Date()),Year(DateAdd("yyyy",1,Date())),Year(Date()))))>=CDate([strBDayDate])))

Any ideas why this would just stop working?