ACCESS FEED: Re: [MS_AccessPros] Strange Character Problem

Senin, 10 Maret 2014

Re: [MS_AccessPros] Strange Character Problem

John,

Sorry it took so long to reply. Our network drives disconnected and it took most of the morning to get them to come back.

I looked at the query and I took off the totals and compared the count of each with the totals an without. With the totals there is 16 records. Without the totals 107. We surely need the totals. is there another way to get this to work?

Thank You

Jim Wagner

On Saturday, March 8, 2014 12:13 AM, John Viescas <JohnV@msn.com> wrote:

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