Prevent query from changing number values to text values

[Renraf]

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.

 

[Renraf]

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?

 

[John Spencer]

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

 

[Jerry Whittle]

Show us the SQL. Open the queries in design view. Next go to View, SQL View
and copy and past them here.

 

[Renraf]

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]++[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;

 

[John Spencer]

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

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]++[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;


[QUOTE="Jerry Whittle"]
Show us the SQL. Open the queries in design view. Next go to View, SQL View
and copy and past them here.[/QUOTE][/QUOTE]

 

[Jerry Whittle]

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.

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]++[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;


[QUOTE="Jerry Whittle"]
Show us the SQL. Open the queries in design view. Next go to View, SQL View
and copy and past them here.[/QUOTE][/QUOTE]