Query too complex? It's simple!

vincentj

unread,

Oct 1, 2001, 10:29:24 AM10/1/01

to

Hi,

When I try to run my query, I get a message saying that
the query is typed incorrectly or too complex to be
evaluated. It works fine in an older version of the same
database, so why would it stop working correctly? Here is
the query:

SELECT *
FROM qryWindInfo
WHERE Date=Date()
ORDER BY Date;

This is a serious impediment to my team, because we need
to print daily reports, and if we can't do it then this is
useless to us. Plus a big waste of money because we spent
nine months developing this database.

Please, someone help with this problem!!!

-jay

Lynn Trapp

unread,

Oct 1, 2001, 10:37:54 AM10/1/01

to

It looks like Access is confused about what Date is. I presume this is the
name of a field in your table. Access doesn't know if it refers to your
field or to the Date() function, which can be written without the
parentheses. I think you'll find that if you change the name of the field in
your table your problem will disappear.

--
Lynn Trapp
Programmer/Analyst
Wells Dairy, Inc.
rlt...@bluebunny.com

vincentj <vin...@SPAMSUX.clarkson.edu> wrote in message
news:3de101c14a85$7814f630$36ef2ecf@tkmsftngxa12...

vincentj

unread,

Oct 1, 2001, 10:46:34 AM10/1/01

to

Hi,

Thanks, but it didn't work. Besides, the query works
exactly as written in another (older) copy of the database.

Any other suggestions?

>.
>

Lynn Trapp

unread,

Oct 1, 2001, 10:54:11 AM10/1/01

to

Newer versions of Access have become much pickier on naming things. Could
you send the structure of the table you are trying to query?

--
Lynn Trapp
Programmer/Analyst
Wells Dairy, Inc.
rlt...@bluebunny.com

vincentj <vinc...@SPAMSUX.clarkson.edu> wrote in message
news:0a2001c14a87$ddaac040$9ae62ecf@tkmsftngxa02...

vincentj

unread,

Oct 1, 2001, 11:03:01 AM10/1/01

to

Here is the list of columns from the source table:

theDate, Shift, Employee1, Employee2, Employee3,
Employee4, ShopOrder, Blocks, Percentage, Notes, DelayCodes

Both of my versions of the database are written in Access
97, but the other one is a months-old revision (so I can't
regress to the previous revision).

>.
>

Duane Hookom

unread,

Oct 1, 2001, 11:05:08 AM10/1/01

to

What happens if you put []s around the field named Date. As Lynn mentioned,
this is a poor choice for a field name.
SELECT *
FROM qryWindInfo
WHERE [Date]=Date()
ORDER BY [Date];

If this doesn't help,
-does the query work if you try:
SELECT *
FROM qryWindInfo
-can you open qryWindInfo?
-Are there any other criteria perhaps in qryWindInfo?
-Have you checked your references from a module window?
Duane

"vincentj" <vin...@SPAMSUX.clarkson.edu> wrote in message
news:3de101c14a85$7814f630$36ef2ecf@tkmsftngxa12...

Rachel Ceraul

unread,

Oct 1, 2001, 11:11:08 AM10/1/01

to

Hello Jay! My name is Rachel Ceraul. Thank you for using the
Microsoft Access Newsgroups. If I understand your issue correctly, you are
receiving the following error when attempting to run a query: "This
expression is typed incorrectly, or it is too complex to be evaluated. For
example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."
One suggestion that I have is to name your field to something other
than 'Date.' This is a reserved word in Access and can cause some problems
in the database as Access doesn't know if Date is supposed to be a field or
a function. I have included a link to a list of reserved words in the
article below. Please rename this field and test the query again.
I hope this helps! If you have additional questions on this topic,
please reply to this posting.

ACC2000: Reserved Words in Microsoft Access
http://support.microsoft.com/support/kb/articles/q209/1/87.asp

Regards,
Rachel Ceraul
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

vincentj

unread,

Oct 1, 2001, 11:20:40 AM10/1/01

to

Ok, I tried this after changing the field name and it
doesn't work:

SELECT *
FROM qryWindInfo
WHERE [theDate] = Date()
ORDER BY [theDate];

but this DOES work (although it doesn't help me):

SELECT *
FROM qryWindInfo
WHERE [theDate] = #9/26/01#
ORDER BY [theDate];

And the references are set OK; the Date() function works
fine elsewhere in the database.

>.
>

vincentj

unread,

Oct 1, 2001, 11:23:01 AM10/1/01

to

Hi,

I tried renaming the field and it doesn't work. Besides,
as I mentioned, the query works exactly as written in an
earlier revision of the database.

Lynn Trapp

unread,

Oct 1, 2001, 11:32:15 AM10/1/01

to

On the surface I can't see any reason it shouldn't work if you changed the
field name to theDate. Have you tried all of Duane's suggestions? Could you
post the SQL statement for qryWindInfo?

--
Lynn Trapp
Programmer/Analyst
Wells Dairy, Inc.
rlt...@bluebunny.com

vincentj <vinc...@NOSPAM.clarkson.edu> wrote in message
news:52c901c14a8a$2a13af80$19ef2ecf@tkmsftngxa01...

Duane Hookom

unread,

Oct 1, 2001, 11:36:43 AM10/1/01

to

Can you tell us how you checked your references? You might want to review
http://www.mvps.org/access/bugs/bugs0001.htm. MISSING references can cause
some fairly bizarre behavior.

Duane

"vincentj" <vinc...@NOSPAM.clarkson.edu> wrote in message

news:272f01c14a8c$a1442880$b1e62ecf@tkmsftngxa04...

vincentj

unread,

Oct 1, 2001, 11:52:55 AM10/1/01

to

I did try Duane's suggestions. Is there a limitation to
how many functions you can call in a query? Or a limit to
how many records those functions can be called on?
Because (as you will see) there are *tons* of records and
several fairly complex calculations going on. And the
older revision has less records, so I wonder if that has
something to do with it? Other than that, everything is
the same between these two versions (the queries are
identical)

Anyway, here is the SQL statement for qryWindInfo.
Unfortunately, I this database was 5 months in development
before I started working on it, and the person that
started didn't know about normalization, so it's very
poorly designed. This is what I'm stuck with, and I know
it sucks. Well, here is the query:

SELECT ALL Date As theDate, Shift, Employee1, Employee2,
Employee3, Employee4, ShopOrder, Blocks, (Blocks/(IIf
(Employee1 Is Null,0,8)+IIf(Employee2 Is Null,0,8)+IIf
(Employee3 Is Null,0,8)+IIf(Employee4 Is Null,0,8))*100
& "%") As Percentage, Notes, DelayCodes
FROM tabBake
WHERE Shift <>"" AND Blocks <> "" AND (Employee1<>"" OR
Employee2<>"" OR Employee3 <>"" OR Employee4 <>"")
UNION ALL SELECT Date As theDate, Shift, Employee1,