Union Query Access 2007 SP2 MSO

  • Follow


I have a union query that keeps telling me that there is a data type 
mismatch. When I first run the query everything is okay and it displays the 
fields accordingly. Then using the record selector I tell it to go to the 
last record. The query runs a little bit and then sends the error message 
Data type mismatch in criteria expression. (Error 3464). 

I have triple checked all the fields to make sure they are in the correct 
order and have the correct data. I can run the query with any combination of 
two queries and there is no problem. A check of the records reveals that each 
query is compatible with the other. I have a Parts query with 44,905 records, 
a Labor query with 49,956 records and a Sublets query with 11,239 records. 
The only time I get the error is when I have three queries within the Union. 
Parts and Sublet produce 561,144 records, Parts and Labor produce 94,861 
records; Labor and Sublet produce 61,195 records all with no errors. My code 
is listed below:

SELECT [WO Master Parts 03].BD, [WO Master Parts 03].Department, 1 AS 
Segment, [WO Master Parts 03].WorkOrderUID, [WO Master Parts 
03].WorkOrderNumber, [WO Master Parts 03].WOServiceUID, [WO Master Parts 
03].Type, [WO Master Parts 03].NPD, [WO Master Parts 03].Total, [WO Master 
Parts 03].Quantity, [WO Master Parts 03].StoreRoom, [WO Master Parts 
03].PartNumber, Null AS StartDate, Null AS EndDate, Null AS HrTime
FROM [WO Master Parts 03]

UNION ALL SELECT [WO Master Labor 03].BD, [WO Master Labor 03].Department, 2 
AS Segment, [WO Master Labor 03].WorkOrderUID, [WO Master Labor 
03].WorkOrderNumber, [WO Master Labor 03].WOServiceUID, [WO Master Labor 
03].Type, [WO Master Labor 03].NPD, [WO Master Labor 03].Total, Null AS 
Quantity, Null AS StoreRoom, Null AS PartNumber, [WO Master Labor 
03].StartDate, [WO Master Labor 03].EndDate, [WO Master Labor 03].HrTime
FROM [WO Master Labor 03]

UNION ALL SELECT [WO Master Sublet 03].BD, [WO Master Sublet 03].Department, 
3 AS Segment, [WO Master Sublet 03].WorkOrderUID, [WO Master Sublet 
03].WorkOrderNumber, [WO Master Sublet 03].WOServiceUID, [WO Master Sublet 
03].Type, [WO Master Sublet 03].NPD, [WO Master Sublet 03].Total, Null AS 
Quantity, Null AS StoreRoom, Null AS PartNumber, Null AS StartDate, Null AS 
EndDate, Null AS HrTime
FROM [WO Master Sublet 03];

Can anyone assist me and tell me the error of my code?? Please

-- 
Bruce
0
Reply Utf 5/20/2010 11:56:01 AM

Are any of these field Memo datatypes? I've run into strange things with 
corrupt memo fields before. If any are Memos, try removing those fields from 
the SQL and see if the union query then works.

Actually that might be a good troubleshooting method for the rest of the 
fields. Remove some of them and see what does or doesn't work.

Another thought would be to create a union query with just two of the 
tables, which you say works, then create another union query based on this 
plus the third table. You might try different combinations of this to see if 
there is a problem with a particular table.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Bruce" wrote:

> I have a union query that keeps telling me that there is a data type 
> mismatch. When I first run the query everything is okay and it displays the 
> fields accordingly. Then using the record selector I tell it to go to the 
> last record. The query runs a little bit and then sends the error message 
> Data type mismatch in criteria expression. (Error 3464). 
> 
> I have triple checked all the fields to make sure they are in the correct 
> order and have the correct data. I can run the query with any combination of 
> two queries and there is no problem. A check of the records reveals that each 
> query is compatible with the other. I have a Parts query with 44,905 records, 
> a Labor query with 49,956 records and a Sublets query with 11,239 records. 
> The only time I get the error is when I have three queries within the Union. 
> Parts and Sublet produce 561,144 records, Parts and Labor produce 94,861 
> records; Labor and Sublet produce 61,195 records all with no errors. My code 
> is listed below:
> 
> SELECT [WO Master Parts 03].BD, [WO Master Parts 03].Department, 1 AS 
> Segment, [WO Master Parts 03].WorkOrderUID, [WO Master Parts 
> 03].WorkOrderNumber, [WO Master Parts 03].WOServiceUID, [WO Master Parts 
> 03].Type, [WO Master Parts 03].NPD, [WO Master Parts 03].Total, [WO Master 
> Parts 03].Quantity, [WO Master Parts 03].StoreRoom, [WO Master Parts 
> 03].PartNumber, Null AS StartDate, Null AS EndDate, Null AS HrTime
> FROM [WO Master Parts 03]
> 
> UNION ALL SELECT [WO Master Labor 03].BD, [WO Master Labor 03].Department, 2 
> AS Segment, [WO Master Labor 03].WorkOrderUID, [WO Master Labor 
> 03].WorkOrderNumber, [WO Master Labor 03].WOServiceUID, [WO Master Labor 
> 03].Type, [WO Master Labor 03].NPD, [WO Master Labor 03].Total, Null AS 
> Quantity, Null AS StoreRoom, Null AS PartNumber, [WO Master Labor 
> 03].StartDate, [WO Master Labor 03].EndDate, [WO Master Labor 03].HrTime
> FROM [WO Master Labor 03]
> 
> UNION ALL SELECT [WO Master Sublet 03].BD, [WO Master Sublet 03].Department, 
> 3 AS Segment, [WO Master Sublet 03].WorkOrderUID, [WO Master Sublet 
> 03].WorkOrderNumber, [WO Master Sublet 03].WOServiceUID, [WO Master Sublet 
> 03].Type, [WO Master Sublet 03].NPD, [WO Master Sublet 03].Total, Null AS 
> Quantity, Null AS StoreRoom, Null AS PartNumber, Null AS StartDate, Null AS 
> EndDate, Null AS HrTime
> FROM [WO Master Sublet 03];
> 
> Can anyone assist me and tell me the error of my code?? Please
> 
> -- 
> Bruce
0
Reply Utf 5/20/2010 3:34:09 PM


