Expression typed incorrect or too complicated

  • Thread starter Thread starter wallymeister
  • Start date Start date
W

wallymeister

I have a database that I am updating w/ new Models. Got all the Models added
and then tried to run the report on these Models. Report uses Query2A which
uses Query1 linked to MESPROD_LINESE table. It asks for start date and end
date for the report. Since I added the new models, when I run the report, I
get {Expression typed incorrect or too complicated] error message. Query1
runs by itself fine though.

Here is the SQL for Query2A of the Report. (it all worked fine before)

SELECT MESPROD_LINESE.[Line Number], MESPROD_LINESE.[Release Number],
MESPROD_LINESE.[Model Number], MESPROD_LINESE.Quantity, MESPROD_LINESE.[Run
Date], MESPROD_LINESE.[Seq Number], Query1.[Order Index], Query1.[Check Item
Index], Query1.AssyName, Query1.PartName, Query1.PartNum, Query1.Casing,
Query1.Cells, Query1.[Lo-Nox], Query1.ModelType, Query1.Door, Query1.Note,
Query1.Shaded, Query1.Comments, Query1.QE, Query1.FormName, Query1.FormRev,
Query1.SeriesRev, Left([ModelType],InStr(1,[ModelType]," ")) & "- " & [Door]
& " DOOR" AS DoorDesc, Query1.FormRevDate, Query1.ModelRev, Query1.[Component
Rev], Query1.QtyPer, Query1.BlowerCap, Query1.[Btu's]
FROM Query1 INNER JOIN MESPROD_LINESE ON Query1.MODNUM =
MESPROD_LINESE.[Model Number]
WHERE (((MESPROD_LINESE.[Line Number])="7128A" Or (MESPROD_LINESE.[Line
Number])="7128B") AND ((MESPROD_LINESE.[Run Date]) Between [Type the
beginning date:] And [Type the ending date:]))
ORDER BY MESPROD_LINESE.[Seq Number], Query1.[Order Index], Query1.[Check
Item Index];

From searching the forums;
I tried using CDATE([Type the beginning date:]) And CDATE([Type the ending
date:]) but it made no difference.

Any help is greatly appreciated!
Wally
 
I can only see two things:

1. Query1.Note "Note" is a reserved word. Check
http://support.microsoft.com/kb/286335/
for more about reserved words. You could fix it like this:

Query1.[Note]

2. You are using parameters, but not defining the parameter data types. Your
SQL should have a first line like this including the semicolon:

PARAMETERS [Type the beginning date:] DateTime, [Type the ending date:]
DateTime;
 
Thanks Jerry for the reply.
Everything worked before I added new data and still does with backed up db
before I added data. (I always backup before working on upgrading) I tried
your suggestions and it still doing the same thing. I'm really confused how
just adding data to the tables can have this effect. Never seen this before.
Both db's (upgraded one and backed up one) is 2003 format running in 2007.

Thanks again,
Wally

Jerry Whittle said:
I can only see two things:

1. Query1.Note "Note" is a reserved word. Check
http://support.microsoft.com/kb/286335/
for more about reserved words. You could fix it like this:

Query1.[Note]

2. You are using parameters, but not defining the parameter data types. Your
SQL should have a first line like this including the semicolon:

PARAMETERS [Type the beginning date:] DateTime, [Type the ending date:]
DateTime;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


wallymeister said:
I have a database that I am updating w/ new Models. Got all the Models added
and then tried to run the report on these Models. Report uses Query2A which
uses Query1 linked to MESPROD_LINESE table. It asks for start date and end
date for the report. Since I added the new models, when I run the report, I
get {Expression typed incorrect or too complicated] error message. Query1
runs by itself fine though.

Here is the SQL for Query2A of the Report. (it all worked fine before)

SELECT MESPROD_LINESE.[Line Number], MESPROD_LINESE.[Release Number],
MESPROD_LINESE.[Model Number], MESPROD_LINESE.Quantity, MESPROD_LINESE.[Run
Date], MESPROD_LINESE.[Seq Number], Query1.[Order Index], Query1.[Check Item
Index], Query1.AssyName, Query1.PartName, Query1.PartNum, Query1.Casing,
Query1.Cells, Query1.[Lo-Nox], Query1.ModelType, Query1.Door, Query1.Note,
Query1.Shaded, Query1.Comments, Query1.QE, Query1.FormName, Query1.FormRev,
Query1.SeriesRev, Left([ModelType],InStr(1,[ModelType]," ")) & "- " & [Door]
& " DOOR" AS DoorDesc, Query1.FormRevDate, Query1.ModelRev, Query1.[Component
Rev], Query1.QtyPer, Query1.BlowerCap, Query1.[Btu's]
FROM Query1 INNER JOIN MESPROD_LINESE ON Query1.MODNUM =
MESPROD_LINESE.[Model Number]
WHERE (((MESPROD_LINESE.[Line Number])="7128A" Or (MESPROD_LINESE.[Line
Number])="7128B") AND ((MESPROD_LINESE.[Run Date]) Between [Type the
beginning date:] And [Type the ending date:]))
ORDER BY MESPROD_LINESE.[Seq Number], Query1.[Order Index], Query1.[Check
Item Index];

From searching the forums;
I tried using CDATE([Type the beginning date:]) And CDATE([Type the ending
date:]) but it made no difference.

Any help is greatly appreciated!
Wally
Click to expand...
Click to expand...
 
I would make a backup and put it in a safe place. Then do a Compact and
Repair. See if that helps.

I'd also check the new data for things like null values that never showed up
in fields before. Nulls could cause problems.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


wallymeister said:
Thanks Jerry for the reply.
Everything worked before I added new data and still does with backed up db
before I added data. (I always backup before working on upgrading) I tried
your suggestions and it still doing the same thing. I'm really confused how
just adding data to the tables can have this effect. Never seen this before.
Both db's (upgraded one and backed up one) is 2003 format running in 2007.

Thanks again,
Wally

Jerry Whittle said:
I can only see two things:

1. Query1.Note "Note" is a reserved word. Check
http://support.microsoft.com/kb/286335/
for more about reserved words. You could fix it like this:

Query1.[Note]

2. You are using parameters, but not defining the parameter data types. Your
SQL should have a first line like this including the semicolon:

PARAMETERS [Type the beginning date:] DateTime, [Type the ending date:]
DateTime;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


wallymeister said:
I have a database that I am updating w/ new Models. Got all the Models added
and then tried to run the report on these Models. Report uses Query2A which
uses Query1 linked to MESPROD_LINESE table. It asks for start date and end
date for the report. Since I added the new models, when I run the report, I
get {Expression typed incorrect or too complicated] error message. Query1
runs by itself fine though.

Here is the SQL for Query2A of the Report. (it all worked fine before)

SELECT MESPROD_LINESE.[Line Number], MESPROD_LINESE.[Release Number],
MESPROD_LINESE.[Model Number], MESPROD_LINESE.Quantity, MESPROD_LINESE.[Run
Date], MESPROD_LINESE.[Seq Number], Query1.[Order Index], Query1.[Check Item
Index], Query1.AssyName, Query1.PartName, Query1.PartNum, Query1.Casing,
Query1.Cells, Query1.[Lo-Nox], Query1.ModelType, Query1.Door, Query1.Note,
Query1.Shaded, Query1.Comments, Query1.QE, Query1.FormName, Query1.FormRev,
Query1.SeriesRev, Left([ModelType],InStr(1,[ModelType]," ")) & "- " & [Door]
& " DOOR" AS DoorDesc, Query1.FormRevDate, Query1.ModelRev, Query1.[Component
Rev], Query1.QtyPer, Query1.BlowerCap, Query1.[Btu's]
FROM Query1 INNER JOIN MESPROD_LINESE ON Query1.MODNUM =
MESPROD_LINESE.[Model Number]
WHERE (((MESPROD_LINESE.[Line Number])="7128A" Or (MESPROD_LINESE.[Line
Number])="7128B") AND ((MESPROD_LINESE.[Run Date]) Between [Type the
beginning date:] And [Type the ending date:]))
ORDER BY MESPROD_LINESE.[Seq Number], Query1.[Order Index], Query1.[Check
Item Index];

From searching the forums;
I tried using CDATE([Type the beginning date:]) And CDATE([Type the ending
date:]) but it made no difference.

Any help is greatly appreciated!
Wally
Click to expand...
Click to expand...
Click to expand...
 
Jerry,
I did a compact and repair and it didn't help. I didn't really give the
null values any consideration but as I was looking at some data in my tables
I saw a field that had 15 null values. The Required property of this field
is set to "No" so in my mind this couldn't be causing my problem. Filled in
the data and "BAM" it is working fine now. Go figure! The errror message
doesn't really describe the problem very well. I would think that if null
values were the problem the error could better point this out. I'm just
saying.

Thanks a bunch. You guys are awsome.
Wally

Jerry Whittle said:
I would make a backup and put it in a safe place. Then do a Compact and
Repair. See if that helps.

I'd also check the new data for things like null values that never showed up
in fields before. Nulls could cause problems.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


wallymeister said:
Thanks Jerry for the reply.
Everything worked before I added new data and still does with backed up db
before I added data. (I always backup before working on upgrading) I tried
your suggestions and it still doing the same thing. I'm really confused how
just adding data to the tables can have this effect. Never seen this before.
Both db's (upgraded one and backed up one) is 2003 format running in 2007.

Thanks again,
Wally

Jerry Whittle said:
I can only see two things:

1. Query1.Note "Note" is a reserved word. Check
http://support.microsoft.com/kb/286335/
for more about reserved words. You could fix it like this:

Query1.[Note]

2. You are using parameters, but not defining the parameter data types. Your
SQL should have a first line like this including the semicolon:

PARAMETERS [Type the beginning date:] DateTime, [Type the ending date:]
DateTime;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a database that I am updating w/ new Models. Got all the Models added
and then tried to run the report on these Models. Report uses Query2A which
uses Query1 linked to MESPROD_LINESE table. It asks for start date and end
date for the report. Since I added the new models, when I run the report, I
get {Expression typed incorrect or too complicated] error message. Query1
runs by itself fine though.

Here is the SQL for Query2A of the Report. (it all worked fine before)

SELECT MESPROD_LINESE.[Line Number], MESPROD_LINESE.[Release Number],
MESPROD_LINESE.[Model Number], MESPROD_LINESE.Quantity, MESPROD_LINESE.[Run
Date], MESPROD_LINESE.[Seq Number], Query1.[Order Index], Query1.[Check Item
Index], Query1.AssyName, Query1.PartName, Query1.PartNum, Query1.Casing,
Query1.Cells, Query1.[Lo-Nox], Query1.ModelType, Query1.Door, Query1.Note,
Query1.Shaded, Query1.Comments, Query1.QE, Query1.FormName, Query1.FormRev,
Query1.SeriesRev, Left([ModelType],InStr(1,[ModelType]," ")) & "- " & [Door]
& " DOOR" AS DoorDesc, Query1.FormRevDate, Query1.ModelRev, Query1.[Component
Rev], Query1.QtyPer, Query1.BlowerCap, Query1.[Btu's]
FROM Query1 INNER JOIN MESPROD_LINESE ON Query1.MODNUM =
MESPROD_LINESE.[Model Number]
WHERE (((MESPROD_LINESE.[Line Number])="7128A" Or (MESPROD_LINESE.[Line
Number])="7128B") AND ((MESPROD_LINESE.[Run Date]) Between [Type the
beginning date:] And [Type the ending date:]))
ORDER BY MESPROD_LINESE.[Seq Number], Query1.[Order Index], Query1.[Check
Item Index];

From searching the forums;
I tried using CDATE([Type the beginning date:]) And CDATE([Type the ending
date:]) but it made no difference.

Any help is greatly appreciated!
Wally
Click to expand...
Click to expand...
Click to expand...
 
I know you have solved this problem. I am responding to your "Go figure!"
comment.

I've found that when nesting queries, that even if a query will run on its own
and handle an error by returning Error in the result field, when the query
is used in another query, you will often get a type mismatch error.

The error is probably in query1. You are probably doing something in that
query that cannot handle nulls and is generating an error.

To start, I would suspect any column in query1 that you are using in a where
clause or order by clause in query2A (Query1.[Order Index],
Query1.[Check Item Index]). Next I would look at Query1.MODNUM used in the
Join clause.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Jerry,
I did a compact and repair and it didn't help. I didn't really give the
null values any consideration but as I was looking at some data in my tables
I saw a field that had 15 null values. The Required property of this field
is set to "No" so in my mind this couldn't be causing my problem. Filled in
the data and "BAM" it is working fine now. Go figure! The errror message
doesn't really describe the problem very well. I would think that if null
values were the problem the error could better point this out. I'm just
saying.

Thanks a bunch. You guys are awsome.
Wally
Here is the SQL for Query2A of the Report. (it all worked fine before)

SELECT MESPROD_LINESE.[Line Number], MESPROD_LINESE.[Release Number],
MESPROD_LINESE.[Model Number], MESPROD_LINESE.Quantity, MESPROD_LINESE.[Run
Date], MESPROD_LINESE.[Seq Number], Query1.[Order Index], Query1.[Check Item
Index], Query1.AssyName, Query1.PartName, Query1.PartNum, Query1.Casing,
Query1.Cells, Query1.[Lo-Nox], Query1.ModelType, Query1.Door, Query1.Note,
Query1.Shaded, Query1.Comments, Query1.QE, Query1.FormName, Query1.FormRev,
Query1.SeriesRev, Left([ModelType],InStr(1,[ModelType]," ")) & "- " & [Door]
& " DOOR" AS DoorDesc, Query1.FormRevDate, Query1.ModelRev, Query1.[Component
Rev], Query1.QtyPer, Query1.BlowerCap, Query1.[Btu's]
FROM Query1 INNER JOIN MESPROD_LINESE ON Query1.MODNUM =
MESPROD_LINESE.[Model Number]
WHERE (((MESPROD_LINESE.[Line Number])="7128A" Or (MESPROD_LINESE.[Line
Number])="7128B") AND ((MESPROD_LINESE.[Run Date]) Between [Type the
beginning date:] And [Type the ending date:]))
ORDER BY MESPROD_LINESE.[Seq Number], Query1.[Order Index], Query1.[Check
Item Index];

From searching the forums;
I tried using CDATE([Type the beginning date:]) And CDATE([Type the ending
date:]) but it made no difference.

Any help is greatly appreciated!
Wally
Click to expand...
Click to expand...
Click to expand...
 
John,
Thanks for your input, this is good info to know. I also said that fields'
Required property is set to "No" but I overlooked that the "Allow zero
length" property is also set to "No". This could have been the problem.
Anyway, thanks again for the additional info.

Wally

John Spencer said:
I know you have solved this problem. I am responding to your "Go figure!"
comment.

I've found that when nesting queries, that even if a query will run on its own
and handle an error by returning Error in the result field, when the query
is used in another query, you will often get a type mismatch error.

The error is probably in query1. You are probably doing something in that
query that cannot handle nulls and is generating an error.

To start, I would suspect any column in query1 that you are using in a where
clause or order by clause in query2A (Query1.[Order Index],
Query1.[Check Item Index]). Next I would look at Query1.MODNUM used in the
Join clause.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Jerry,
I did a compact and repair and it didn't help. I didn't really give the
null values any consideration but as I was looking at some data in my tables
I saw a field that had 15 null values. The Required property of this field
is set to "No" so in my mind this couldn't be causing my problem. Filled in
the data and "BAM" it is working fine now. Go figure! The errror message
doesn't really describe the problem very well. I would think that if null
values were the problem the error could better point this out. I'm just
saying.

Thanks a bunch. You guys are awsome.
Wally
Here is the SQL for Query2A of the Report. (it all worked fine before)

SELECT MESPROD_LINESE.[Line Number], MESPROD_LINESE.[Release Number],
MESPROD_LINESE.[Model Number], MESPROD_LINESE.Quantity, MESPROD_LINESE.[Run
Date], MESPROD_LINESE.[Seq Number], Query1.[Order Index], Query1.[Check Item
Index], Query1.AssyName, Query1.PartName, Query1.PartNum, Query1.Casing,
Query1.Cells, Query1.[Lo-Nox], Query1.ModelType, Query1.Door, Query1.Note,
Query1.Shaded, Query1.Comments, Query1.QE, Query1.FormName, Query1.FormRev,
Query1.SeriesRev, Left([ModelType],InStr(1,[ModelType]," ")) & "- " & [Door]
& " DOOR" AS DoorDesc, Query1.FormRevDate, Query1.ModelRev, Query1.[Component
Rev], Query1.QtyPer, Query1.BlowerCap, Query1.[Btu's]
FROM Query1 INNER JOIN MESPROD_LINESE ON Query1.MODNUM =
MESPROD_LINESE.[Model Number]
WHERE (((MESPROD_LINESE.[Line Number])="7128A" Or (MESPROD_LINESE.[Line
Number])="7128B") AND ((MESPROD_LINESE.[Run Date]) Between [Type the
beginning date:] And [Type the ending date:]))
ORDER BY MESPROD_LINESE.[Seq Number], Query1.[Order Index], Query1.[Check
Item Index];

From searching the forums;
I tried using CDATE([Type the beginning date:]) And CDATE([Type the ending
date:]) but it made no difference.

Any help is greatly appreciated!
Wally
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top