Unable to use filter in datasheet view or add criteria to query in design view

Hello all,

I have a question regarding a custom function. The query results can't
be filtered nor can a criteria be used in the design window to filter/
restrict the records. The function returns the correct answer for all
the cases but I need the functionality to filter the results.

Here is purpose, input and output and example uses:

Purpose:
If given 2 dates, a month and a year, the function returns the number
of days that fall between the start and enddates, in the given month
and year.
Example 1,
	My_Function(#1/1/2010#, #1/15/2010#, 1,2010) Returns 14
		BTW - 14 is correct because the end date is not considered a full
day; another example:
	My_Function(#1/1/2010#, #1/15/2010#, 2, 2010) Returns 0

Input:
  StartDate As Date
  EndDate As Date
  intMonth As Integer
  IntYear As Integer
Output:
  Integer value

My problem:
The results in the query can't be filtered nor can a criteria be used
in the design window.

Please let me know if you need more detail
0
HouBMan
3/3/2010 3:38:29 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
2675 Views

Similar Articles

[PageSpeed] 43

HouBMan wrote:
>I have a question regarding a custom function. The query results can't
>be filtered nor can a criteria be used in the design window to filter/
>restrict the records. The function returns the correct answer for all
>the cases but I need the functionality to filter the results.
>
>Here is purpose, input and output and example uses:
>
>Purpose:
>If given 2 dates, a month and a year, the function returns the number
>of days that fall between the start and enddates, in the given month
>and year.
>Example 1,
>	My_Function(#1/1/2010#, #1/15/2010#, 1,2010) Returns 14
>		BTW - 14 is correct because the end date is not considered a full
>day; another example:
>	My_Function(#1/1/2010#, #1/15/2010#, 2, 2010) Returns 0
>
>Input:
>  StartDate As Date
>  EndDate As Date
>  intMonth As Integer
>  IntYear As Integer
>Output:
>  Integer value
>
>My problem:
>The results in the query can't be filtered nor can a criteria be used
>in the design window.


There is something wrong in the query.  Poast back with a
Copy/Paste of the query's SQL view.

-- 
Marsh
MVP [MS Access]
0
Marshall
3/3/2010 4:41:40 PM
You should be able to filter based on the results of the function.

SELECT SomeField, My_Function(#1/1/2010#, #1/15/2010#, 1,2010) as TheResult
FROM SomeTable
WHERE My_Function(#1/1/2010#, #1/15/2010#, 1,2010) = 14

What you cannot do is the following:
SELECT SomeField, My_Function(#1/1/2010#, #1/15/2010#, 1,2010) as TheResult
FROM SomeTable
WHERE TheResult= 14

The latter query does not work because at the time the criteria is applied 
"TheResult" column does not yet exist.  That is just the way Access (JET) SQL 
works.

If this does not explain it for you,
Please copy and post the SQL of your query that does not work. Also please 
tell us what error messages you encounter (if any).

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

HouBMan wrote:
> Hello all,
> 
> I have a question regarding a custom function. The query results can't
> be filtered nor can a criteria be used in the design window to filter/
> restrict the records. The function returns the correct answer for all
> the cases but I need the functionality to filter the results.
> 
> Here is purpose, input and output and example uses:
> 
> Purpose:
> If given 2 dates, a month and a year, the function returns the number
> of days that fall between the start and enddates, in the given month
> and year.
> Example 1,
> 	My_Function(#1/1/2010#, #1/15/2010#, 1,2010) Returns 14
> 		BTW - 14 is correct because the end date is not considered a full
> day; another example:
> 	My_Function(#1/1/2010#, #1/15/2010#, 2, 2010) Returns 0
> 
> Input:
>   StartDate As Date
>   EndDate As Date
>   intMonth As Integer
>   IntYear As Integer
> Output:
>   Integer value
> 
> My problem:
> The results in the query can't be filtered nor can a criteria be used
> in the design window.
> 
> Please let me know if you need more detail
0
John
3/3/2010 4:50:22 PM
Reply:

Similar Artilces:

How do I cut and paste using a macro....
Hello. I'm having trouble creating a macro that will let me cut postcodes from one cell and paste into another cell. I would appreciate if anybody can help me. What I have, is a large excel spreadsheet with a couple of thousand names and addresses. The address fields start in column B through to G. Each line of the address should be in a separate cell within each row, but the postcodes have all ended up in the same cell as the last line of the address. I need to cut the postcodes out of these cells and paste them into column H which is empty. The addresses are different lengths, leavi...

Unable to open Outlook2003. Error: MAPI unable loading msncon.dll
I am trying to set up Outlook 2003 on home computer but keep getting the following message when trying to open application. MAPI was unable to load the information service msncon.dll. Since I am not open to open, I cannot get to the help feature. Please advise Have you downloaded the latest MSN Connector from their site? Or, if not using it, have you uninstalled it? Try opening Outlook in Safe mode (start->run->outlook.exe /safe) and go into the Advanced options and uncheck it from add-in manager or COM add-ins. --� Milly Staples [MVP - Outlook] Post all replies to the group to ...

Using Money 98 in a virtual PC
I have used Money 98 for a good many years and am attached to it. It has run in 98, 2000, and XP without a hitch. I now have a 64 bit Windows 7 machine with a virtual PC installed which operates on XP as its operating system. I have my Money 98 installed there, but in that environment it runs with glacial slowness and I don't know why. All of the other apps I have in that VPC run just fine. I did notice that instead of using My Money.mny it uses My Money XP.mny. I can't imagine why that would cause slowing. Can anyone tell me where to look for answers? Thanks In microsoft....

Unable to Edit/Add records on Form in a New ADP
I just started playing around with rebuilding a MDB file as an ADP. I built a form and subform from scratch, but I'm unable to edit or add records. SELECT Id, LoadDate, TrailerDOTNumber, SealNumber, SealDate, SealId, LoadLocation, LoadStatus, DispatchStatus, DispatchLocationId, DispatchUserId FROM TrailerActivityHeaders ORDER BY LoadDate Recordset Type: Updatable Snapshot I'm getting the 'This recordset is not updatable' Ideas? Never mind... (Does anyone have a spare bandage btw? I cut my head banging it on the wall due to my stupidity...) "D...

Using Autocorrect to remove spaces
Hi, I am looking at how to remove extra spaces, and have been thinking about using Autocorrect. I have been reading another thread which suggested; For Each cell In Selection cell.Value = WorksheetFunction.Trim(cell.Text) Next What I have been using is; With Application.AutoCorrect .AddReplacement "Want", "Replace" End With Works well within a loop changing any "Replace" text with "Want". Something I didn't get Substitute to do correctly within a loop. What I can't get AutoCorrect to do though is work with spaces, so...

Design View vs Form View
In Access 2007, I have a graph in Design View which shows differently in Form View. In other words, the information that is plotted in the graph shows differently in the two views. Why is this? Thank you, Bill Bill, Your probably saying the design data displays an earlier design results, but the form view graph data is correct. The graphs have acted that way for the past Access versions as well. I think the Graphing in Access is borrowed from Excel, and does not update in design view as it should. One way around that, is to run the query behind the graph in des...

Unable to open calendar
Folks, i cannot open or view my calendar. When I start Oultook a Pop Up appears saying "Could not read the Calendar" On clicking Ok, the pop up vanishes and nothing happens. If i then go over to my calendar, it says "Cannot display the folder" I have run Oultook Diagnostics and nothing shows up as irregular. I ran the repair function (off my setuo CD) and nothing happened. I also re-installed Outlook and rebooted my comp and nada. When staring in safe mode i also get he same errors. I am using Off 2007, Win 7 TIA, P "phamiltonsmith" <...

using embedded .xsd vs file based .xsd
I am thinking of embedding my schemas as embedded resources instead of reading it using URI at run-time. I came across some snags while trying to do just that such as, previously unknown to me, XmlValidatingReader.Schemas.Add was using XmlValidatingReader.Resolver to resolve my schemas using the URI method. Resolver property was never set, so the reader simply ignored any external references even though one of the schema explicitly imports the other one. I verified this by removing the referred schema from the schema collection, and the code chugged along fine. But when I changed the code to ...

unable to download emails and unable to stop synchronization
I am going to try one more time to get this fixed. My Outlook 2003 has been unable to download emails. While it is trying, a small box in the tray section shows up and it states that it is trying to synchronize folders. After a time out time Outlook gives me errors. This is the error message. It has these numbers 0X800CCC15 , or 0X8004210A, or 0X8004210B, which these error messages state that either I did not have enough timeout or look and see if server name is wrong. All of you have given me these suggestions. no joy. Thank you very much however. I went ahead and finally found a sect...

Error sending email from Order using template
I created an email template to be used to confirm receipt of an order. It copies a few fields from the Order Form. It does not work, but shows a really helpful error message: MS CRM Web Page Dialog -An error has occurred; please consult your SA. Well, that's me. But I have no clue where to look. The built-in Order Thank You template shows the same behavior: Error. I made lots of email templates to be used from the Opportunity and Quote forms; these all work fine. I tried switching on tracing on the client (don't want to mess with the server registry keys) so I would get some ad...

using for-next index in range variable
MSDN seems to say that range values must be written in the A1 format. I need to compare a value in column D (sample IDs) of worksheet import and compare it to column A (location) of worksheet data. If the values are equal I need to set a cell in worksheet data equal to the sum of columns I:M in the same row that the sample ID = the location. Will something like this work: If worksheet(import).cell[D"i"] = worksheet(data).cell[A"j"] then worksheet(data).cell[k,j] = sum(worksheet(import).[I"i":M"i"] Is there a different, perhaps easier way to do this? ...

how can I use my e-mail addresses on excel xp with microsoft outlo #2
I need to use my addresses on Excel xp with Microsoft outlook. I want to use my excel file as a database that I can change and improve everyday. I need to send mails to more than 1 contact, so creating an e.mail link is not enough for me: in fact, if I click on it, I send my mail only to 1 address... Txs ...

Which shapes should be used?
I'm trying to draw a diagram which shows that an application is using odbc to access SQL Server database. I want to know which shapes I should use to represent the application, odbc, and SQL Server database. Thanks. ...

Using "OR" in an IF statement
I am trying to use the command OR in an If statement and I am having no luck. In Lotus 123 I could string commands by using #OR# to produce a common result. For example: =IF(J3="INVEST" #OR# IF(K3 = "INVEST",1,"NO")) I can't seem to make this work in Excel. Can anyone provide me with a correction? Doug, Like this =IF(OR(J3="Invest",K3="Invest"),"The true condition","the false condition") If the true or false condition is numeric then drop the quotes -- Mike When competing hypotheses are other...

Run a Query using VBA without User Interface
Hello, I have a VBA Macro that Opens an Append Query (see below), which works great, except for the end user has to answer the following MsAccess prompts: 1) You are about to run an append query that will modify data in your table. (They have to click Yes) 2) You are about to append 1 row(s). (They have to click Yes) I'd rather them not have to answer either question. Instead I'd like the Append Query to run and do what it's asked without any human intervention. Can this be done? Please help! VBA Macro Code: DoCmd.OpenQuery "tblTask_List Query", acViewNorm...

unable to scroll the page
on microsoft office frontpage 2003 - when i up loaded my web pages to my web site www.millertimeauction.com , there is no sroll bar for up & down of page - any help what browser? it scrolls for me in IE8 scroll bar will only appear if it needs to -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression Web "FrontPage Error" <FrontPageError@discussions.microsoft.com> wrote in message news:EC19C7CE-1C5A-4872-A47B-2C04BD7E96A7@microsoft.com... > on microsoft office frontpage 2003 - when i up loaded my web pages to my > web > s...

Unable to start POP3, NNTP, IMAP4...
Dear all, I'm evaluating to install Exchange 2003. After the installation I find the said protocol haven't been started. I tried to start them in "Servers --> Server Name --> Protocols" but no help. Please comment.... Thanks!! Background: Windows 2003, Exchange 2003, Active Directory DC with all roles of FSMO as it's the first DC in the forest. Thank you very much!! -- Are you boring? Let's come to talk together... ^^ news://news.hk4u.com/hk4u.2hit6 Those services are disabled by default in Exchange 2003. Go to Administrative Tools, Services and enable...

Filter Criteria
Excel Filter Criteria ="abcd" but result includes "abcdefg" How do I restrict results (in one cell) to just "abcd" For the criteria, use: ="=abcd" Yaasien wrote: > Excel Filter Criteria ="abcd" but result includes "abcdefg" How do I restrict > results (in one cell) to just "abcd" -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Brilliant. Now if I have this in a macro it will not accept it. The macro reads ... ActiveCell.FormulaR1C1 = ""=abcd"&quo...

query LDAP
I'd like to make a worksheet with the following columns: Username Full name Email This data is somewhere inside our address book server, an LDAP server (not Windows). I have Googled this but have not yet found interesting results. -- Amedee Van Gasse using XanaNews 1.17.3.1 If it has an "X" in the name, it must be Linux? How To Ask Questions The Smart Way http://www.catb.org/~esr/faqs/smart-questions.html How to Report Bugs Effectively http://www.chiark.greenend.org.uk/~sgtatham/bugs.html Only ask questions with yes/no answers if you want "yes" or "no" a...

how to use cursor to navigate active cells
With our Excel 2003, I could navigate to different cells by using the cursor keys. The cursor up key moved the active cell up. Left, right and down moved the active cell in the appropriate direction. I could select multiple cells by holding the shift key down and using the cursor keys to start selecting large groups of cells. Now, with Excel 2007, the cursor keys move the sheet around in the window, instead of moving the active cell in the indicated direction. I have found no way to select multiple cells. How can I get my Excel 2007 cursor keys to move the active cell, instead of moving t...

Unable to conect
Hi, I have set up my email account to work with aol using the guidlines for outlook 2002 but it is still unable to connect. Any suggestions? Thanks. John <John@discussions.microsoft.com> wrote: > I have set up my email account to work with aol using the guidlines > for outlook 2002 but it is still unable to connect. Any suggestions? Since you pay AOL for their service, my suggestion is to ask AOL. -- Brian Tillman ...

Running Total in report using net of two columns in third column
I have a client time/fees database of over 22k records. I have a report which provided a summary of all time booke and all fees charges to that client. What I require is two additional columns the first which provides a running total of time bookings and the second which provides a running total of the net of the time booked field less the fees charged field, i.e. the net of time bookings which have not been invoiced. I can do this in excel but in access I appear to be getting data from other clients which should not be included in the report. What type of field are you usin...

Addding notes and rating fields to a report
I just started experimenting with creating some reports using vb.net 2003 and the SRS. So far getting most of the data on the report has been pretty easy but I cant seem to get certain data on there. I would like to have columns available for the notes and also rating as of yet I have not been able to find them. Any ideas? ------=_NextPart_0001_666CDF65 Content-Type: text/plain Content-Transfer-Encoding: 7bit <xxdcmast@discussions.microsoft.com> wrote: > I just started experimenting with creating some reports using vb.net 2003 and > the SRS. > > So far getting mos...

Unable to click on charts & unable to create any new charts Excel
Hi Just started using Microsoft Excel 2007 and I seem to have done something that means the chart option is unavailable. The chart section is now grey and all charts I have created are 'locked' I cannot click on them anywhere. I'm working on a project and need it finished by Monday. Do you mean that if you click on a chart it does not get a border to show it is activated and there are no range-finder borders around the data that makes the chart? This is a bit odd. Have you tried a new workbook to see if the problem is with just this one? My advice with all odd things is...

Long Delay when viewing inbox items
We have a combination of Outlook 2000 and 2002 connected to exchange 2000. All Outlook 2002 clients experience long delays when viewing mail items. So when they click on a mail item sometimes it takes 30 seconds before it shows up in the preview pane. This behaviour occurs for all message types. I have installed the latest service packs on all machines and servers but the delay is still there. Can anyone help please? Regards, Rayyan Have you tried disabling the instant messenger integration? (see tools | options | other) "Rayyan Sultani" <rayyan@seeingmachines.com> wr...