Jerry,

None of the fields are memo fields.

What I find strange is that any two combinations will run with no errors, it 
is just when I add that third query is when it blows up?

I did try creating a union with two of the queries then writing a query 
against the results of the union then tried to link the third query, same 
results, would not work. 

Is there a record limitation to a union query? I have another union query 
that has three queries with no problem. This query I am attempting would be 
over 100,000 records, do you think that might be a problem?

I am going to try and limit the size of the three query union to see if that 
works. 
-- 
Bruce


"Jerry Whittle" wrote:

> Are any of these field Memo datatypes? I've run into strange things with 
> corrupt memo fields before. If any are Memos, try removing those fields from 
> the SQL and see if the union query then works.
> 
> Actually that might be a good troubleshooting method for the rest of the 
> fields. Remove some of them and see what does or doesn't work.
> 
> Another thought would be to create a union query with just two of the 
> tables, which you say works, then create another union query based on this 
> plus the third table. You might try different combinations of this to see if 
> there is a problem with a particular table.
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "Bruce" wrote:
> 
> > I have a union query that keeps telling me that there is a data type 
> > mismatch. When I first run the query everything is okay and it displays the 
> > fields accordingly. Then using the record selector I tell it to go to the 
> > last record. The query runs a little bit and then sends the error message 
> > Data type mismatch in criteria expression. (Error 3464). 
> > 
> > I have triple checked all the fields to make sure they are in the correct 
> > order and have the correct data. I can run the query with any combination of 
> > two queries and there is no problem. A check of the records reveals that each 
> > query is compatible with the other. I have a Parts query with 44,905 records, 
> > a Labor query with 49,956 records and a Sublets query with 11,239 records. 
> > The only time I get the error is when I have three queries within the Union. 
> > Parts and Sublet produce 561,144 records, Parts and Labor produce 94,861 
> > records; Labor and Sublet produce 61,195 records all with no errors. My code 
> > is listed below:
> > 
> > SELECT [WO Master Parts 03].BD, [WO Master Parts 03].Department, 1 AS 
> > Segment, [WO Master Parts 03].WorkOrderUID, [WO Master Parts 
> > 03].WorkOrderNumber, [WO Master Parts 03].WOServiceUID, [WO Master Parts 
> > 03].Type, [WO Master Parts 03].NPD, [WO Master Parts 03].Total, [WO Master 
> > Parts 03].Quantity, [WO Master Parts 03].StoreRoom, [WO Master Parts 
> > 03].PartNumber, Null AS StartDate, Null AS EndDate, Null AS HrTime
> > FROM [WO Master Parts 03]
> > 
> > UNION ALL SELECT [WO Master Labor 03].BD, [WO Master Labor 03].Department, 2 
> > AS Segment, [WO Master Labor 03].WorkOrderUID, [WO Master Labor 
> > 03].WorkOrderNumber, [WO Master Labor 03].WOServiceUID, [WO Master Labor 
> > 03].Type, [WO Master Labor 03].NPD, [WO Master Labor 03].Total, Null AS 
> > Quantity, Null AS StoreRoom, Null AS PartNumber, [WO Master Labor 
> > 03].StartDate, [WO Master Labor 03].EndDate, [WO Master Labor 03].HrTime
> > FROM [WO Master Labor 03]
> > 
> > UNION ALL SELECT [WO Master Sublet 03].BD, [WO Master Sublet 03].Department, 
> > 3 AS Segment, [WO Master Sublet 03].WorkOrderUID, [WO Master Sublet 
> > 03].WorkOrderNumber, [WO Master Sublet 03].WOServiceUID, [WO Master Sublet 
> > 03].Type, [WO Master Sublet 03].NPD, [WO Master Sublet 03].Total, Null AS 
> > Quantity, Null AS StoreRoom, Null AS PartNumber, Null AS StartDate, Null AS 
> > EndDate, Null AS HrTime
> > FROM [WO Master Sublet 03];
> > 
> > Can anyone assist me and tell me the error of my code?? Please
> > 
> > -- 
> > Bruce
0
Reply Utf 5/20/2010 4:55:01 PM

Also on A2007, I just ran a simple union query on 3 tables that totaled 
2,244,414 records with no problems. Going to the last record using the record 
selector took a couple of minutes. These tables have 10 fields with various 
data types.

