Filter recordset using query results

Hi all

I have a form based on a query called [qry Quarterly Planning], it lists all 
Itineraries on the system. On this form you can filter records by specifying 
a Start and End Date for the [ReviewDate] and/or [Specialist]. It is a 
subform on a main unbound form, lets call this Subform1. Along side this I 
have another subform (Subform2) which displays ReviewDates that exist 
against an Itinerary. In other words Subform1 has a start date of an 
activity and if the activity lasts longer than 1 day, then the additional 
dates are stored in Subform2 (ItineraryDates). Currently when I filter 
Subform1 on the dates it only shows itineraries based on the start date of 
the activity (ReviewDate). What I want to do is also include any Itineraries 
that have the dates in subform2 (ReviewDates) that fall in the date range 
too. ie if you search between 01/04/10 and 30/04/10 Itineraries that maybe 
start in March but run into April should be displayed.

I have created a union query ([qry quarterly planning itineraries]) which 
combines all dates and links to the Itinerary to display all the Itineraries 
needed, but I am not sure how to use this in my form filter? I know you can 
change recordsource but the user may then want to change the dates and do 
another filter so this would need to be on the original recordsource again. 
Please can you tell me how best to achieve this?

N.B. Please don't worry about the db design as this is a very large database 
and it has been changed over the years to best suit our working practices... 
I can't change the table designs now.

Thanks.... Sue.
(Access 2003) 


0
Sue
3/18/2010 11:09:44 AM
access 16762 articles. 3 followers. Follow

0 Replies
899 Views

Similar Articles

[PageSpeed] 55

Reply:

Similar Artilces:

Using data from cells in a Query to a MS Access Database
I need to use a MS Excel 2003 spreadsheet to perform a simple set of calcs. One piece of information I need is stored in an Access Database. If I could have, I would have converted the spreadsheet to an MS Access database and programmed the query to use information from text boxes. However, not all employees in the company have MS Access on their computer. So I need to use an external query in Excel to look at the MS Access Database. I can do that pretty easily. The Query Wizard walks you right through it. However, I need to make it so that the criteria for the query is drawn ...

Data Connection: Access Query to Excel How to get rid of header ro
Hello. I have created a data connection in Excel to an Access query. It does exactly what I need however the header rows of the query are imported as well and all I want are the data in the simplest way possible. Thanks in advance. It depends on how you setup the data connection. If you're doing it in VBA using ADO and a reference to the query, the default is to not import the field headings. If you're doing it without VBA, using Get External Data on the Data ribbon in Excel 2007 you'll get the headings. Here's a short example using Micros...

Using the combo box from the forms toobar
When using the combo box from the forms toolbar I get text that is a specified size. I can increase the size of the drop down arrow and box but I can't change the text size. How do I change the text size? Hi R, > When using the combo box from the forms toolbar I get text that is a specified size. I can increase the size of the drop down arrow and box but I can't change the text size. How do I change the text size? You can't. The combo box from the forms toolbar is a fixed font. If you need a different font, you'll have to use the combobox from the control toolbox. Re...

Using Sound Recorder in Word VBA SOMETIMES does not work
I am recording short (< 60 secs) snipets of sound using Sound Recorder under control of Word VBA using the following Public Sub recordSound() Selection.InlineShapes.AddOLEObject ClassType:="SoundRec", FileName:="", LinkToFile:=False, DisplayAsIcon:=False End Sub All is OK most of the time but on some machines I get a message that says "Unable to load package" (or something like that ... I have never seeh it but 2 people have reported this). Does anyone know what might be causing this? I think both occurances were under Word 2007 on Vista. ...

