If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Expression typed incorrect or too complicated
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 |
#2
|
|||
|
|||
Expression typed incorrect or too complicated
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" wrote: 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 |
#3
|
|||
|
|||
Expression typed incorrect or too complicated
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" wrote: 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" wrote: 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 |
#4
|
|||
|
|||
Expression typed incorrect or too complicated
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" wrote: 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" wrote: 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" wrote: 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 |
#5
|
|||
|
|||
Expression typed incorrect or too complicated
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#6
|
|||
|
|||
Expression typed incorrect or too complicated
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 wallymeister wrote: 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 |
#7
|
|||
|
|||
Expression typed incorrect or too complicated
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" wrote: 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 wallymeister wrote: 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 . |
Thread Tools | |
Display Modes | |
|
|