SELECT * from cargo_tariff_backup
union all 
SELECT * from cargo_tariff
union all 
SELECT * from cargo_tariff2;

Maybe you should simplify the number of fields especially where you have to 
insert the nulls and such.

Have you tried a compact and repair?
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Bruce" wrote:

> Jerry,
> 
> None of the fields are memo fields.
> 
> What I find strange is that any two combinations will run with no errors, it 
> is just when I add that third query is when it blows up?
> 
> I did try creating a union with two of the queries then writing a query 
> against the results of the union then tried to link the third query, same 
> results, would not work. 
> 
> Is there a record limitation to a union query? I have another union query 
> that has three queries with no problem. This query I am attempting would be 
> over 100,000 records, do you think that might be a problem?
> 
> I am going to try and limit the size of the three query union to see if that 
> works. 
> -- 
> Bruce
> 
> 
> "Jerry Whittle" wrote:
> 
> > Are any of these field Memo datatypes? I've run into strange things with 
> > corrupt memo fields before. If any are Memos, try removing those fields from 
> > the SQL and see if the union query then works.
> > 
> > Actually that might be a good troubleshooting method for the rest of the 
> > fields. Remove some of them and see what does or doesn't work.
> > 
> > Another thought would be to create a union query with just two of the 
> > tables, which you say works, then create another union query based on this 
> > plus the third table. You might try different combinations of this to see if 
> > there is a problem with a particular table.
> > -- 
> > Jerry Whittle, Microsoft Access MVP 
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > 
> > 
> > "Bruce" wrote:
> > 
> > > I have a union query that keeps telling me that there is a data type 
> > > mismatch. When I first run the query everything is okay and it displays the 
> > > fields accordingly. Then using the record selector I tell it to go to the 
> > > last record. The query runs a little bit and then sends the error message 
> > > Data type mismatch in criteria expression. (Error 3464). 
> > > 
> > > I have triple checked all the fields to make sure they are in the correct 
> > > order and have the correct data. I can run the query with any combination of 
> > > two queries and there is no problem. A check of the records reveals that each 
> > > query is compatible with the other. I have a Parts query with 44,905 records, 
> > > a Labor query with 49,956 records and a Sublets query with 11,239 records. 
> > > The only time I get the error is when I have three queries within the Union. 
> > > Parts and Sublet produce 561,144 records, Parts and Labor produce 94,861 
> > > records; Labor and Sublet produce 61,195 records all with no errors. My code 
> > > is listed below:
> > > 
> > > SELECT [WO Master Parts 03].BD, [WO Master Parts 03].Department, 1 AS 
> > > Segment, [WO Master Parts 03].WorkOrderUID, [WO Master Parts 
> > > 03].WorkOrderNumber, [WO Master Parts 03].WOServiceUID, [WO Master Parts 
> > > 03].Type, [WO Master Parts 03].NPD, [WO Master Parts 03].Total, [WO Master 
> > > Parts 03].Quantity, [WO Master Parts 03].StoreRoom, [WO Master Parts 
> > > 03].PartNumber, Null AS StartDate, Null AS EndDate, Null AS HrTime
> > > FROM [WO Master Parts 03]
> > > 
> > > UNION ALL SELECT [WO Master Labor 03].BD, [WO Master Labor 03].Department, 2 
> > > AS Segment, [WO Master Labor 03].WorkOrderUID, [WO Master Labor 
> > > 03].WorkOrderNumber, [WO Master Labor 03].WOServiceUID, [WO Master Labor 
> > > 03].Type, [WO Master Labor 03].NPD, [WO Master Labor 03].Total, Null AS 
> > > Quantity, Null AS StoreRoom, Null AS PartNumber, [WO Master Labor 
> > > 03].StartDate, [WO Master Labor 03].EndDate, [WO Master Labor 03].HrTime
> > > FROM [WO Master Labor 03]
> > > 
> > > UNION ALL SELECT [WO Master Sublet 03].BD, [WO Master Sublet 03].Department, 
> > > 3 AS Segment, [WO Master Sublet 03].WorkOrderUID, [WO Master Sublet 
> > > 03].WorkOrderNumber, [WO Master Sublet 03].WOServiceUID, [WO Master Sublet 
> > > 03].Type, [WO Master Sublet 03].NPD, [WO Master Sublet 03].Total, Null AS 
> > > Quantity, Null AS StoreRoom, Null AS PartNumber, Null AS StartDate, Null AS 
> > > EndDate, Null AS HrTime
> > > FROM [WO Master Sublet 03];
> > > 
> > > Can anyone assist me and tell me the error of my code?? Please
> > > 
> > > -- 
> > > Bruce
0
Reply Utf 5/20/2010 5:08:01 PM

Jerry,

I think I found the problem: I removed the two fields StartDate and EndDate, 
the query ran with no problems. Is the fact that Date is part of the field 
name and date is a reserved word causing the problem?

In the morning I am going to rewrite the last queries and alias these two 
fields and then try it again.

-- 
Bruce


"Jerry Whittle" wrote:

