data gets truncated

[SAm]

hi

i have a field which stores email addresses in a memo field. i have one
query which shows the full length of the field. then i have a another query
which truncates part of the field. the catch is that the join i use in the
second query is unusual join it uses a between join. i will post the second
query. is there a way to somehow with some minipulation to stop it from
truncating my field. i was told maybe to minipulate it with unicode, strconv.
i am not sure. please help.

here is the query:

SELECT WeeklyOTAll1Qry.Facility, WeeklyOTAll1Qry.FullName,
WeeklyOTAll1Qry.DeptName, WeeklyOTAll1Qry.EmailAddressTo,
WeeklyOTAll1Qry.EmailAddressCC, WeeklyOTAll2Qry.WeekEndDate,
Sum(nz(WeeklyOTAll1Qry.TotalHours)) AS TotalHours,
Sum(nz(WeeklyOTAll1Qry.RegHours)) AS RegularHours,
Sum(nz(WeeklyOTAll1Qry.OTHours)) AS OTHours,
Sum(nz(AgencyDeTransposeQry.AgencyHours)) AS AgencyHours
FROM AgencyDeTransposeQry RIGHT JOIN (WeeklyOTAll2Qry INNER JOIN
WeeklyOTAll1Qry ON
(WeeklyOTAll2Qry.FacilityInitials=WeeklyOTAll1Qry.Facility) AND
(WeeklyOTAll1Qry.RecDate Between WeeklyOTAll2Qry.WeekStartDate And
WeeklyOTAll2Qry.WeekEndDate)) ON
(AgencyDeTransposeQry.FacilityName=WeeklyOTAll1Qry.Facility) AND
(AgencyDeTransposeQry.RecordDate=WeeklyOTAll1Qry.RecDate) AND
(AgencyDeTransposeQry.Department=WeeklyOTAll1Qry.DeptName)
GROUP BY WeeklyOTAll1Qry.Facility, WeeklyOTAll1Qry.FullName,
WeeklyOTAll1Qry.DeptName, WeeklyOTAll1Qry.EmailAddressTo,
WeeklyOTAll1Qry.EmailAddressCC, WeeklyOTAll2Qry.WeekEndDate;


the field WeeklyOTAll1Qry.EmailAddressCC gets truncated.

thanks,

sam

 

[Allen Browne]

You have the field EmailAddressCC in your GROUP BY clause.
This will cause it to truncate.

Could you use First, instead of Group By in the Total row under this field
in query design?

More info in:
Truncation of Memo fields
at:
http://allenbrowne.com/ser-63.html
(Your's is the first issue listed - aggregation.)

 

[SAm]

thanks for the quick reply. yeah, it makes sense as well, i don't know why i
overlooked that issue. i will rearrange my queries so that it doesn't
aggregate the memo field.

sam