By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

The expression you have entered is too complex

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.