> Also on A2007, I just ran a simple union query on 3 tables that totaled 
> 2,244,414 records with no problems. Going to the last record using the record 
> selector took a couple of minutes. These tables have 10 fields with various 
> data types.
> 
> SELECT * from cargo_tariff_backup
> union all 
> SELECT * from cargo_tariff
> union all 
> SELECT * from cargo_tariff2;
> 
> Maybe you should simplify the number of fields especially where you have to 
> insert the nulls and such.
> 
> Have you tried a compact and repair?
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "Bruce" wrote:
> 
> > Jerry,
> > 
> > None of the fields are memo fields.
> > 
> > What I find strange is that any two combinations will run with no errors, it 
> > is just when I add that third query is when it blows up?
> > 
> > I did try creating a union with two of the queries then writing a query 
> > against the results of the union then tried to link the third query, same 
> > results, would not work. 
> > 
> > Is there a record limitation to a union query? I have another union query 
> > that has three queries with no problem. This query I am attempting would be 
> > over 100,000 records, do you think that might be a problem?
> > 
> > I am going to try and limit the size of the three query union to see if that 
> > works. 
> > -- 
> > Bruce
> > 
> > 
> > "Jerry Whittle" wrote:
> > 
> > > Are any of these field Memo datatypes? I've run into strange things with 
> > > corrupt memo fields before. If any are Memos, try removing those fields from 
> > > the SQL and see if the union query then works.
> > > 
> > > Actually that might be a good troubleshooting method for the rest of the 
> > > fields. Remove some of them and see what does or doesn't work.
> > > 
> > > Another thought would be to create a union query with just two of the 
> > > tables, which you say works, then create another union query based on this 
> > > plus the third table. You might try different combinations of this to see if 
> > > there is a problem with a particular table.
> > > -- 
> > > Jerry Whittle, Microsoft Access MVP 
> > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > 
> > > 
> > > "Bruce" wrote:
> > > 
> > > > I have a union query that keeps telling me that there is a data type 
> > > > mismatch. When I first run the query everything is okay and it displays the 
> > > > fields accordingly. Then using the record selector I tell it to go to the 
> > > > last record. The query runs a little bit and then sends the error message 
> > > > Data type mismatch in criteria expression. (Error 3464). 
> > > > 
> > > > I have triple checked all the fields to make sure they are in the correct 
> > > > order and have the correct data. I can run the query with any combination of 
> > > > two queries and there is no problem. A check of the records reveals that each 
> > > > query is compatible with the other. I have a Parts query with 44,905 records, 
> > > > a Labor query with 49,956 records and a Sublets query with 11,239 records. 
> > > > The only time I get the error is when I have three queries within the Union. 
> > > > Parts and Sublet produce 561,144 records, Parts and Labor produce 94,861 
> > > > records; Labor and Sublet produce 61,195 records all with no errors. My code 
> > > > is listed below:
> > > > 
> > > > SELECT [WO Master Parts 03].BD, [WO Master Parts 03].Department, 1 AS 
> > > > Segment, [WO Master Parts 03].WorkOrderUID, [WO Master Parts 
> > > > 03].WorkOrderNumber, [WO Master Parts 03].WOServiceUID, [WO Master Parts 
> > > > 03].Type, [WO Master Parts 03].NPD, [WO Master Parts 03].Total, [WO Master 
> > > > Parts 03].Quantity, [WO Master Parts 03].StoreRoom, [WO Master Parts 
> > > > 03].PartNumber, Null AS StartDate, Null AS EndDate, Null AS HrTime
> > > > FROM [WO Master Parts 03]
> > > > 
> > > > UNION ALL SELECT [WO Master Labor 03].BD, [WO Master Labor 03].Department, 2 
> > > > AS Segment, [WO Master Labor 03].WorkOrderUID, [WO Master Labor 
> > > > 03].WorkOrderNumber, [WO Master Labor 03].WOServiceUID, [WO Master Labor 
> > > > 03].Type, [WO Master Labor 03].NPD, [WO Master Labor 03].Total, Null AS 
> > > > Quantity, Null AS StoreRoom, Null AS PartNumber, [WO Master Labor 
> > > > 03].StartDate, [WO Master Labor 03].EndDate, [WO Master Labor 03].HrTime
> > > > FROM [WO Master Labor 03]
> > > > 
> > > > UNION ALL SELECT [WO Master Sublet 03].BD, [WO Master Sublet 03].Department, 
> > > > 3 AS Segment, [WO Master Sublet 03].WorkOrderUID, [WO Master Sublet 
> > > > 03].WorkOrderNumber, [WO Master Sublet 03].WOServiceUID, [WO Master Sublet 
> > > > 03].Type, [WO Master Sublet 03].NPD, [WO Master Sublet 03].Total, Null AS 
> > > > Quantity, Null AS StoreRoom, Null AS PartNumber, Null AS StartDate, Null AS 
> > > > EndDate, Null AS HrTime
> > > > FROM [WO Master Sublet 03];
> > > > 
> > > > Can anyone assist me and tell me the error of my code?? Please
> > > > 
> > > > -- 
> > > > Bruce
0
Reply Utf 5/20/2010 8:00:03 PM

It's not a reserved word problem. It would need to be a plain old Date 
without brackets around it for that to happen. However your Type field could 
be a problem!

http://support.microsoft.com/kb/286335/

You should double check that those fields are a Date/time data types.

