Filtering for "Is Null" and date data

  • Thread starter Thread starter CSDunn
  • Start date Start date
C

CSDunn

Hello,
I have a problem with field filtering between an Access 2000 Project form
(the application is called CELDT), and the report that shows the results of
the filter. Both the form and the report are based on the same View
addressed in the Record Source of both as 'dbo.CLMain_vw'. The View resides
in a SQL Server 2000 database. There are two different problems I am having
with filtering: The first involves filtering in the form by date fields, the
other occurs when I try to open a report to show the results of filtering on
fields in the form where the SQL Server datatype is 'nvarchar', and the
criteria filtered in the form 'IS NULL'.

In the form, I can filter by any field except a field where the field data
source is 'smalldatetime'. When I attempt to preform any filter on such a
field, I get the following message: "Cannot apply Filter on one or more
fields specified in the Filter property". After clicking 'OK', the filter
does not occur, I am retunred back to the form, and the field I attempted to
set up the filter in has focus.

The other filtering problem occurs when I try to filter for 'IS NULL' in the
form with fields of the 'nvarchar' data type. When I attempt to filter this
way on such a field, the form does its filter and shows the filtered
records, but the problem occurs when I try to view a report that shows the
results of the filter.

The way I handle the filtered data in the report is like this: After the
filter takes place in the form, the user clicks a button that runs the
following macro:

*********************
OpenReport (rptMainFilteredSummary, Print Preview, no Filter Name, no Where
Condition)
.... SetValue (Item: [Reports]![rptMainFilteredSummary].[Filter])
(Expression: [Forms]![frmMain].[Filter])
.... Maximize
*********************
The value of the form Filter property that gets passed to the report Filter
property when filtering for 'IS NULL' in the form looks something like this:
((dbo.CLMain_vw.Status Is Null)). The 'Filter On' property in the report is
set to 'Yes'.

The Filter property syntax works fine for the Filter property in the form,
but when I try to use this same syntax directly in the Filter property of
the report, or when I click the button that runs the macro to open the
report and display the results of the filter, I get the following message
when the report tries to open:
"CELDT can't find the object 'CELDT can't find the object 'dbo.CLMain_vw.'.'
*You misspelled the object name. Check for missing underscores (_) or other
punctuation, and make sure you didnt' enter leading spaces.
*You tried to open a linked table, but the file containing the table isn't
on the path you specified. Use the Linked Table Manager to update the link
and point to the correct path."

Only this message shows up when the report opens (no data shows up in the
report), and when I click 'OK' on the message, the report closes, and I am
returned back to the form. This filter problem only occurs when my form
filter criteria for the 'nvarchar' fields is 'IS NULL'. It does not happen
when the criteria for these fields is actual data, or when the criteria is
expressed as 'IS NOT NULL'.

How can I overcome these problems?

Thanks for your help!

CSDunn
 
Hi,

Filtering in Access is really a problem it seems when you use ADPs. I had
this problem is an application and I decided to resolve it by manually doing
the filters. I give the user a list of valid filters (with combo box ou
buttons, depending what I want them to have access) and then I modify the
record source with a adhoc query. Example:

SELECT CASE combo1
CASE "CreationDate"
strWhere = "WHERE CreationDate = '" & Me.txtCreationDate & "'"
'will take the current row. (Be careful with dates, they must be in
a format understandable by SQL Server. I always convert them in YYYYMMDD to
be sure the Windows parameters of the client machine don't interfere.)
CASE "Client"
strWhere = "WHERE Client = '" & Me.txtClientName & "'"
ELSE
strWhere = ""
END SELECT

Me.Recordsource = "SELECT * FROM dbo.CLMain_vw " & strWhere

I did the same with sorting: I'm adding the ORDER BY clause depending on
what the user has selected.

Hope this can help you.

Karen

CSDunn said:
Hello,
I have a problem with field filtering between an Access 2000 Project form
(the application is called CELDT), and the report that shows the results of
the filter. Both the form and the report are based on the same View
addressed in the Record Source of both as 'dbo.CLMain_vw'. The View resides
in a SQL Server 2000 database. There are two different problems I am having
with filtering: The first involves filtering in the form by date fields, the
other occurs when I try to open a report to show the results of filtering on
fields in the form where the SQL Server datatype is 'nvarchar', and the
criteria filtered in the form 'IS NULL'.

In the form, I can filter by any field except a field where the field data
source is 'smalldatetime'. When I attempt to preform any filter on such a
field, I get the following message: "Cannot apply Filter on one or more
fields specified in the Filter property". After clicking 'OK', the filter
does not occur, I am retunred back to the form, and the field I attempted to
set up the filter in has focus.

The other filtering problem occurs when I try to filter for 'IS NULL' in the
form with fields of the 'nvarchar' data type. When I attempt to filter this
way on such a field, the form does its filter and shows the filtered
records, but the problem occurs when I try to view a report that shows the
results of the filter.

The way I handle the filtered data in the report is like this: After the
filter takes place in the form, the user clicks a button that runs the
following macro:

*********************
OpenReport (rptMainFilteredSummary, Print Preview, no Filter Name, no Where
Condition)
... SetValue (Item: [Reports]![rptMainFilteredSummary].[Filter])
(Expression: [Forms]![frmMain].[Filter])
... Maximize
*********************
The value of the form Filter property that gets passed to the report Filter
property when filtering for 'IS NULL' in the form looks something like this:
((dbo.CLMain_vw.Status Is Null)). The 'Filter On' property in the report is
set to 'Yes'.

The Filter property syntax works fine for the Filter property in the form,
but when I try to use this same syntax directly in the Filter property of
the report, or when I click the button that runs the macro to open the
report and display the results of the filter, I get the following message
when the report tries to open:
"CELDT can't find the object 'CELDT can't find the object 'dbo.CLMain_vw.'.'
*You misspelled the object name. Check for missing underscores (_) or other
punctuation, and make sure you didnt' enter leading spaces.
*You tried to open a linked table, but the file containing the table isn't
on the path you specified. Use the Linked Table Manager to update the link
and point to the correct path."

Only this message shows up when the report opens (no data shows up in the
report), and when I click 'OK' on the message, the report closes, and I am
returned back to the form. This filter problem only occurs when my form
filter criteria for the 'nvarchar' fields is 'IS NULL'. It does not happen
when the criteria for these fields is actual data, or when the criteria is
expressed as 'IS NOT NULL'.

How can I overcome these problems?

Thanks for your help!

CSDunn
Click to expand...
 
Back
Top