473,220 Members | 2,145 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,220 software developers and data experts.

Problem with 'Filter By Form'

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
Nov 12 '05 #1
0 6425

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Robert Neville | last post by:
I would like to add filter functionality to my database whether through the Main form or the subform. This question may be rudimentary, yet I have not less experience with filtering data outside...
10
by: DataBard007 | last post by:
Hello Access Gurus: I use Win98SE and Access97. I just built a simple Access97 application which holds all contact information for my personal contacts, such as first name, last name, address,...
3
by: dixie | last post by:
I have an Access 2000 form which has been running OK for a long time. Lately, it is playing up and doing things like causing Access to close suddenly when the form is opened or when saving the form...
4
by: hadardo | last post by:
Hello all. Let me start by saying that I don't use VBA so often, so I ask for your forgivness if my questions is somewhat silly. I'm trying to find if a user typed the same value in two or more...
8
by: jesmi | last post by:
my code is: addRecord.cfm <cfinclude template="head.cfm"> <p> <table width="100%" border="0" align="center" cellpadding="2" cellspacing="0"> <tr> <td> <div align="center">
3
by: =?Utf-8?B?UnVkeQ==?= | last post by:
Hello All! I may have posted this problem before, but it was awhile ago. I'm working in VS 2003. I have 10 file watch process going. It seems that I can only about 6or 7 to run at the same...
2
osward
by: osward | last post by:
Hello there, I am using phpnuke 8.0 to build my website, knowing little on php programing. I am assembling a module for my member which is basically cut and paste existing code section of...
0
by: Lyn | last post by:
I have a problem using the form .Filter and .FilterOn properties which causes Access to crash (as detailed in a separate post). The form operates in continuous mode, displaying matching records...
5
by: Jim Mandala | last post by:
Using Access 2003 front end; SQL Server 2005 Back end: I have a complex form that has lots of data fields including about thirty or so checkboxes storing Yes/No data that I would like my users...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.