Any assistance would be greatly appreciated!
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...
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
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...
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.
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...