Maybe insert a bogus date like #1/1/1950# instead of Null for those tables 
without those fields. See if the mixture of nulls and actual dates is the 
problem.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Bruce" wrote:

> Jerry,
> 
> I think I found the problem: I removed the two fields StartDate and EndDate, 
> the query ran with no problems. Is the fact that Date is part of the field 
> name and date is a reserved word causing the problem?
> 
> In the morning I am going to rewrite the last queries and alias these two 
> fields and then try it again.
> 
> -- 
> Bruce
> 
> 
> "Jerry Whittle" wrote:
> 
> > Also on A2007, I just ran a simple union query on 3 tables that totaled 
> > 2,244,414 records with no problems. Going to the last record using the record 
> > selector took a couple of minutes. These tables have 10 fields with various 
> > data types.
> > 
> > SELECT * from cargo_tariff_backup
> > union all 
> > SELECT * from cargo_tariff
> > union all 
> > SELECT * from cargo_tariff2;
> > 
> > Maybe you should simplify the number of fields especially where you have to 
> > insert the nulls and such.
> > 
> > Have you tried a compact and repair?
> > -- 
> > Jerry Whittle, Microsoft Access MVP 
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > 
> > 
> > "Bruce" wrote:
> > 
> > > Jerry,
> > > 
> > > None of the fields are memo fields.
> > > 
> > > What I find strange is that any two combinations will run with no errors, it 
> > > is just when I add that third query is when it blows up?
> > > 
> > > I did try creating a union with two of the queries then writing a query 
> > > against the results of the union then tried to link the third query, same 
> > > results, would not work. 
> > > 
> > > Is there a record limitation to a union query? I have another union query 
> > > that has three queries with no problem. This query I am attempting would be 
> > > over 100,000 records, do you think that might be a problem?
> > > 
> > > I am going to try and limit the size of the three query union to see if that 
> > > works. 
> > > -- 
> > > Bruce
> > > 
> > > 
> > > "Jerry Whittle" wrote:
> > > 
> > > > Are any of these field Memo datatypes? I've run into strange things with 
> > > > corrupt memo fields before. If any are Memos, try removing those fields from 
> > > > the SQL and see if the union query then works.
> > > > 
> > > > Actually that might be a good troubleshooting method for the rest of the 
> > > > fields. Remove some of them and see what does or doesn't work.
> > > > 
> > > > Another thought would be to create a union query with just two of the 
> > > > tables, which you say works, then create another union query based on this 
> > > > plus the third table. You might try different combinations of this to see if 
> > > > there is a problem with a particular table.
> > > > -- 
> > > > Jerry Whittle, Microsoft Access MVP 
> > > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > > 
> > > > 
> > > > "Bruce" wrote:
> > > > 
> > > > > I have a union query that keeps telling me that there is a data type 
> > > > > mismatch. When I first run the query everything is okay and it displays the 
> > > > > fields accordingly. Then using the record selector I tell it to go to the 
> > > > > last record. The query runs a little bit and then sends the error message 
> > > > > Data type mismatch in criteria expression. (Error 3464). 
> > > > > 
> > > > > I have triple checked all the fields to make sure they are in the correct 
> > > > > order and have the correct data. I can run the query with any combination of 
> > > > > two queries and there is no problem. A check of the records reveals that each 
> > > > > query is compatible with the other. I have a Parts query with 44,905 records, 
> > > > > a Labor query with 49,956 records and a Sublets query with 11,239 records. 
> > > > > The only time I get the error is when I have three queries within the Union. 
> > > > > Parts and Sublet produce 561,144 records, Parts and Labor produce 94,861 
> > > > > records; Labor and Sublet produce 61,195 records all with no errors. My code 
> > > > > is listed below:
> > > > > 
> > > > > SELECT [WO Master Parts 03].BD, [WO Master Parts 03].Department, 1 AS 
> > > > > Segment, [WO Master Parts 03].WorkOrderUID, [WO Master Parts 
> > > > > 03].WorkOrderNumber, [WO Master Parts 03].WOServiceUID, [WO Master Parts 
> > > > > 03].Type, [WO Master Parts 03].NPD, [WO Master Parts 03].Total, [WO Master 
> > > > > Parts 03].Quantity, [WO Master Parts 03].StoreRoom, [WO Master Parts 
> > > > > 03].PartNumber, Null AS StartDate, Null AS EndDate, Null AS HrTime
> > > > > FROM [WO Master Parts 03]
> > > > > 
> > > > > UNION ALL SELECT [WO Master Labor 03].BD, [WO Master Labor 03].Department, 2 
> > > > > AS Segment, [WO Master Labor 03].WorkOrderUID, [WO Master Labor 
> > > > > 03].WorkOrderNumber, [WO Master Labor 03].WOServiceUID, [WO Master Labor 
> > > > > 03].Type, [WO Master Labor 03].NPD, [WO Master Labor 03].Total, Null AS 
> > > > > Quantity, Null AS StoreRoom, Null AS PartNumber, [WO Master Labor 
> > > > > 03].StartDate, [WO Master Labor 03].EndDate, [WO Master Labor 03].HrTime
> > > > > FROM [WO Master Labor 03]
> > > > > 
> > > > > UNION ALL SELECT [WO Master Sublet 03].BD, [WO Master Sublet 03].Department, 
> > > > > 3 AS Segment, [WO Master Sublet 03].WorkOrderUID, [WO Master Sublet 
> > > > > 03].WorkOrderNumber, [WO Master Sublet 03].WOServiceUID, [WO Master Sublet 
> > > > > 03].Type, [WO Master Sublet 03].NPD, [WO Master Sublet 03].Total, Null AS 
> > > > > Quantity, Null AS StoreRoom, Null AS PartNumber, Null AS StartDate, Null AS 
> > > > > EndDate, Null AS HrTime
> > > > > FROM [WO Master Sublet 03];
> > > > > 
> > > > > Can anyone assist me and tell me the error of my code?? Please
> > > > > 
> > > > > -- 
> > > > > Bruce
0
Reply Utf 5/20/2010 8:34:01 PM