Copying form and subform to new record (using tip from Allen Browne's website)
I'm using code from Allen Browne's website for copying data from a form and its subform into a new record. I keep getting syntax errors when running. I've quadruple checked the field names. Any ideas what is causing the errors? code> strSql = "INSERT INTO Tbl_WorkorderEntry (Log#, LogLetter, Description, OrderType, CoatingType, SourceType, SourcePlatform, SourceFileType, SourceFileName, FilePrep, Quantity, Originals, Format, Paper1, Paper2, Paper3, Paper4, Paper5, Paper6, FinishedPieces, FinishedSize, FinishingServices, PricingNotes, ItemsPricing, Pricing, Ex...

Result of one combo box, affecting results of another...
Just out of curiousity... Is there a way to have what the user selects in one combo box on a form, affect what is available for the user on another box!? For instance... Box A has 8 different addresses.. Within those addresses there are 12 different building ID locations... Instead of having all 12 building locations for each address always appear in the combo box, If the user picks building 4, is there a way to just have the building ID options for building 4 showing in combo box B? Charles! Use your favorite search engine. Use "Cascading Combo boxes" for search terms. -- Reg...

remove filter on a form when closing
Hi All, I have form "frmRecipes" that can be called from two different places: from a button on a form named "frmUserRecipes" and from a button on the main switchboard. From "frmUserRecipes" I use this code to take me to a specific record: DoCmd.OpenForm "frmRecipes", acNormal, , "FoodCode=Forms!frmUserRecipes!OLDRNUM" This works fine. If I use the button on the switchboard I get prompted for "Forms!frmUserRecipes!OLDRNUM" when "frmRecipes" opens. I need it to open on the first record in this instance - no filter. I...

book query
I would like a recommendation for a good book on Excel. I would place myself as an intermediate who has just started to learn some formulae. I use Excel XP, 97 and also Excel X on my mac I would like it to be not too heavy going so as not to put me off learning Any help would be appreciated Bobby For information and examples on Excel's functions, you can download Peter Noneley's Function Dictionary: http://homepage.ntlworld.com/noneley/ or Norman Harker's information on functions: http://www.contextures.com/functions.html and John Walkenbach's book on formul...

cannot use wildcards in LDAP query
I am using genifax and two of the tools they provide error out when using wildcards as part of an LDAP query in the production envioronment. I also tried the tool against servers running LDAP in different sites but they fail. I tested this tool against a lab machine all the same service pack/patches and it works correctly. Any ideas? sorry this is a 5.5 server running sp4 on nt4 sp6a "Charles Hibbits" <Charles_hibbits@hotmail.com> wrote in message news:%23v7mrVmJEHA.4052@TK2MSFTNGP11.phx.gbl... > I am using genifax and two of the tools they provide error out when using...

item barcodes and sql query
This is a multi-part message in MIME format. ------=_NextPart_000_0007_01C89274.4D47EC80 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, Customer forgot to set the right barcodes for the items they left it blank. Customer would like to print labels but since they did not specify the right barcode types in the item properties I guess it will not work. So is there a sql query that can do that? For example I would like to it to use upca for all items, since almost all of their products are upca's. Thank you. ------=_NextPart_000_0007_01C...

Formula to show specific results?
I know the answer to this is something simple, but it has me stumped. I need a formula in C4 which, when copied to the remaining cells in Col C, will show the listed results. A B C 1 4 0 4 2 3 -2 5 3 -1 -3 2 4 -2 2 0 TIA, OldKenGoat Looks like homework? But you pen name belies this idea! Surely you can see that: 4 - 0 = 4 2-(-3) = 5 -1-(-3) =2 -2 -2 = 0 So in C4 enter =A4-B4 OR in C1 use =A1=B1 best wishes Bernard "OldKenGoat" <wswensonNOJUNK@cablespeed.com> wrote in message news:ezKkD$cxEHA.3476@TK2MSFTNGP15.phx.gbl... &g...

Using a spread sheet for e-mail addresses
I have a large number of e-mail addresses in a spread sheet column. Is there a way to copy the e-mail addresses into the To box (bcc) of an outgoing e-mail without first installing them in my contacts folder? Thanks, Len Urban Save the column as a text file, open the file in Word or your favorite text editor, replace all the line endings with semicolons, and paste it all into the BCC field. -Peter "Len Urban" <lturban@verizon.net> wrote in message news:hgVph.373$9B6.212@trnddc07... >I have a large number of e-mail addresses in a spread sheet column. Is >ther...

reconcile Inventory with General Ledger when using Average Perpetu
The customer wants to have the ability to reconcile their inventory balances with the general ledger accounts when Average costing is being used. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser...

Reporting services available for use with access?
I am building a DB in access for my project managers, some will be using just access. Is it possible that report builder can be used in Access? In some way? ...

Pivot Chart Filter
I have two Pivot charts that compare similar data and have them displayed side-by-side. When I change the filters in one chart, I want to apply the same filter for the other chart. Is there a way to have one Pivot Chart Filter Pane control two or more Pivot Charts without having to click each chart and change to filter for each one? ...

Returning records from Recordset object
Does anyone knows how to get records returned from the OpenRecordset function in the session.database.openrecordset function? I would like to returns records from a query using the session object. ...

Filter Form Records button
I'm working with a form that displays all records, but has a filter button to show only a certain year, or all (*). Once prompted for the year in the messagebox, hitting cancel returns me to the form but with NO records showing. How can I hit cancel and have it go back to the form, displaying whatever records it was before hitting the filter button? Here's my current button code: ------------------------------------------------------------------------------ Private Sub btnFindStyle_Click() On Error GoTo Err_btnFindStyle_Click Dim msg, Style, Title, myResponse msg = "Enter Year...

Most recently Used (MRUD) List
Is the MRUD only updated if you open a file from within Excel? That seems to be the way it's working for me. Is there any way to get Excel to add files the the MRUD if they are opened by double-clicking in an Explorer window? Pierre Only in XL2002 will the files show up in the MRU list. Earlier versions do not. Dana DeLouis offers VBA code in this google search thread. http://snurl.com/1qzz Gord Dibben Excel MVP - XL97 SR2 & XL2002 On Tue, 29 Jul 2003 19:07:38 -0400, stephens@no-fixed-abode.com wrote: >Is the MRUD only updated if you open a file from within Excel? That...

FindRecord2K works perfectly, how to display results in my form??
I downloaded the findrecord2k search form. I would like to add a command button that takes those results that are generated from the query and displays them in my form. There are several command buttons available, would it be easier to modify an event to include displaying the search results in my form instead of the unbound control? ...

filtering colums
Can anyone tell me how I filter a column, so it shows the entries that are not present in a second column? say: column1: column2: 1 2 2 5 3 4 5 How can I get a third column showing: 1 3 4 Is this possible in Excel??? Thx.. Can you use a third column and filter on that? I'd put this in C2 (headers in Row 1): =isnumber(match(a2,b:b,0)) and copy down Then filter to show the False's in that column. benny wrote: > > Can anyone tell me how I filter a column, so it shows the entries that are > not present in a second column? say: ...

Using lparam in cheaderctrl
Hi there, I know that when insertcolumn is called from inside a clistctrl, it takes care of calling methods in header control to add the header text. However is there a way of sending some more information. I want to make use of the cheaderctrl's (HD_ITEM'S) lparam which is supposed to be an application define lparam. Can I do this at the time of inserting the columns? If not, when and how can I do it? Because then when I get a notification of onheaderclicked, I want to be able to read the value stored in this lparam to do stuff accordingly. Thank you. I think it's kinda anno...

