If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Prevent query from changing number values to text values
I have three queries, each building off the previous, and by the final query,
some of my number data has been converted to text data, and I need to prevent this from happening. Here is the situation: Query1: Crosstab query Input Fields: IntrrptID, PTaskID, Category, Minutes (integer) Output Fields: PTaskID, CategoryA, CategoryB, CategoryC . . . (each containing the sum of minutes for that category for that PTaskID) Query2: Input Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Category 1-m are from table PrimaryTaskLogs (integer), Category A-n are from Query1. Output Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Query3: Input Fields: (Query2) Output Fields: PTaskID, Category1, Category2+A, Category3, Category4+D, Category1+2+A+7+F+G, . . . Most of my fields that are summing other fields are coming out correctly, but one of them is coming out 540+0=5400, 0+0=00, 0+10=010, etc., so clearly it thinks the data is text. Why would this happen? I start with integer data, and all I do with that data is crosstab it (uses a sum), sum some of it, and just shuffle other data into the next query with an occassional Field rename. I am baffled. I know I can fix it by using CInt along the way, but I would really like to know why this is happening in the first place, and if there is a better way for me to prevent it. |
#2
|
|||
|
|||
Prevent query from changing number values to text values
I have an update. It looks like all the fields in Query3 that came from the
CrossTab Query and were not combined with other Fields are the ones that are coming through as text. Why? "Renraf" wrote: I have three queries, each building off the previous, and by the final query, some of my number data has been converted to text data, and I need to prevent this from happening. Here is the situation: Query1: Crosstab query Input Fields: IntrrptID, PTaskID, Category, Minutes (integer) Output Fields: PTaskID, CategoryA, CategoryB, CategoryC . . . (each containing the sum of minutes for that category for that PTaskID) Query2: Input Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Category 1-m are from table PrimaryTaskLogs (integer), Category A-n are from Query1. Output Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Query3: Input Fields: (Query2) Output Fields: PTaskID, Category1, Category2+A, Category3, Category4+D, Category1+2+A+7+F+G, . . . Most of my fields that are summing other fields are coming out correctly, but one of them is coming out 540+0=5400, 0+0=00, 0+10=010, etc., so clearly it thinks the data is text. Why would this happen? I start with integer data, and all I do with that data is crosstab it (uses a sum), sum some of it, and just shuffle other data into the next query with an occassional Field rename. I am baffled. I know I can fix it by using CInt along the way, but I would really like to know why this is happening in the first place, and if there is a better way for me to prevent it. |
#3
|
|||
|
|||
Prevent query from changing number values to text values
A guess since you haven't shown us the ACTUAL SQL of the queries.
Did you use the NZ function in the crosstab query to force zero in place of nulls? If so, that will return text values that look like numbers. You can convert that back to a string by using something like the following. CLng(Nz(Sum([Minutes]),0)) Not knowing the size of your sums I used CLng instead of CInt. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Renraf wrote: I have an update. It looks like all the fields in Query3 that came from the CrossTab Query and were not combined with other Fields are the ones that are coming through as text. Why? "Renraf" wrote: I have three queries, each building off the previous, and by the final query, some of my number data has been converted to text data, and I need to prevent this from happening. Here is the situation: Query1: Crosstab query Input Fields: IntrrptID, PTaskID, Category, Minutes (integer) Output Fields: PTaskID, CategoryA, CategoryB, CategoryC . . . (each containing the sum of minutes for that category for that PTaskID) Query2: Input Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Category 1-m are from table PrimaryTaskLogs (integer), Category A-n are from Query1. Output Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Query3: Input Fields: (Query2) Output Fields: PTaskID, Category1, Category2+A, Category3, Category4+D, Category1+2+A+7+F+G, . . . Most of my fields that are summing other fields are coming out correctly, but one of them is coming out 540+0=5400, 0+0=00, 0+10=010, etc., so clearly it thinks the data is text. Why would this happen? I start with integer data, and all I do with that data is crosstab it (uses a sum), sum some of it, and just shuffle other data into the next query with an occassional Field rename. I am baffled. I know I can fix it by using CInt along the way, but I would really like to know why this is happening in the first place, and if there is a better way for me to prevent it. |
#4
|
|||
|
|||
Prevent query from changing number values to text values
Show us the SQL. Open the queries in design view. Next go to View, SQL View
and copy and past them here. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Renraf" wrote: I have an update. It looks like all the fields in Query3 that came from the CrossTab Query and were not combined with other Fields are the ones that are coming through as text. Why? "Renraf" wrote: I have three queries, each building off the previous, and by the final query, some of my number data has been converted to text data, and I need to prevent this from happening. Here is the situation: Query1: Crosstab query Input Fields: IntrrptID, PTaskID, Category, Minutes (integer) Output Fields: PTaskID, CategoryA, CategoryB, CategoryC . . . (each containing the sum of minutes for that category for that PTaskID) Query2: Input Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Category 1-m are from table PrimaryTaskLogs (integer), Category A-n are from Query1. Output Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Query3: Input Fields: (Query2) Output Fields: PTaskID, Category1, Category2+A, Category3, Category4+D, Category1+2+A+7+F+G, . . . Most of my fields that are summing other fields are coming out correctly, but one of them is coming out 540+0=5400, 0+0=00, 0+10=010, etc., so clearly it thinks the data is text. Why would this happen? I start with integer data, and all I do with that data is crosstab it (uses a sum), sum some of it, and just shuffle other data into the next query with an occassional Field rename. I am baffled. I know I can fix it by using CInt along the way, but I would really like to know why this is happening in the first place, and if there is a better way for me to prevent it. |
#5
|
|||
|
|||
Prevent query from changing number values to text values
In the process of copying the SQL, I remembered that I was using this
definition of my Fields in the second query for all of the values from the Crosstab: IntField1: Nz([IntDetails]![Field1],0) I can only assume that was the problem. Do you know why the 0 is interpreted as a string instead of a numerical value? Shouldn't it be "0" to be considered a string? And how would I force it to be a value? Anyway, I replaced the code above with the following, and it works correctly now: IntField1: CInt(Nz([IntDetails]![Field1],0)) Should I do this instead? : IntField1: Nz([IntDetails]![Field1],CInt(0)) And here's the (corrected) SQL, in case my withholding information is aggravating: IntDetails (query1) TRANSFORM Sum(Interruptions.Minutes) AS SumOfMinutes SELECT Interruptions.PTaskID, Count(Interruptions.IntrrptID) AS IntCount, Sum(Interruptions.Minutes) AS IntMinSum FROM (DailyLogs INNER JOIN PrimaryTaskLogs ON DailyLogs.LogID = PrimaryTaskLogs.LogID) INNER JOIN (NonTicketCategories INNER JOIN Interruptions ON NonTicketCategories.CategoryID = Interruptions.Category) ON PrimaryTaskLogs.PTaskID = Interruptions.PTaskID WHERE (((DailyLogs.InvalidData)=0)) GROUP BY Interruptions.PTaskID PIVOT NonTicketCategories.CategoryName; TaskTimeDetails (query2) SELECT PrimaryTaskLogs.PTaskID, DailyLogs.Date, PrimaryTaskLogs.LogID, DailyLogs.StudyID, PrimaryTaskLogs.Category AS TaskCtgry, PrimaryTaskLogs.Start, PrimaryTaskLogs.Stop, PrimaryTaskLogs.Overnight, DateDiff("n",[Start],[Stop])-[Overnight]*1440-[TktMinSum]-[IntMinSum] AS TimeDiff, [TktPlRvwOrder]+[TktMngMtrls]+[TktPrfrmTask]+[TktTravel]+[TktSysUpdt]+[TktVendCtct]+[TktPhone]+[TktEmail] AS TktMinSum, Nz(IntDetails!IntMinSum,0) AS IntMinSum, Nz(IntDetails!IntCount,0) AS IntCount, PrimaryTaskLogs.PullReviewOrder AS TktPlRvwOrder, PrimaryTaskLogs.ManageMaterials AS TktMngMtrls, PrimaryTaskLogs.PerformTask AS TktPrfrmTask, PrimaryTaskLogs.Travel AS TktTravel, PrimaryTaskLogs.SystemUpdate AS TktSysUpdt, PrimaryTaskLogs.VendorContact AS TktVendCtct, PrimaryTaskLogs.Phone AS TktPhone, PrimaryTaskLogs.Email AS TktEmail, CInt(Nz(IntDetails!Break,0)) AS IntBreak, CInt(Nz(IntDetails!Email,0)) AS IntEmail, CInt(Nz(IntDetails![General Maintenance],0)) AS IntGenMaint, CInt(Nz(IntDetails!Lunch,0)) AS IntLunch, CInt(Nz(IntDetails![Manage Materials],0)) AS IntMngMtrls, CInt(Nz(IntDetails!Meeting,0)) AS IntMeeting, CInt(Nz(IntDetails!Other,0)) AS IntOther, CInt(Nz(IntDetails!Phone,0)) AS IntPhone, CInt(Nz(IntDetails!PTO,0)) AS IntPTO, CInt(Nz(IntDetails!Training,0)) AS IntTraining, CInt(Nz(IntDetails!Travel,0)) AS IntTravel, CInt(Nz(IntDetails![Vendor Contact],0)) AS IntVendCtct FROM Studies INNER JOIN (DailyLogs INNER JOIN (PrimaryTaskLogs LEFT JOIN IntDetails ON PrimaryTaskLogs.PTaskID=IntDetails.PTaskID) ON DailyLogs.LogID=PrimaryTaskLogs.LogID) ON Studies.StudyID=DailyLogs.StudyID WHERE (((DailyLogs.InvalidData)=0)); AdminDetails (query3): SELECT TaskTimeDetails.PTaskID, TaskTimeDetails.Date, TaskTimeDetails.LogID, TaskTimeDetails.StudyID, [PullReviewOrder]+[ManageMaterials]+[SystemUpdate]+[VendorContact]+[Phone]+[email]+[Meeting] AS AdminSum, [PerformTask]+[Travel]+[Break]+[GeneralMaintenance]+[Lunch]+[Training] AS NonAdminSum, [Other]+[PTO] AS ExcludeSum, TaskTimeDetails.TktPlRvwOrder AS PullReviewOrder, [TktMngMtrls]+[IntMngMtrls] AS ManageMaterials, TaskTimeDetails.TktPrfrmTask AS PerformTask, [TktTravel]+[IntTravel] AS Travel, TaskTimeDetails.TktSysUpdt AS SystemUpdate, [TktVendCtct]+[IntVendCtct] AS VendorContact, [TktPhone]+[IntPhone] AS Phone, [TktEmail]+[IntEmail] AS Email, TaskTimeDetails.IntBreak AS Break, TaskTimeDetails.IntGenMaint AS GeneralMaintenance, TaskTimeDetails.IntLunch AS Lunch, TaskTimeDetails.IntMeeting AS Meeting, TaskTimeDetails.IntTraining AS Training, TaskTimeDetails.IntOther AS Other, TaskTimeDetails.IntPTO AS PTO FROM TaskTimeDetails; "Jerry Whittle" wrote: Show us the SQL. Open the queries in design view. Next go to View, SQL View and copy and past them here. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Renraf" wrote: I have an update. It looks like all the fields in Query3 that came from the CrossTab Query and were not combined with other Fields are the ones that are coming through as text. Why? "Renraf" wrote: I have three queries, each building off the previous, and by the final query, some of my number data has been converted to text data, and I need to prevent this from happening. Here is the situation: Query1: Crosstab query Input Fields: IntrrptID, PTaskID, Category, Minutes (integer) Output Fields: PTaskID, CategoryA, CategoryB, CategoryC . . . (each containing the sum of minutes for that category for that PTaskID) Query2: Input Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Category 1-m are from table PrimaryTaskLogs (integer), Category A-n are from Query1. Output Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Query3: Input Fields: (Query2) Output Fields: PTaskID, Category1, Category2+A, Category3, Category4+D, Category1+2+A+7+F+G, . . . Most of my fields that are summing other fields are coming out correctly, but one of them is coming out 540+0=5400, 0+0=00, 0+10=010, etc., so clearly it thinks the data is text. Why would this happen? I start with integer data, and all I do with that data is crosstab it (uses a sum), sum some of it, and just shuffle other data into the next query with an occassional Field rename. I am baffled. I know I can fix it by using CInt along the way, but I would really like to know why this is happening in the first place, and if there is a better way for me to prevent it. |
#6
|
|||
|
|||
Prevent query from changing number values to text values
NZ in a query will often force the value that is returned to be a string. It
seems to be that the query cannot resolve the data type and therefore goes with the safe datatype of string. Your first fix will work to overcome the problem IntField1: CInt(Nz([IntDetails]![Field1],0)) Your second fix will (in all probability) not fix the problem. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Renraf wrote: In the process of copying the SQL, I remembered that I was using this definition of my Fields in the second query for all of the values from the Crosstab: IntField1: Nz([IntDetails]![Field1],0) I can only assume that was the problem. Do you know why the 0 is interpreted as a string instead of a numerical value? Shouldn't it be "0" to be considered a string? And how would I force it to be a value? Anyway, I replaced the code above with the following, and it works correctly now: IntField1: CInt(Nz([IntDetails]![Field1],0)) Should I do this instead? : IntField1: Nz([IntDetails]![Field1],CInt(0)) And here's the (corrected) SQL, in case my withholding information is aggravating: IntDetails (query1) TRANSFORM Sum(Interruptions.Minutes) AS SumOfMinutes SELECT Interruptions.PTaskID, Count(Interruptions.IntrrptID) AS IntCount, Sum(Interruptions.Minutes) AS IntMinSum FROM (DailyLogs INNER JOIN PrimaryTaskLogs ON DailyLogs.LogID = PrimaryTaskLogs.LogID) INNER JOIN (NonTicketCategories INNER JOIN Interruptions ON NonTicketCategories.CategoryID = Interruptions.Category) ON PrimaryTaskLogs.PTaskID = Interruptions.PTaskID WHERE (((DailyLogs.InvalidData)=0)) GROUP BY Interruptions.PTaskID PIVOT NonTicketCategories.CategoryName; TaskTimeDetails (query2) SELECT PrimaryTaskLogs.PTaskID, DailyLogs.Date, PrimaryTaskLogs.LogID, DailyLogs.StudyID, PrimaryTaskLogs.Category AS TaskCtgry, PrimaryTaskLogs.Start, PrimaryTaskLogs.Stop, PrimaryTaskLogs.Overnight, DateDiff("n",[Start],[Stop])-[Overnight]*1440-[TktMinSum]-[IntMinSum] AS TimeDiff, [TktPlRvwOrder]+[TktMngMtrls]+[TktPrfrmTask]+[TktTravel]+[TktSysUpdt]+[TktVendCtct]+[TktPhone]+[TktEmail] AS TktMinSum, Nz(IntDetails!IntMinSum,0) AS IntMinSum, Nz(IntDetails!IntCount,0) AS IntCount, PrimaryTaskLogs.PullReviewOrder AS TktPlRvwOrder, PrimaryTaskLogs.ManageMaterials AS TktMngMtrls, PrimaryTaskLogs.PerformTask AS TktPrfrmTask, PrimaryTaskLogs.Travel AS TktTravel, PrimaryTaskLogs.SystemUpdate AS TktSysUpdt, PrimaryTaskLogs.VendorContact AS TktVendCtct, PrimaryTaskLogs.Phone AS TktPhone, PrimaryTaskLogs.Email AS TktEmail, CInt(Nz(IntDetails!Break,0)) AS IntBreak, CInt(Nz(IntDetails!Email,0)) AS IntEmail, CInt(Nz(IntDetails![General Maintenance],0)) AS IntGenMaint, CInt(Nz(IntDetails!Lunch,0)) AS IntLunch, CInt(Nz(IntDetails![Manage Materials],0)) AS IntMngMtrls, CInt(Nz(IntDetails!Meeting,0)) AS IntMeeting, CInt(Nz(IntDetails!Other,0)) AS IntOther, CInt(Nz(IntDetails!Phone,0)) AS IntPhone, CInt(Nz(IntDetails!PTO,0)) AS IntPTO, CInt(Nz(IntDetails!Training,0)) AS IntTraining, CInt(Nz(IntDetails!Travel,0)) AS IntTravel, CInt(Nz(IntDetails![Vendor Contact],0)) AS IntVendCtct FROM Studies INNER JOIN (DailyLogs INNER JOIN (PrimaryTaskLogs LEFT JOIN IntDetails ON PrimaryTaskLogs.PTaskID=IntDetails.PTaskID) ON DailyLogs.LogID=PrimaryTaskLogs.LogID) ON Studies.StudyID=DailyLogs.StudyID WHERE (((DailyLogs.InvalidData)=0)); AdminDetails (query3): SELECT TaskTimeDetails.PTaskID, TaskTimeDetails.Date, TaskTimeDetails.LogID, TaskTimeDetails.StudyID, [PullReviewOrder]+[ManageMaterials]+[SystemUpdate]+[VendorContact]+[Phone]+[email]+[Meeting] AS AdminSum, [PerformTask]+[Travel]+[Break]+[GeneralMaintenance]+[Lunch]+[Training] AS NonAdminSum, [Other]+[PTO] AS ExcludeSum, TaskTimeDetails.TktPlRvwOrder AS PullReviewOrder, [TktMngMtrls]+[IntMngMtrls] AS ManageMaterials, TaskTimeDetails.TktPrfrmTask AS PerformTask, [TktTravel]+[IntTravel] AS Travel, TaskTimeDetails.TktSysUpdt AS SystemUpdate, [TktVendCtct]+[IntVendCtct] AS VendorContact, [TktPhone]+[IntPhone] AS Phone, [TktEmail]+[IntEmail] AS Email, TaskTimeDetails.IntBreak AS Break, TaskTimeDetails.IntGenMaint AS GeneralMaintenance, TaskTimeDetails.IntLunch AS Lunch, TaskTimeDetails.IntMeeting AS Meeting, TaskTimeDetails.IntTraining AS Training, TaskTimeDetails.IntOther AS Other, TaskTimeDetails.IntPTO AS PTO FROM TaskTimeDetails; "Jerry Whittle" wrote: Show us the SQL. Open the queries in design view. Next go to View, SQL View and copy and past them here. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Renraf" wrote: I have an update. It looks like all the fields in Query3 that came from the CrossTab Query and were not combined with other Fields are the ones that are coming through as text. Why? "Renraf" wrote: I have three queries, each building off the previous, and by the final query, some of my number data has been converted to text data, and I need to prevent this from happening. Here is the situation: Query1: Crosstab query Input Fields: IntrrptID, PTaskID, Category, Minutes (integer) Output Fields: PTaskID, CategoryA, CategoryB, CategoryC . . . (each containing the sum of minutes for that category for that PTaskID) Query2: Input Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Category 1-m are from table PrimaryTaskLogs (integer), Category A-n are from Query1. Output Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Query3: Input Fields: (Query2) Output Fields: PTaskID, Category1, Category2+A, Category3, Category4+D, Category1+2+A+7+F+G, . . . Most of my fields that are summing other fields are coming out correctly, but one of them is coming out 540+0=5400, 0+0=00, 0+10=010, etc., so clearly it thinks the data is text. Why would this happen? I start with integer data, and all I do with that data is crosstab it (uses a sum), sum some of it, and just shuffle other data into the next query with an occassional Field rename. I am baffled. I know I can fix it by using CInt along the way, but I would really like to know why this is happening in the first place, and if there is a better way for me to prevent it. |
#7
|
|||
|
|||
Prevent query from changing number values to text values
That is strange. I was hoping for something simple like a Format(), which
will turn numbers to strings, in you SQL but I don't see anything obviously wrong. NZ() returns a Variant which can be interpeted as many different datatypes. I guess in this case Access is choosing badly. I recommend testing your solution below. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Renraf" wrote: In the process of copying the SQL, I remembered that I was using this definition of my Fields in the second query for all of the values from the Crosstab: IntField1: Nz([IntDetails]![Field1],0) I can only assume that was the problem. Do you know why the 0 is interpreted as a string instead of a numerical value? Shouldn't it be "0" to be considered a string? And how would I force it to be a value? Anyway, I replaced the code above with the following, and it works correctly now: IntField1: CInt(Nz([IntDetails]![Field1],0)) Should I do this instead? : IntField1: Nz([IntDetails]![Field1],CInt(0)) And here's the (corrected) SQL, in case my withholding information is aggravating: IntDetails (query1) TRANSFORM Sum(Interruptions.Minutes) AS SumOfMinutes SELECT Interruptions.PTaskID, Count(Interruptions.IntrrptID) AS IntCount, Sum(Interruptions.Minutes) AS IntMinSum FROM (DailyLogs INNER JOIN PrimaryTaskLogs ON DailyLogs.LogID = PrimaryTaskLogs.LogID) INNER JOIN (NonTicketCategories INNER JOIN Interruptions ON NonTicketCategories.CategoryID = Interruptions.Category) ON PrimaryTaskLogs.PTaskID = Interruptions.PTaskID WHERE (((DailyLogs.InvalidData)=0)) GROUP BY Interruptions.PTaskID PIVOT NonTicketCategories.CategoryName; TaskTimeDetails (query2) SELECT PrimaryTaskLogs.PTaskID, DailyLogs.Date, PrimaryTaskLogs.LogID, DailyLogs.StudyID, PrimaryTaskLogs.Category AS TaskCtgry, PrimaryTaskLogs.Start, PrimaryTaskLogs.Stop, PrimaryTaskLogs.Overnight, DateDiff("n",[Start],[Stop])-[Overnight]*1440-[TktMinSum]-[IntMinSum] AS TimeDiff, [TktPlRvwOrder]+[TktMngMtrls]+[TktPrfrmTask]+[TktTravel]+[TktSysUpdt]+[TktVendCtct]+[TktPhone]+[TktEmail] AS TktMinSum, Nz(IntDetails!IntMinSum,0) AS IntMinSum, Nz(IntDetails!IntCount,0) AS IntCount, PrimaryTaskLogs.PullReviewOrder AS TktPlRvwOrder, PrimaryTaskLogs.ManageMaterials AS TktMngMtrls, PrimaryTaskLogs.PerformTask AS TktPrfrmTask, PrimaryTaskLogs.Travel AS TktTravel, PrimaryTaskLogs.SystemUpdate AS TktSysUpdt, PrimaryTaskLogs.VendorContact AS TktVendCtct, PrimaryTaskLogs.Phone AS TktPhone, PrimaryTaskLogs.Email AS TktEmail, CInt(Nz(IntDetails!Break,0)) AS IntBreak, CInt(Nz(IntDetails!Email,0)) AS IntEmail, CInt(Nz(IntDetails![General Maintenance],0)) AS IntGenMaint, CInt(Nz(IntDetails!Lunch,0)) AS IntLunch, CInt(Nz(IntDetails![Manage Materials],0)) AS IntMngMtrls, CInt(Nz(IntDetails!Meeting,0)) AS IntMeeting, CInt(Nz(IntDetails!Other,0)) AS IntOther, CInt(Nz(IntDetails!Phone,0)) AS IntPhone, CInt(Nz(IntDetails!PTO,0)) AS IntPTO, CInt(Nz(IntDetails!Training,0)) AS IntTraining, CInt(Nz(IntDetails!Travel,0)) AS IntTravel, CInt(Nz(IntDetails![Vendor Contact],0)) AS IntVendCtct FROM Studies INNER JOIN (DailyLogs INNER JOIN (PrimaryTaskLogs LEFT JOIN IntDetails ON PrimaryTaskLogs.PTaskID=IntDetails.PTaskID) ON DailyLogs.LogID=PrimaryTaskLogs.LogID) ON Studies.StudyID=DailyLogs.StudyID WHERE (((DailyLogs.InvalidData)=0)); AdminDetails (query3): SELECT TaskTimeDetails.PTaskID, TaskTimeDetails.Date, TaskTimeDetails.LogID, TaskTimeDetails.StudyID, [PullReviewOrder]+[ManageMaterials]+[SystemUpdate]+[VendorContact]+[Phone]+[email]+[Meeting] AS AdminSum, [PerformTask]+[Travel]+[Break]+[GeneralMaintenance]+[Lunch]+[Training] AS NonAdminSum, [Other]+[PTO] AS ExcludeSum, TaskTimeDetails.TktPlRvwOrder AS PullReviewOrder, [TktMngMtrls]+[IntMngMtrls] AS ManageMaterials, TaskTimeDetails.TktPrfrmTask AS PerformTask, [TktTravel]+[IntTravel] AS Travel, TaskTimeDetails.TktSysUpdt AS SystemUpdate, [TktVendCtct]+[IntVendCtct] AS VendorContact, [TktPhone]+[IntPhone] AS Phone, [TktEmail]+[IntEmail] AS Email, TaskTimeDetails.IntBreak AS Break, TaskTimeDetails.IntGenMaint AS GeneralMaintenance, TaskTimeDetails.IntLunch AS Lunch, TaskTimeDetails.IntMeeting AS Meeting, TaskTimeDetails.IntTraining AS Training, TaskTimeDetails.IntOther AS Other, TaskTimeDetails.IntPTO AS PTO FROM TaskTimeDetails; "Jerry Whittle" wrote: Show us the SQL. Open the queries in design view. Next go to View, SQL View and copy and past them here. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Renraf" wrote: I have an update. It looks like all the fields in Query3 that came from the CrossTab Query and were not combined with other Fields are the ones that are coming through as text. Why? "Renraf" wrote: I have three queries, each building off the previous, and by the final query, some of my number data has been converted to text data, and I need to prevent this from happening. Here is the situation: Query1: Crosstab query Input Fields: IntrrptID, PTaskID, Category, Minutes (integer) Output Fields: PTaskID, CategoryA, CategoryB, CategoryC . . . (each containing the sum of minutes for that category for that PTaskID) Query2: Input Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Category 1-m are from table PrimaryTaskLogs (integer), Category A-n are from Query1. Output Fields: PTaskID, Category1, Category2, Category3, . . . , CategoryA, CategoryB, CategoryC, . . . Query3: Input Fields: (Query2) Output Fields: PTaskID, Category1, Category2+A, Category3, Category4+D, Category1+2+A+7+F+G, . . . Most of my fields that are summing other fields are coming out correctly, but one of them is coming out 540+0=5400, 0+0=00, 0+10=010, etc., so clearly it thinks the data is text. Why would this happen? I start with integer data, and all I do with that data is crosstab it (uses a sum), sum some of it, and just shuffle other data into the next query with an occassional Field rename. I am baffled. I know I can fix it by using CInt along the way, but I would really like to know why this is happening in the first place, and if there is a better way for me to prevent it. |
Thread Tools | |
Display Modes | |
|
|