Text to ascii values change in summary query

[debert254]

I'm running a simple summary query. Prior to summarizing values in three columns I get the text values (i.e., "Medicare") for column 1. If I summarize I get "䄂˜" in place of the original text values.

Using Access 2003, SP2 under WinXP

The query:
SELECT Qtr_Report_In_County01.Memo, Qtr_Report_In_County01.Program, Qtr_Report_In_County01.Units
FROM Qtr_Report_In_County01
GROUP BY Qtr_Report_In_County01.Memo, Qtr_Report_In_County01.Program, Qtr_Report_In_County01.Units;

Sample rows being summarized:
Memo Program Units
Medicaid Crisis Intervention 2
1st/3rd Party CSP - Child 10


I'd work-around by taking the data out to excel, but my source is > 88k rows and over the 65k limit.

tia for any leads.

Dave Ebert

[willakawill]

I'm running a simple summary query. Prior to summarizing values in three columns I get the text values (i.e., "Medicare") for column 1. If I summarize I get "䄂˜" in place of the original text values.

Using Access 2003, SP2 under WinXP

The query:
SELECT Qtr_Report_In_County01.Memo, Qtr_Report_In_County01.Program, Qtr_Report_In_County01.Units
FROM Qtr_Report_In_County01
GROUP BY Qtr_Report_In_County01.Memo, Qtr_Report_In_County01.Program, Qtr_Report_In_County01.Units;

Sample rows being summarized:
Memo Program Units
Medicaid Crisis Intervention 2
1st/3rd Party CSP - Child 10


I'd work-around by taking the data out to excel, but my source is > 88k rows and over the 65k limit.

tia for any leads.

Dave Ebert

Hi Dave
Try this
SELECT Qtr_Report_In_County01.Memo, Qtr_Report_In_County01.Program, SUM(Qtr_Report_In_County01.Units)
FROM Qtr_Report_In_County01
GROUP BY Qtr_Report_In_County01.Memo, Qtr_Report_In_County01.Program;

[MMcCarthy]

Hi Dave

You've called your first field memo. Is it by any chance a memo data type. They often seem to cause problems in queries.

Other than that the main reason that ascii values get returned is because there are characters like carriage returns in the text. If you're not sure take some sample text out to notepad with the word wrap switched off it should all just appear as one line. If not then you will need to remove the line down characters. Find and replace should do it.

[debert254]

Thanks, it turns out the field was indeed a MEMO type. Changing it to text fixed my problem.

Thanks again.

Hi Dave

You've called your first field memo. Is it by any chance a memo data type. They often seem to cause problems in queries.

Other than that the main reason that ascii values get returned is because there are characters like carriage returns in the text. If you're not sure take some sample text out to notepad with the word wrap switched off it should all just appear as one line. If not then you will need to remove the line down characters. Find and replace should do it.

[MMcCarthy]

Thanks, it turns out the field was indeed a MEMO type. Changing it to text fixed my problem.

Thanks again.

You're welcome.