Frequently used formulas
Is there a way to access frequently used formulas easily? I have a report where I need to import the data and then type in the same formula each time in order to create analyzable results. Is there a quicker way? Thanks I'm not sure this will work well for you, but it might be worth a try. Why not create a workbook with just the formulas in place, but without the data? Then you'd open that workbook, import the data into it and save it out with a new name? If that's not good for you, perhaps simply copying the formulas into a notepad (.txt) file and saving it s...

Report Filtering
I just added CRMAF to my query and now I get no results whatsoever no matter what filter I use. Has anyone seen this before and is there any way around it. I have a report with 3 subreports in it. All I wanted to do was be able to filter the main report but now I get nothing. Any suggestion will be appreciated. It looks there is something wrong with the filters. The common problems could be conflicting filters, for example one filtered defined using CRMAF and other filter was a static one defined in reports. You can do a SQL trace using SQL profiler. It will show up the final SQL st...

Copying Query Results into a Table
Hi, I have a Query which filters out certain records from an existing Table, how can I copy these filtered out records which were based on certain criteria, to an existing Table which have exactly the same name of fields as the Query? Thanks Ange Kappas wrote: > I have a Query which filters out certain records from an existing >Table, how can I copy these filtered out records which were based on certain >criteria, to an existing Table which have exactly the same name of fields as >the Query? Try changing the query to an Append query (using the Query menu...

Color lost when using two instances of Excel
I am using two instances of Excel on two separate screens. In one screen I have a workbook with custom color scheme and in the other a workbook with a default color scheme. Whenvever I copy data (either using paste or paste special) from a worksheet in one book to a worksheet in the other, the color scheme is copied across as well, thereby putting out the formatting of the colored cells. Has anyone come across this or any thoughts as to a solution? Open the two workbooks in the same instance of excel. You can continue to use multiple monitors with a single instance. Yo...