Union Query Access 2007 SP2 MSO

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
Utf
5/20/2010 11:56:01 AM
access.queries 6343 articles. 1 followers. Follow

7 Replies
825 Views

Similar Articles

[PageSpeed] 3

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
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
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
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
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
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
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
Utf
5/21/2010 9:20:01 AM
Reply:

Similar Artilces:

Unhandled exception in app1.exe(WNRPC32.DLL): 0xC0000005: Access Violation.
hello, i have created a window which acts as a simulator window . In this window i have my application(app1.exe) running . when i close this application and then if i close the simulator window it gives this error: Unhandled exception in app1.exe(WNRPC32.DLL): 0xC0000005: Access Violation. and if i directly close the simulator window with out closing the application window , no error occurs. please help me what to do as i am very new to VC++ Thanks in advance meenal You have to do the proper cleaning...on ur window close..and sgould kill all the worker threads if any exist in ur applicti...

Union Query Problem
I am trying to use a union query to join 4 table together I have done the code but when it runs i get the following error ODBC - Call Failed [MySQL][ODBC3.51 Driver][mysql-4.1.22-standard] You have an error in your SQL syntax: check the manual that corresponds to your MySQL server version for the right syntax to use near Union( SELECT 'tblcodesWS' , 'tblProductsWS' , 'ProductName' , ' tblPr' at line 1 (#1064) Can any one help me If i if i split the Union query into 2 seperate ones they both work fine, just have problems when i put them together What...

International Money 2005 queries
Firstly - Apologies for raising International version queries in this forum. There does not appear to be a specific Australian NG. Ran 2005 Trial version O.K for the 60 days then brought Money Standard 'New for 2005' version a few weeks ago. Help|About shows it is Vrs 14.0.120.1105 A couple of queries. 1. There appears to be no built-in Help - selecting Help brings up a Help side panel with "A problem occurred, Please Try again link". Clicking on the link brings up "Get help with an MSN product" and a list of topics such as 'Billing, MSN Client, MSN Hotma...

Query Analyzer Question #2
We are running GP 9.0. One of our customers was bought out and all of thier stores have changed names. I am trying to find an easy was to go into our GP SQL database and change the Customer Name, The ShortName and StatementName to reflect the new name of the company. Does anyone know the query syntax I would use to make these changes? Am I posting in the correct group? TIA TRD Why not use Integration Mgr, its easier but since query analyzer is your choice then here's the basic sql statement but maybe somebody here can improve it: Update RM00101 set STMTNAME = ' ' where...

User denied access to reports after account name change
We have several users who have gotten married which has led to their account name being changed. Now the can not run any reports, they get an access denied error. The reports are being run in SharePoint integration mode. The data connections are not using "Windows authentication (integrated)" mode. All other users are fine. Any ideas? Hi I am not famililar with reports that run in SharePoint integration mode, perhaps SharePoint persmissions are the issue. "Rincewind60" <Rincewind60@discussions.microsoft.com> wrote in message news:584868...

Query to delete characters after first hypen
I'm looking to return all the text before the first hypen IBDT-1209-USD-B INGBFE-434142-EUR-B F/AGD-4079-NOK-B ADE-434129-EUR-B I would like to see: IBDT INGBFE F/AGD ADE Thanks for the assistance. U�ytkownik "Rachel" <Rachel@discussions.microsoft.com> napisa� w wiadomo�ci news:39E1E728-B534-4C11-B02F-0A9BA833E7E7@microsoft.com... > I'm looking to return all the text before the first hypen > > IBDT-1209-USD-B > INGBFE-434142-EUR-B > F/AGD-4079-NOK-B > ADE-434129-EUR-B > > I would like to see: > > IBDT > INGBFE > F/AGD > A...

Building a Query by Form Interface
Hello, I'm trying to create a method where a user selects some criteria in a form and a query then generates the results. I have been able to pass numbers successfully in the query but not text. If anyone has an easy way of developing this let me know. The code below works for numbers but not text Help me please. Function BuildSQLString(strSQL As String) As Boolean Dim strSELECT As String Dim strFROM As String Dim strWHERE As String strSELECT = "s.*" strFROM = "UFRRecords s " If Check2 Then strWHERE = strWHERE & " s.[FlagField1] = " & Combo0 End I...

Make Table query, but remove character from field
I already have a rroutine set up and part of that is a make table query. One of my fields has some values as G35.X and some as G35X. I want to make this field in my make table query read G35X for all. I do not want to run update queries or find/replace and want in my make table definition some code that when creating this field to remove the "." where it exists. Is this possible? Thanks Sorry, I should have made clear I can't just set the field to "G35X" as there are other values with the "." ! "Andy" wrote: > I already have ...

Rights required to access Exchange 'Mailbox Rights' in ADUC #2
*I believe I'm posting as a a manged newsgroup user with alias, but so far I'm not getting a response. This is my third try to see if I can get it to work.* Is it possible to provide IT staff access to manipulate Mailbox Rights under the Exchange Advanced tab in ADUC without giving them Exchange Administrator rights? We'd like our staff to be able to manipulate all aspects of users using ADUC, but not the back-end Exchange stuff in System Manager. However it appears to be an all-or-nothing situation, at least where access to the mailbox rights are concerned. The message...

Internationalisation queries
1. When entering numbers in Excel using scientific notation in the English version of Excel, the following convention is used "[number]E[number]", for example 12E3 (i.e. 12,000). Can someone tell me whether the "E" character is the same for all languages? 2. Error codes such in Excel such as "#N/A" and "#VALUE", can someone tell me whether these are the same for all languages? And if not, is there an easy way to determine what the error strings are in the language locale used by Excel? Many thanks, John www.johnmote.com ...

Access Denied to AL Attachments
Hello, one of the people in my office has started having a problem were they cannot open ANY attachment that comes into outlook express. They get the message "Can't open this item. Access Denied. Make sure the disk is not full or write protected and that the file is not being used." There is plenty of disk space (over 14 gig) We can write to the disk The file is not in use. The OS is Windows 2000 Pro SP 4 Outlook 2000 9.0.0.2711 Can someone help? What directory is outlook trying to open the file in? thats ALL attachments - no matter the file type. it looks like a per...

Analytical Accounting Queries for multiple years
Would like the ability to select multiple years within the queries in Analytical Accounting. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=e284dc78-f0fb-4f68-80eb-ee9026f...

Access Developer
I have used Microsoft Office Access Developer Extension 2007 and Microsoft office access run time 2007 to package and make access (exe) I will tell you what I have do: 1 - I open the data base that I want to (make it exe) and make setup file for this data base 2- I choose (Developer) ---- package and solution 3- In (installation option) I complete all text box. 4- in (installation option) I choose this option (require nothing and install access runtime ). (The file will be installed with Accdr file extension and access 2007 runtime support will be enabled) Then I choose Access 200...

how to calculate total time in query
Anyone know how to calculate total time in query if the total time exceed 24 hours? I wish the total time to be display in number of hours and minutes. See: Calculating elapsed time at: http://allenbrowne.com/casu-13.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. <xiaodan86@hotmail.com> wrote in message news:1176090276.654203.320320@y80g2000hsf.googlegroups.com... > Anyone know how to calculate total time in query if the total time > exceed...

CTE and union error on varchar field
I have a CTE that I am reading with a UNION. For some reason, it thinks the 1st field is a bit. But LastName from both tables are varchars - one is varchar(50) and the other is varchar(36). The Error I am geting is: Conversion failed when converting the varchar value 'Clartia, MD (ALCM-FT)' to data type bit. The first row does happen to be a 1 but the type of r.LastName is varchar(36) and u.LastName is varchar(50). Also, if I run the query without the UNION and only use one SELECT it works fine with both SELECTs. The one with the error is: WITH Distribution...

CRM Mobile querying recipientAddr,recipientSrvcPort etc
Hi there Since we installed CRMMobile our (new) server has been SO SLOW! SqlServer is taking anywhere from 69 - 99% cpu time (mostly around 99) I have run sql profiler and discovered that many times a second the following transactions are running: exec sp_executesql N'SELECT recipientAddr,recipientSrvcPort,sourceAddr,sourceSrvcPort, MIN(arrivalDate) as minArrivalDate, count(*) as messageCount FROM vwInBoundQueue WHERE recipientAddr=@recipientAddr AND recipientSrvcPort=@recipientSrvcPort GROUP BY sourceAddr,sourceSrvcPort,recipientAddr,recipientSrvcPort ORDER BY minArrivalDat...

Remove Certain String from Query Field
Hello Experts, I have a query with mulitple fields. one of them contains the names of the projects our depatrment is working on. the name starts with a 4 digit number, then an underscore, then a 4 or 5 digit number then another underscore then the Name of the proejct itself. so the field looks like this xxxx_xxxx_Project Name. I want to create an experssion that only shows the Project Name part without the numbers or the underscores. the Problem i'm having tis that the number of these digits vary from project to project. the total number of characters can very between 10 1...

Query Access
I am using VB.net 2008. I have a access database with 2 tables in it tblTeacher and tblStudent. I create a recordset (rsTeacher) one of the fields (TeacherName) in tblTeacher using distinct. Then I loop through the rsTeacher and then create another recordset (rsSchedule) and pull the Period and StudentID then I want to use that StudentID to query the tblStudent with the StudentID to get the StudentLastName, StudentFirstName and etc. then order by period, studentlastname, studentfirst. I usually cheat and use the query function in Access to do them but it doesn't seem like this...

How do I restrict Access to all mail groups on my Domain
Guys, How do I disable external mail access to all mail groups on my domain. Assuming I dont want anybody on the net to send an email to mail groups on my domain.I have checked the Exchange System Manager but I havent found anything explicit from global settings.Can anyone help? Thanks in Advance. Regards, -- Buchi Hi, hit properties of distr.group (Exchange general tab) and select Accept messages from authenticated users only... -- Regards, Sasa Milovanovic MCSE:Messaging sasa.milovanovic(at)exchangemaster.net "Buchi" <Buchi@discussions.microsoft.com> wrote in mes...

Purpose of Data Access Page?
Hi, I hope this doesn't seem like a stupid question. What is the purpose of a data access page? It seems to me you can accomplish a whole lot more with forms. Data access pages have many limitations. What would be an example of when you would want to use a data access page over a form? Forms: PC use Data Access page: Web use -- HTH, George "REGREGL" <REGREGL@discussions.microsoft.com> wrote in message news:B59B6E5E-4096-4BA8-98E2-F24AD7305409@microsoft.com... > Hi, > > I hope this doesn't seem like a stupid question. What is the purpose of a &...

Union or Union All
I am trying to combine two tables into one but when I try to combine the text fields it gives me null values Table A Customer SalesPerson TEXT TEXT2 A B ABCD EFGH B C BCDE FGHI A You hit the send key too early. You only posted data from one table and then no results of the union query. You did not post your query SQL. I can not see into to the other screens on your computer. -- KARL DEWEY Build a little - Test a little "FSUrules2883@aol.com" wrote: &...

System Data Protection 2007 on a SBS 2008 Domain
Hi all, This is a lab situation where I have SBS 2008 running on HyperV and doing well... I would like to install Server 2008R2, give it 2 or 4 GBs RAM, 2 CPUs and then install System Data Protection 2007 Has anyone tried this? Is it doable at all? Thanks to all, Dan On Jan 7, 11:52=A0am, "Daniel Jewel" <cyberdud...@yahoo.com.br> wrote: > Hi all, > > This is a lab situation where I have SBS 2008 running on HyperV and doing > well... > > I would like to install Server 2008R2, give it 2 or 4 GBs RAM, 2 CPUs and > then install Sy...

Importing from Access issue
When I import a table from Access (either through a TransferSpreadsheet of a table or a manual export) for some strange reason, the fields do not stay in the same place as they are in the Access table. It appears to be an Excel issue but I cannot figure it out. For example, I have an Access table that has, say, 5 fields....Field1, Field2, Field3, Field4, and Field 5. It is possible for a field to be blank for a given record. Everything is fine in the Access table. When I use the macro or manually export, Field 5 data shows up in Field4 IF Field 4 is blank. Otherwise, Field4 contains Fie...

embedding font problem in word 2007/2010
Hi The company I work for uses a non standard Font. When I embed the font in Word 2007 or 2010 (docx) I have two problems: 1. If you send the document to a person that doesnt have the font and hava a older word version the font will be replaced. so if I save the file in 2010 and open it in 2007 or 2003 on a machine that doesnt have the font it will be replaced even if the font is true type and embedable. If I save the document in 2010 or 2007 and uses the old fileformat (.doc) the font is correctly embedded. 2. the filesize increses for example from 166 kb (not embedded) to ...

Using Union All in a stored proc
I have the following Stored Proc that works...basically it provides a dummy record for me to work with. I want to ad another record to this. I thought I could do something like a UNION ALL to make two records but it doesn't work. What's the easiest way to do this? See original SP, then edited SP below that... Thanks! -- Begin Original SP -- USE [CODS] GO /****** Object: StoredProcedure [dbo].[AHIS_GET_PATIENT_INFORMATION] Script Date: 05/28/2010 13:35:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================...