ACCESS FEED: Re: [MS_AccessPros] Strange Character Problem

Jumat, 07 Maret 2014

Re: [MS_AccessPros] Strange Character Problem

Jim-

When you GROUP BY or DISTINCT a query with a memo field, strange things happen. At a minimum, the memo field gets truncated to 255 characters. Other than to remove duplicates, I can see no reason why this is a Totals query.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 7, 2014, at 11:30 PM, Jim Wagner <luvmymelody@yahoo.com> wrote:

I have a query that makes a table from various tables and a query. It has been working for years and all of a sudden it has stopped working. I have a backup from 3 days ago that works perfectly. It is very strange that it worked and then it stopped. Is there something that I can fix quickly?

Thank You
Jim Wagner

The issue is that the query makes a table that has strange characters like 祥), 益). The field is a memo data type. The SQL is below

SELECT [LEAVE UPDATE TABLE].[Person Id], [EMPLOYEES WITH LEAVE ACTION].LASTNAME, [LEAVE UPDATE TABLE].[TYPE OF LEAVE], [EMPLOYEES WITH LEAVE ACTION].FIRSTNAME, [LEAVE UPDATE TABLE].FAMLVDTBEGIN, [LEAVE UPDATE TABLE].FAMLVDTEND, [EMPLOYEES WITH LEAVE ACTION].HR_HOME_DEPARTMENT_CODE, [LEAVE UPDATE TABLE].EXTLVDTBEGIN, [LEAVE UPDATE TABLE].EXTLVTERMDT, tblIncident.IntermittentLeave, [R&D-CURRENTEMPLOYEES].[Jobcode Ld], [R&D-CURRENTEMPLOYEES].[Dept Id], [R&D-CURRENTEMPLOYEES].[Dept Ld], tblIncident.IncidentSummary, [R&D-CURRENTEMPLOYEES].[Person Id] INTO INTERMITTENTFORABSENCEDBASE3
FROM [R&D-CURRENTEMPLOYEES] INNER JOIN ((tblIncident INNER JOIN ([EMPLOYEES WITH LEAVE ACTION] INNER JOIN [LEAVE UPDATE TABLE] ON [EMPLOYEES WITH LEAVE ACTION].[Person Id] = [LEAVE UPDATE TABLE].[Person Id]) ON tblIncident.NoOfIncident = [LEAVE UPDATE TABLE].NoOfIncident) INNER JOIN qryIntermittentLeaveMaxActionOrder1 ON ([EMPLOYEES WITH LEAVE ACTION].[Person Id] = qryIntermittentLeaveMaxActionOrder1.[Person Id]) AND ([LEAVE UPDATE TABLE].ACTIONDT = qryIntermittentLeaveMaxActionOrder1.MaxOfACTIONDT1) AND ([LEAVE UPDATE TABLE].ActionOrder = qryIntermittentLeaveMaxActionOrder1.MaxOfActionOrder)) ON [R&D-CURRENTEMPLOYEES].[Person Id] = [LEAVE UPDATE TABLE].[Person Id]
WHERE (((tblIncident.[Closed/Open])=False))
GROUP BY [LEAVE UPDATE TABLE].[Person Id], [EMPLOYEES WITH LEAVE ACTION].LASTNAME, [LEAVE UPDATE TABLE].[TYPE OF LEAVE], [EMPLOYEES WITH LEAVE ACTION].FIRSTNAME, [LEAVE UPDATE TABLE].FAMLVDTBEGIN, [LEAVE UPDATE TABLE].FAMLVDTEND, [EMPLOYEES WITH LEAVE ACTION].HR_HOME_DEPARTMENT_CODE, [LEAVE UPDATE TABLE].EXTLVDTBEGIN, [LEAVE UPDATE TABLE].EXTLVTERMDT, tblIncident.IntermittentLeave, [R&D-CURRENTEMPLOYEES].[Jobcode Ld], [R&D-CURRENTEMPLOYEES].[Dept Id], [R&D-CURRENTEMPLOYEES].[Dept Ld], tblIncident.IncidentSummary, [R&D-CURRENTEMPLOYEES].[Person Id]
HAVING ((([LEAVE UPDATE TABLE].[TYPE OF LEAVE]) Like "int*") AND ((tblIncident.IntermittentLeave)=True))
ORDER BY [EMPLOYEES WITH LEAVE ACTION].LASTNAME, [EMPLOYEES WITH LEAVE ACTION].FIRSTNAME, [LEAVE UPDATE TABLE].FAMLVDTBEGIN;

Jim Wagner