469,648 Members | 1,143 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,648 developers. It's quick & easy.

The expression you have entered is too complex

I am trying to get around the problem "The expression you have entered
is too complex" for a select query. (The example below is not the
expression that is giving me headaches.)

So I am thinking that I just need to do the parsing and calculating in
an event procedure for an "On Click" event.

My question is:

If I have a query field in access97 that parses date (in format
"MM/DD/YYYY") of a field [DOB] in a table like:

BIRTHDAYNUM:Val(Left((Mid([DOB],(InStr([DOB],"/"))+1,(InStr((InStr([DOB],"/")+1),[DOB],"/")))),InStr((Mid([DOB],(InStr([DOB],"/"))+1,(InStr((InStr([DOB],"/")+1),[DOB],"/")))),"/")-1))

(I know that I could do this some other way inside the query to get
the day but I want to learn how you would communicate between an event
procedure and a query [GET_BIRTHDAYNUM_Q] called inside the event
procedure: Private Sub GET_BIRTHDAYNUM_Click().)

What would the code need to be so that I am able to pull the data from
the field [DOB] in each record in a table [Employee], manipulate it,
and then put the result into the field [BIRTHDAYNUM]?
Nov 12 '05 #1
2 5800
sa****@uni.edu (Mikel) wrote in message news:<7c**************************@posting.google. com>...
I am trying to get around the problem "The expression you have entered
is too complex" for a select query. (The example below is not the
expression that is giving me headaches.)
Not sure why you haven't posted the actual problem, but maybe my
solution to the problem you DID post will help you with the solution
you seek.

The "too complex" error is usually related to too many nested IFs,
which I don't see below. As a matter of fact, in your example below,
you should not be getting the error message at all. It should work.
My question is:

If I have a query field in access97 that parses date (in format
"MM/DD/YYYY") of a field [DOB] in a table like:

BIRTHDAYNUM:Val(Left((Mid([DOB],(InStr([DOB],"/"))+1,(InStr((InStr([DOB],"/")+1),[DOB],"/")))),InStr((Mid([DOB],(InStr([DOB],"/"))+1,(InStr((InStr([DOB],"/")+1),[DOB],"/")))),"/")-1))

My solution, for your query value, use this instead:
BIRTHDAYNUM: Format([DOB],"dd")
(I know that I could do this some other way inside the query to get
the day but I want to learn how you would communicate between an event
procedure and a query [GET_BIRTHDAYNUM_Q] called inside the event
procedure: Private Sub GET_BIRTHDAYNUM_Click().)

What would the code need to be so that I am able to pull the data from
the field [DOB] in each record in a table [Employee], manipulate it,
and then put the result into the field [BIRTHDAYNUM]?


My guess would be to put the following code in your On Click event:

[BIRTHDAYNUM]=Format([DOB],"dd")

Dennis
Nov 12 '05 #2
sa****@uni.edu (Mikel) wrote in message news:<7c**************************@posting.google. com>...
I am trying to get around the problem "The expression you have entered
is too complex" for a select query. (The example below is not the
expression that is giving me headaches.)

So I am thinking that I just need to do the parsing and calculating in
an event procedure for an "On Click" event.

My question is:

If I have a query field in access97 that parses date (in format
"MM/DD/YYYY") of a field [DOB] in a table like:

BIRTHDAYNUM:Val(Left((Mid([DOB],(InStr([DOB],"/"))+1,(InStr((InStr([DOB],"/")+1),[DOB],"/")))),InStr((Mid([DOB],(InStr([DOB],"/"))+1,(InStr((InStr([DOB],"/")+1),[DOB],"/")))),"/")-1))

(I know that I could do this some other way inside the query to get
the day but I want to learn how you would communicate between an event
procedure and a query [GET_BIRTHDAYNUM_Q] called inside the event
procedure: Private Sub GET_BIRTHDAYNUM_Click().)

What would the code need to be so that I am able to pull the data from
the field [DOB] in each record in a table [Employee], manipulate it,
and then put the result into the field [BIRTHDAYNUM]?


you could use the standard VBA-functions cdate or datevalue (also
accepts monthnames) or - if the system-defaults, calendar-property of
the project don't comply with the notation to convert you also can
write a public VBA function in a module to use within your
SQL-expression. When using Jet/DAO this will work. I don't have
experience in the ADO-environment.
It is also possible that some other problem in your SQL-expression is
the cause of the error-message.

Marc
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.