> 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" <AETep[ at ]discussions.microsoft.com> wrote in message
> news:6B17ED3C-9EEC-43B6-99A6-9964C73D2E10[ at ]microsoft.com...
> > 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" wrote:
> >
> >> 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.
> >>
> >> "AETep" <AETep[ at ]discussions.microsoft.com> wrote in message
> >> news:E2A20F7B-882A-423D-8DAF-0B77E021D535[ at ]microsoft.com...
> >> > 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" wrote:
> >> >
> >> >> 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.
> >> >>
> >> >> "AETep" <AETep[ at ]discussions.microsoft.com> wrote in message
> >> >> news:24A905BB-C784-478F-92B3-856066032E5D[ at ]microsoft.com...
> >> >> >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.
>
>
>