Jerry,

I inserted #1/1/1800# in the Labor and Sublet query (these were nulled out) 
of the StartDate and EndDate fields and the query ran with no problems. I 
then removed the bogus date in the Sublet query (last query) and the query 
ran with no problems. I then reinserted the bogus date in the Sublet query 
and removed from the Parts query (first query) and the query crashed. It 
seems that the parts query was the problem but yet when coupled with just the 
Labor query it ran with no errors??

I know that it works, but have trouble as to why when any combination of two 
queries worked without crashing, but by adding that third query it would 
fail. The logic does not compute with me (I am very anal and have to 
understand why things work or do not work versus just accepting that it works 
this way, versus not). I was even going to convert the date to a serial 
number and see if it would run, but will not waste any more time on this 
issue. I can null out the bogus values once I get the union through the 
process. 

Thank you for all of your assistance in this matter.
-- 
Bruce


"Jerry Whittle" wrote:

> It's not a reserved word problem. It would need to be a plain old Date 
> without brackets around it for that to happen. However your Type field could 
> be a problem!
> 
> http://support.microsoft.com/kb/286335/
> 
> You should double check that those fields are a Date/time data types.
> 
> Maybe insert a bogus date like #1/1/1950# instead of Null for those tables 
> without those fields. See if the mixture of nulls and actual dates is the 
> problem.
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "Bruce" wrote:
> 
> > Jerry,
> > 
> > I think I found the problem: I removed the two fields StartDate and EndDate, 
> > the query ran with no problems. Is the fact that Date is part of the field 
> > name and date is a reserved word causing the problem?
> > 
> > In the morning I am going to rewrite the last queries and alias these two 
> > fields and then try it again.
> > 
> > -- 
> > Bruce
> > 
> > 
> > "Jerry Whittle" wrote:
> > 
> > > Also on A2007, I just ran a simple union query on 3 tables that totaled 
> > > 2,244,414 records with no problems. Going to the last record using the record 
> > > selector took a couple of minutes. These tables have 10 fields with various 
> > > data types.
> > > 
> > > SELECT * from cargo_tariff_backup
> > > union all 
> > > SELECT * from cargo_tariff
> > > union all 
> > > SELECT * from cargo_tariff2;
> > > 
> > > Maybe you should simplify the number of fields especially where you have to 
> > > insert the nulls and such.
> > > 
> > > Have you tried a compact and repair?
> > > -- 
> > > Jerry Whittle, Microsoft Access MVP 
> > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > 
> > > 
> > > "Bruce" wrote:
> > > 
> > > > Jerry,
> > > > 
> > > > None of the fields are memo fields.
> > > > 
> > > > What I find strange is that any two combinations will run with no errors, it 
> > > > is just when I add that third query is when it blows up?
> > > > 
> > > > I did try creating a union with two of the queries then writing a query 
> > > > against the results of the union then tried to link the third query, same 
> > > > results, would not work. 
> > > > 
> > > > Is there a record limitation to a union query? I have another union query 
> > > > that has three queries with no problem. This query I am attempting would be 
> > > > over 100,000 records, do you think that might be a problem?
> > > > 
> > > > I am going to try and limit the size of the three query union to see if that 
> > > > works. 
> > > > -- 
> > > > Bruce
> > > > 
> > > > 
> > > > "Jerry Whittle" wrote:
> > > > 
> > > > > Are any of these field Memo datatypes? I've run into strange things with 
> > > > > corrupt memo fields before. If any are Memos, try removing those fields from 
> > > > > the SQL and see if the union query then works.
> > > > > 
> > > > > Actually that might be a good troubleshooting method for the rest of the 
> > > > > fields. Remove some of them and see what does or doesn't work.
> > > > > 
> > > > > Another thought would be to create a union query with just two of the 
> > > > > tables, which you say works, then create another union query based on this 
> > > > > plus the third table. You might try different combinations of this to see if 
> > > > > there is a problem with a particular table.
> > > > > -- 
> > > > > Jerry Whittle, Microsoft Access MVP 
> > > > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > > > 
> > > > > 
> > > > > "Bruce" wrote:
> > > > > 
> > > > > > I have a union query that keeps telling me that there is a data type 
> > > > > > mismatch. When I first run the query everything is okay and it displays the 
> > > > > > fields accordingly. Then using the record selector I tell it to go to the 
> > > > > > last record. The query runs a little bit and then sends the error message 
> > > > > > Data type mismatch in criteria expression. (Error 3464). 
> > > > > > 
> > > > > > I have triple checked all the fields to make sure they are in the correct 
> > > > > > order and have the correct data. I can run the query with any combination of 
> > > > > > two queries and there is no problem. A check of the records reveals that each 
> > > > > > query is compatible with the other. I have a Parts query with 44,905 records, 
> > > > > > a Labor query with 49,956 records and a Sublets query with 11,239 records. 
> > > > > > The only time I get the error is when I have three queries within the Union. 
> > > > > > Parts and Sublet produce 561,144 records, Parts and Labor produce 94,861 
> > > > > > records; Labor and Sublet produce 61,195 records all with no errors. My code 
> > > > > > is listed below:
> > > > > > 
> > > > > > SELECT [WO Master Parts 03].BD, [WO Master Parts 03].Department, 1 AS 
> > > > > > Segment, [WO Master Parts 03].WorkOrderUID, [WO Master Parts 
> > > > > > 03].WorkOrderNumber, [WO Master Parts 03].WOServiceUID, [WO Master Parts 
> > > > > > 03].Type, [WO Master Parts 03].NPD, [WO Master Parts 03].Total, [WO Master 
> > > > > > Parts 03].Quantity, [WO Master Parts 03].StoreRoom, [WO Master Parts 
> > > > > > 03].PartNumber, Null AS StartDate, Null AS EndDate, Null AS HrTime
> > > > > > FROM [WO Master Parts 03]
> > > > > > 
> > > > > > UNION ALL SELECT [WO Master Labor 03].BD, [WO Master Labor 03].Department, 2 
> > > > > > AS Segment, [WO Master Labor 03].WorkOrderUID, [WO Master Labor 
> > > > > > 03].WorkOrderNumber, [WO Master Labor 03].WOServiceUID, [WO Master Labor 
> > > > > > 03].Type, [WO Master Labor 03].NPD, [WO Master Labor 03].Total, Null AS 
> > > > > > Quantity, Null AS StoreRoom, Null AS PartNumber, [WO Master Labor 
> > > > > > 03].StartDate, [WO Master Labor 03].EndDate, [WO Master Labor 03].HrTime
> > > > > > FROM [WO Master Labor 03]
> > > > > > 
> > > > > > UNION ALL SELECT [WO Master Sublet 03].BD, [WO Master Sublet 03].Department, 
> > > > > > 3 AS Segment, [WO Master Sublet 03].WorkOrderUID, [WO Master Sublet 
> > > > > > 03].WorkOrderNumber, [WO Master Sublet 03].WOServiceUID, [WO Master Sublet 
> > > > > > 03].Type, [WO Master Sublet 03].NPD, [WO Master Sublet 03].Total, Null AS 
> > > > > > Quantity, Null AS StoreRoom, Null AS PartNumber, Null AS StartDate, Null AS 
> > > > > > EndDate, Null AS HrTime
> > > > > > FROM [WO Master Sublet 03];
> > > > > > 
> > > > > > Can anyone assist me and tell me the error of my code?? Please
> > > > > > 
> > > > > > -- 
> > > > > > Bruce
0
Reply Utf 5/21/2010 9:02:02 AM

