Bug? Report displaying Chinese character sometimes but not always

[AETep]

I have a report that displays results based on criteria selected in a
subform. When a field is left to the default "all" my comments field
displays correctly. However, when any value is input into my search field,
the comments displays as a Chinese character.

Any assistance would be greatly appreciated!

[Allen Browne]

There are a couple of things that can cause this behavior.

It occurs whenever Access misunderstands the data type.
Can you tell us more about this field?
Is it a memo field?
Is this a UNION query?
What kinds of JOIN?
Perhaps you could post the SQL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AETep" <AE...@discussions.microsoft.com> wrote in message
news:24A905BB-C784-478F...@microsoft.com...

[AETep]

The field in question is a memo field. It appears correctly when Activity
Category is set to All. Otherwise, it displays a Chinese character. All
other fields consistently appear correctly.

Here is the code:
Private Sub cmdViewSelectedReport_Click()
On Error GoTo Err_cmdViewSeLectedReport_Click

If Me.ActivityCategory.Value = "(All)" And Me.EmployeeName.Value <> "(All)"
Then
DoCmd.OpenReport "HRIS TIME SHEET BY DATE WITH SPECIFIC ACTIVITIES &
COMMENTS", acPreview, , "[Employeename] = '" & EmployeeName & "' And
[Dateworked] >= #" & Me.DateRangeStart.Value & "# AND [Dateworked] <= #" &
Me.DateRangeEnd.Value & "#"

ElseIf Me.ActivityCategory.Value = "(All)" And Me.EmployeeName.Value =
"(All)" Then
DoCmd.OpenReport "HRIS TIME SHEET BY DATE WITH SPECIFIC ACTIVITIES &
COMMENTS", acPreview, , "[Dateworked] >= #" & Me.DateRangeStart.Value & "#
AND [Dateworked] <= #" & Me.DateRangeEnd.Value & "#"

ElseIf Me.ActivityCategory.Value <> "(All)" And Me.EmployeeName.Value =
"(All)" Then
DoCmd.OpenReport "HRIS TIME SHEET BY DATE WITH SPECIFIC ACTIVITIES &
COMMENTS", acPreview, , "[Activity Category] = '" & Me.ActivityCategory.Value
& "' And [Dateworked] >= #" & Me.DateRangeStart.Value & "# AND [Dateworked]
<= #" & Me.DateRangeEnd.Value & "#"

Else

'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenReport "HRIS TIME SHEET BY DATE WITH SPECIFIC ACTIVITIES &
COMMENTS", acPreview, , "[Employeename] = '" & EmployeeName & "' And
[Activity Category] = '" & Me.ActivityCategory.Value & "' And [Dateworked] >=
#" & Me.DateRangeStart.Value & "# AND [Dateworked] <= #" &
Me.DateRangeEnd.Value & "#"

End If
Exit_cmdViewSeLectedReport_Click:
Exit Sub
Err_cmdViewSeLectedReport_Click:
MsgBox Err.Description
Resume Exit_cmdViewSeLectedReport_Click

End Sub

[Allen Browne]

Okay, so the problem occurs only when you filter on this field (in the
WhereCondition of OpenReport.) Presumably [Activity Category] is the memo
field.

The table that the memo field comes from - does it have a primary key? What
about any other tables in the query that feeds this report? Does adding a
primary key make a difference?

If the report's RecordSource is a query, post the SQL statement, and tell us
the data type of the fields involved in the JOINs and criteria.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AETep" <AE...@discussions.microsoft.com> wrote in message

news:E2A20F7B-882A-423D...@microsoft.com...

[AETep]

There are two tables combined together in a query. Table 1 is "Time Card
Hours" where the comments field (memo field) is contained. This table did
not have a unique identified. I added one and then added this field to the
table. I ran the report filtering on an Activity Category. The primary key
field appeared fine and the comments field continued to appear with a Chinese
Character. When I do not filter the field appears correctly in English.
Table 2 is called "Activities" and contains a listing of Activity Names and
Activity Categories.
The query "qryActivity&TimeRecorded" has the following SQL:
SELECT [Time Card Hours].EmployeeName, [Time Card Hours].DateWorked,
WeekdayName(DatePart("w",[DateWorked])) AS DayWorked, Activities.[Activity
Category], [Time Card Hours].[Activity Name], Sum([Time Card Hours].[#
Hours]) AS [TotalHrs Per Day], IIf([TotalHrs Per Day]<8," Incomplete
","Complete") AS [Incomplete Time], [Time Card Hours].[Additional Comments],
[Time Card Hours].Organization, [Time Card Hours].[SR #]
FROM [Time Card Hours] LEFT JOIN Activities ON [Time Card Hours].[Activity
Name] = Activities.[Activity Name]
GROUP BY [Time Card Hours].EmployeeName, [Time Card Hours].DateWorked,
WeekdayName(DatePart("w",[DateWorked])), Activities.[Activity Category],
[Time Card Hours].[Activity Name], [Time Card Hours].[Additional Comments],
[Time Card Hours].Organization, [Time Card Hours].[SR #]
ORDER BY [Time Card Hours].DateWorked;

The form "View Time Report with Comments by Name and Date" contains 4
unbound fields to filter the report: two dates (start and end), employee
name and activity category. The row source for Activity Category is as
follows:
SELECT DISTINCT Activities.[Activity Category] From Activities Union Select
"(All)" FROM Activities;

I have tried modifying many things and every time the Activity Category is
filtered I receive a Chinese Character instead. All other fields unique to
the same table as the Comments field appear correctly.

[Allen Browne]

Okay, grouping by the memo in combination with the outer join is probably
enough to get Access confused about this field.

Firstly, do you need a Memo field here? If you could convert it to a Text
field in the table, and live with no more than 255 characters, I would
expect that would solve the problem.

If that's not practical, we will need to redesign the query in some way.
Let's try working on just the query for now. You should be able to see the
same problem if you open the query in design view, and type something into
the Criteria row under the [Activity Category] field. Now change the Total
row under this field to Where instead of Group By. Then add the memo field
to the grid a 2nd time, choosing First in the Total row this time. Verify
that this gives the results you want.

If that works okay, you can begin exploring other options. The field in the
query will now be called something like FirstOfActivityCategory. You could
therefore try changing the code that opens your report to use this name
instead of the [Activity Category]. Or, you may be able to put a reference
to the form in the Critieria row under the Where instance, e.g.:
[Forms].[Form1].[ActivityCategory]

A different approach would be to explicitly force the value from the memo to
be a string, replacing:
Activities.[Activity Category]
in the query SQL with:
Nz(Activities.[Activity Category], "") AS ActivityCategoryString

Hopefully one of those options will help you work around the fact that
Access is misinterpreting your memo field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AETep" <AE...@discussions.microsoft.com> wrote in message

news:6B17ED3C-9EEC-43B6...@microsoft.com...

[AETep]

Allen - You are a genius!!!!!
I changed the field from memo to text and it works perfectly!
Thank you:)