The expression you have entered is too complex

[Mikel]

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((I nStr([DOB],"/")+1),[DOB],"/")))),InStr((Mi d([DOB],(InStr([DOB],"/"))+1,(InStr((I nStr([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]?

[Dennis Kuhn]

sa****@uni.edu (Mikel) wrote in message news:<7c******* *************** ****@posting.go ogle.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((I nStr([DOB],"/")+1),[DOB],"/")))),InStr((Mi d([DOB],(InStr([DOB],"/"))+1,(InStr((I nStr([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

[Marc]

sa****@uni.edu (Mikel) wrote in message news:<7c******* *************** ****@posting.go ogle.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((I nStr([DOB],"/")+1),[DOB],"/")))),InStr((Mi d([DOB],(InStr([DOB],"/"))+1,(InStr((I nStr([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