Crosstab query headers are duplicated

[wvandenberg]

Hello,

I have a crosstab that uses a date field [StartDate] as the column heading. The query displays the correct values in each column. However, sometimes [StartDate] contains the time and sometimes it does not. This is causing two separate columns, one for just the date and one for the same date with the time. I tried using Format([StartDate],"dd/mmm/yyyy") as the column heading but this did not work, both columns are just displayed without the time.

Is there any way to combine these duplicate columns and have the query "ignore" the time if it is included with the date? Any suggestions are appreciated.

Thanks,
Wendy

 

[Remou]

Please post your SQL.

 

[wvandenberg]

TRANSFORM Max(IIf([GroupName]="Toxicity",IIf([Symbol]=">",Replace([ResultValue],"-",[Symbol],1,1),[ResultValue]),IIf([fkQualifierCode]="X","-" & [DetectionLimit],[ResultValue]))) AS Result
SELECT [_qselExpResultsSorted].GroupOrder, [_qselExpResultsSorted].AnalyteOrder, [_qselExpResultsSorted].GroupName, [_qselExpResultsSorted].P_Group, [_qselExpResultsSorted].AnalyteName, [_qselExpResultsSorted].P_Analyte, IIf([P_Group],[P_Group],[GroupName]) AS [Group], IIf([P_Analyte],[P_Analyte],[AnalyteName]) AS Parameter, [_qselExpResultsSorted].Units
FROM _qselExpResultsSorted
GROUP BY [_qselExpResultsSorted].GroupOrder, [_qselExpResultsSorted].AnalyteOrder, [_qselExpResultsSorted].pkGroupID, [_qselExpResultsSorted].GroupName, [_qselExpResultsSorted].P_Group, [_qselExpResultsSorted].pkAnalyteID, [_qselExpResultsSorted].AnalyteName, [_qselExpResultsSorted].P_Analyte, IIf([P_Group],[P_Group],[GroupName]), IIf([P_Analyte],[P_Analyte],[AnalyteName]), [_qselExpResultsSorted].Units
ORDER BY [_qselExpResultsSorted].GroupOrder, [_qselExpResultsSorted].AnalyteOrder, [_qselExpResultsSorted].GroupName, [_qselExpResultsSorted].AnalyteName
PIVOT "Site:" & [SiteName] & " " & "Sample:" & [SampleName] & " " & "Date:" & Format([StartDate],"dd/mmm/yyyy") & " " & "Task:" & Right([ProjectNumber],4);

 

[dhookom]

I would use DateValue() rather than formatting.

TRANSFORM Max(IIf([GroupName]="Toxicity",IIf([Symbol]=">",Replace([ResultValue],"-",[Symbol],1,1),[ResultValue]),IIf([fkQualifierCode]="X","-" & [DetectionLimit],[ResultValue]))) AS Result
SELECT [_qselExpResultsSorted].GroupOrder, [_qselExpResultsSorted].AnalyteOrder, [_qselExpResultsSorted].GroupName, [_qselExpResultsSorted].P_Group, [_qselExpResultsSorted].AnalyteName, [_qselExpResultsSorted].P_Analyte, IIf([P_Group],[P_Group],[GroupName]) AS [Group], IIf([P_Analyte],[P_Analyte],[AnalyteName]) AS Parameter, [_qselExpResultsSorted].Units
FROM _qselExpResultsSorted
GROUP BY [_qselExpResultsSorted].GroupOrder, [_qselExpResultsSorted].AnalyteOrder, [_qselExpResultsSorted].pkGroupID, [_qselExpResultsSorted].GroupName, [_qselExpResultsSorted].P_Group, [_qselExpResultsSorted].pkAnalyteID, [_qselExpResultsSorted].AnalyteName, [_qselExpResultsSorted].P_Analyte, IIf([P_Group],[P_Group],[GroupName]), IIf([P_Analyte],[P_Analyte],[AnalyteName]), [_qselExpResultsSorted].Units
ORDER BY [_qselExpResultsSorted].GroupOrder, [_qselExpResultsSorted].AnalyteOrder, [_qselExpResultsSorted].GroupName, [_qselExpResultsSorted].AnalyteName
PIVOT "Site:" & [SiteName] & " " & "Sample:" & [SampleName] & " " & "Date:" & DateValue([StartDate]) & " " & "Task:" & Right([ProjectNumber],4);

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

I wonder if the doubling up of columns could be coming from somewhere else? Have you tried setting pivot to just the formatted date? ...

PIVOT Format([StartDate],"dd/mmm/yyyy")

 

[dhookom]

There is also a limit to the number of characters in the generated column heading. It might be 64 characters.

Duane
Hook'D on Access
MS Access MVP

 

[wvandenberg]

Sorry for my slow response. Thanks for everyone's suggestions. Remou, after changing the PIVOT, I found that

 "Sample:" & [SampleName]

was causing the problem.

Thanks