Jerry,

Did some more checking found the following: the OBDC link for the Labor 
query has both date fields as a General Date. However when I checked the SQL 
table where these fields are located I found that the table data type is 
datetime and that nulls were not permitted for the StartDate field. Could 
this initial setting in the SQL table eventually cause the problem? This was 
after a View in SQL and at least three layers of queries before the union 
query. Logic tells me that if the no null setting in the table was going to 
be a problem that it would manifest itself the first time a null was inserted 
into this field. Another note, I had to remove both date fields before the 
union query would work, so this tells me too that this may not be the problem.
-- 
Bruce


"Jerry Whittle" wrote:

> It's not a reserved word problem. It would need to be a plain old Date 
> without brackets around it for that to happen. However your Type field could 
> be a problem!
> 
> http://support.microsoft.com/kb/286335/
> 
> You should double check that those fields are a Date/time data types.
> 
> Maybe insert a bogus date like #1/1/1950# instead of Null for those tables 
> without those fields. See if the mixture of nulls and actual dates is the 
> problem.
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "Bruce" wrote:
> 
> > Jerry,
> > 
> > I think I found the problem: I removed the two fields StartDate and EndDate, 
> > the query ran with no problems. Is the fact that Date is part of the field 
> > name and date is a reserved word causing the problem?
> > 
> > In the morning I am going to rewrite the last queries and alias these two 
> > fields and then try it again.
> > 
> > -- 
> > Bruce
> > 
> > 
> > "Jerry Whittle" wrote:
> > 
> > > Also on A2007, I just ran a simple union query on 3 tables that totaled 
> > > 2,244,414 records with no problems. Going to the last record using the record 
> > > selector took a couple of minutes. These tables have 10 fields with various 
> > > data types.
> > > 
> > > SELECT * from cargo_tariff_backup
> > > union all 
> > > SELECT * from cargo_tariff
> > > union all 
> > > SELECT * from cargo_tariff2;
> > > 
> > > Maybe you should simplify the number of fields especially where you have to 
> > > insert the nulls and such.
> > > 
> > > Have you tried a compact and repair?
> > > -- 
> > > Jerry Whittle, Microsoft Access MVP 
> > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > 
> > > 
> > > "Bruce" wrote:
> > > 
> > > > Jerry,
> > > > 
> > > > None of the fields are memo fields.
> > > > 
> > > > What I find strange is that any two combinations will run with no errors, it 
> > > > is just when I add that third query is when it blows up?
> > > > 
> > > > I did try creating a union with two of the queries then writing a query 
> > > > against the results of the union then tried to link the third query, same 
> > > > results, would not work. 
> > > > 
> > > > Is there a record limitation to a union query? I have another union query 
> > > > that has three queries with no problem. This query I am attempting would be 
> > > > over 100,000 records, do you think that might be a problem?
> > > > 
> > > > I am going to try and limit the size of the three query union to see if that 
> > > > works. 
> > > > -- 
> > > > Bruce
> > > > 
> > > > 
> > > > "Jerry Whittle" wrote:
> > > > 
> > > > > Are any of these field Memo datatypes? I've run into strange things with 
> > > > > corrupt memo fields before. If any are Memos, try removing those fields from 
> > > > > the SQL and see if the union query then works.
> > > > > 
> > > > > Actually that might be a good troubleshooting method for the rest of the 
> > > > > fields. Remove some of them and see what does or doesn't work.
> > > > > 
> > > > > Another thought would be to create a union query with just two of the 
> > > > > tables, which you say works, then create another union query based on this 
> > > > > plus the third table. You might try different combinations of this to see if 
> > > > > there is a problem with a particular table.
> > > > > -- 
> > > > > Jerry Whittle, Microsoft Access MVP 
> > > > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > > > 
> > > > > 
> > > > > "Bruce" wrote:
> > > > > 
> > > > > > I have a union query that keeps telling me that there is a data type 
> > > > > > mismatch. When I first run the query everything is okay and it displays the 
> > > > > > fields accordingly. Then using the record selector I tell it to go to the 
> > > > > > last record. The query runs a little bit and then sends the error message 
> > > > > > Data type mismatch in criteria expression. (Error 3464). 
> > > > > > 
> > > > > > I have triple checked all the fields to make sure they are in the correct 
> > > > > > order and have the correct data. I can run the query with any combination of 
> > > > > > two queries and there is no problem. A check of the records reveals that each 
> > > > > > query is compatible with the other. I have a Parts query with 44,905 records, 
> > > > > > a Labor query with 49,956 records and a Sublets query with 11,239 records. 
> > > > > > The only time I get the error is when I have three queries within the Union. 
> > > > > > Parts and Sublet produce 561,144 records, Parts and Labor produce 94,861 
> > > > > > records; Labor and Sublet produce 61,195 records all with no errors. My code 
> > > > > > is listed below:
> > > > > > 
> > > > > > SELECT [WO Master Parts 03].BD, [WO Master Parts 03].Department, 1 AS 
> > > > > > Segment, [WO Master Parts 03].WorkOrderUID, [WO Master Parts 
> > > > > > 03].WorkOrderNumber, [WO Master Parts 03].WOServiceUID, [WO Master Parts 
> > > > > > 03].Type, [WO Master Parts 03].NPD, [WO Master Parts 03].Total, [WO Master 
> > > > > > Parts 03].Quantity, [WO Master Parts 03].StoreRoom, [WO Master Parts 
> > > > > > 03].PartNumber, Null AS StartDate, Null AS EndDate, Null AS HrTime
> > > > > > FROM [WO Master Parts 03]
> > > > > > 
> > > > > > UNION ALL SELECT [WO Master Labor 03].BD, [WO Master Labor 03].Department, 2 
> > > > > > AS Segment, [WO Master Labor 03].WorkOrderUID, [WO Master Labor 
> > > > > > 03].WorkOrderNumber, [WO Master Labor 03].WOServiceUID, [WO Master Labor 
> > > > > > 03].Type, [WO Master Labor 03].NPD, [WO Master Labor 03].Total, Null AS 
> > > > > > Quantity, Null AS StoreRoom, Null AS PartNumber, [WO Master Labor 
> > > > > > 03].StartDate, [WO Master Labor 03].EndDate, [WO Master Labor 03].HrTime
> > > > > > FROM [WO Master Labor 03]
> > > > > > 
> > > > > > UNION ALL SELECT [WO Master Sublet 03].BD, [WO Master Sublet 03].Department, 
> > > > > > 3 AS Segment, [WO Master Sublet 03].WorkOrderUID, [WO Master Sublet 
> > > > > > 03].WorkOrderNumber, [WO Master Sublet 03].WOServiceUID, [WO Master Sublet 
> > > > > > 03].Type, [WO Master Sublet 03].NPD, [WO Master Sublet 03].Total, Null AS 
> > > > > > Quantity, Null AS StoreRoom, Null AS PartNumber, Null AS StartDate, Null AS 
> > > > > > EndDate, Null AS HrTime
> > > > > > FROM [WO Master Sublet 03];
> > > > > > 
> > > > > > Can anyone assist me and tell me the error of my code?? Please
> > > > > > 
> > > > > > -- 
> > > > > > Bruce
0
Reply Utf 5/21/2010 9:20:01 AM

7 Replies
496 Views

(page loaded in 0.207 seconds)


Reply: