Filter records by date in subform by date entered in mainform.

Hello, I've spent 3 days trying to understand what I'm doing
wrong.....Here is the summary of my problem.

I have a form named 'frm_EfficiencyStats' and a subform within the
form named 'qry_Total hours subform' (the subform is based on a
query).  The form contains a text box (for date) which is bound to a
table 'tbl_EfficiencyStats'.
I would like to be able to enter a date in the text box on my form,
and display the records in the subform based on the date I entered in
the text box.  The subform will only pull records from my table based
on the date.
Here are the fields I am using which are essentially tied to one
another.....'DATE' and 'NAME'.

I'm not sure if a filter needs to be applied, but I thought
associating the link child and master fields in the subform would do
the trick.

I apologize for the messy explination, please let me know if you
require any further information.

Thank you for any assistance you may be able to offer.

0
Kevin
5/8/2007 4:35:15 PM
access 16762 articles. 3 followers. Follow

1 Replies
2482 Views

Similar Articles

[PageSpeed] 54

On 8 May 2007 09:35:15 -0700, Kevin D <kdeonarain@gmail.com> wrote:

>Hello, I've spent 3 days trying to understand what I'm doing
>wrong.....Here is the summary of my problem.
>
>I have a form named 'frm_EfficiencyStats' and a subform within the
>form named 'qry_Total hours subform' (the subform is based on a
>query).  The form contains a text box (for date) which is bound to a
>table 'tbl_EfficiencyStats'.
>I would like to be able to enter a date in the text box on my form,
>and display the records in the subform based on the date I entered in
>the text box.  The subform will only pull records from my table based
>on the date.
>Here are the fields I am using which are essentially tied to one
>another.....'DATE' and 'NAME'.

First off... *change your fieldnames*. DATE is a reserved word for the builtin
Date() function; NAME is a reserved word because *everything* - forms,
controls, fields, tables - has a Name property. Access *will* get confused
(and may have done so, causing your problem).

A control used for the purpose of *finding* or filtering records should (must,
I'd say) be Unbound. Otherwise you will overwrite whatever the field contains
in the first record, or whatever record is displayed.

You can use an unbound textbox named (say) txtDate, and set the Master Link
Field property of your subform to [txtDate]. Do use the brackets. Access won't
give you this as a choice but you can type it in. The Child Link Field should
be the name of the datefield in the subform's query, but the query should
*not* reference the form nor should it be filtered by the date value; as you
guessed, the master/child link should take care of it.

             John W. Vinson [MVP]
0
John
5/9/2007 12:24:08 AM
Reply:

Similar Artilces:

Entering Data to make a list?
Is there a way that when I enter something into A1 &B1 it will fill down on a List? Like if I type apples in A1 & $12 in B1 on a certain sheet it will copy Apples and $12 , then if I type Pears & $5 it will copy to the next blank cell and so on -- Thanks in advance for your help....Bob Vance .. .. .. .. Bob I'm assuming you have this list on another sheet. This little macro will do the following: When you enter something in B1 of the first sheet, it will copy A1:B1 to the bottom of the list in Columns A & B of the second sheet. It will then clear (erase) the conte...

Not allow entering repeated references in a column
Frank, Sorted! :) :) :) ... sth was missing (<) The formula to be used is =COUNTIF($A$1:$A$25,A1)<=1 and NOT =COUNTIF($A$1:$A$25,A1)=1 ... little things make the difference, isn�t it ;) ? Thank you very much for your support and your time. Ritinh -- ritinh ----------------------------------------------------------------------- ritinha's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1566 View this thread: http://www.excelforum.com/showthread.php?threadid=27195 ...

Dates in Formulae
Dear all, Suppose I want to create a basic formula eg one which adds 6 months to a date, how would I do it? Doing it by date + 6 months worth of days will obviously not work as the number of days in 6 months will vary. Any thoughts? Thanks, Danny Hi Danny, =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) But do think about what you want the day to be in case the source date is, for example, august 31. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Aardvark" <djurmann@hotmail.com> wrote in message news:%23cU8IWF0DHA.2580@TK2MSFTNGP09.phx.gbl... > Dear all, > > ...

Auto fill cells with dates, exclude weekends?
What I'm doing is filling in multiple cells with dates (by dragging and filling) automatically. I was wondering if there was anyway for it to skip the weekends within the dates? Thanks in advance. Hi Sam rather than dragging with the left mouse button down, drag with the right .... when you let go you'll see an option for fill weekdays. Cheers JulieD "Sam Weber" <sam@hostradius.com> wrote in message news:af3901c4796d$85dff660$a501280a@phx.gbl... > What I'm doing is filling in multiple cells with dates > (by dragging and filling) automatically. I was wo...

Converting a Date
I have a column with dates in the format mm/dd/yyyy. I would like to have an additional column the will give me only the month and another only the year, all from the above date column Pls Advise. Thankyou. Do not store the month and year in other columns. Instead extract the month and years using the Month() and Year() functions. They will give you number of both. For example today (March 19, 2010) will show up as 3 for the month. If you need to see March instead of 3, use the Format Function something like below: Format([TheDateColumn], "MMMM") -- Jerry Whi...

Date Changes to 01/00/00 when i enter any date.
Using XL2003, I have a problem. When i enter a date in cell, such as 08/25/10, or use the shortcut Ctrl ;, XL displays 01/00/00 in the cell instead. I have formatted the cell as a date. What can I do to fix this problem? Thank you so much. Tonso ...

Create Formula
I need to create a formula where I add a set number of Networkdays to a start date. Example: Start Date = 1/2/07 Number of Networkdays = 21 End Date = Is calculated If Start = 1/2/07 and 21 Networkdays are added, what is the retured end date???? I can only find example of Networkdays where I would be providing the Start and End Dates, and it will calculate the Networkdays. I can't find example where End Date is calculated based on the number of networkdays from start date. Can anyone help with a valid formula for this? Thanks!! ...

Contract Date Issue
Basically, you create a contract normally. However, when you go to add a contract line, you get a date error. Apparently, the system thinks that the contract line start and/or end dates are outside the contract start and end dates, even though the contract line defaults to the using the contract start and end dates (i.e. the dates on the line match those on the contract). I know this must be a data type issue with the datetime values. Has anyone else had this problem? Is there a fix? On Mon, 31 Oct 2005 15:08:14 -0500, "Tim Powers" <tim.powers@rimrock.com> wrote: ...

Activity without date
In my current project plan I know a few activities that will be conducted, more specifically meetings that are important. Now, I don't know the date they will happen yet and I want to indicate that by adding the activity but not entering a date. Is that allowed in MS Project? Since Project is designed to calculate dates, not only can you enter a task without a date, you should be entering ALL tasks without dates and then Project tells you when you should schedule them. -- Steve House MS Project Trainer & Consultant "Hertsh" <Hertsh@discussions.microsoft....

Archiving broke
Outlook is not archiving most of my deleted and sent items. Yes, I've read about making sure the modification date is within range as well as making sure these folders are set to archive. THE SETTINGS ARE CORRECT. I heard somewhere that if the msgs modification date is "none", then Outlook interprets this a 1/1/4501 (and empty date) and therefore things don't get archived (my deleted and sent items). Anyone heard if this is true...and if so, is there a workaround?????? Amil Why are you keeping deleted items? If you need to keep them they should be in a specific Folder...

Using SUMIF to add data between a range of dates
Hi, I am developing a cashflow spreadsheet, and need to add a range of values (in column B) based on the criteria that they are relating to a set week, ie in column B has the amount to be paid, and column C has the date the amount is due. I need to find out the total amount due between 2 dates. Does anyone know how I can do this? Hi With start date in B20 and end date in B21 try this: =SUMIF(C2:C5,">=" &B20,B2:B5)-SUMIF(C2:C5,"<="&B21,B2:B5) Regards, Per "Jaspa" <Jaspa@discussions.microsoft.com> skrev i meddelelsen news...

Excel importing dates from a RTF in a macro
If I open a RTF with a date in, the date appears as it does in the original RTF. If however, if I run Office 2000 and record the above event using a macro, when played the date swaps the mm and dd round (without changing the properties, so in fact the mm is now the dd and the dd is now the mm) This only occurs if the dd is 12 or below in value for obvious reasons. I then tried to open the RTF date column as text, and then change the column properties after it is imported. This does work fine, but I am using the Application.Filesearch and am not sure how to integrate the OpenText function whic...

Combining date and time into one cell
I have the date (m/dd/yy) in one cell and time (hh:mm) in another cell. How can I merge these two in one cell with the format m/dd/yy hh:mm ? Thanks. Date in A1, time in B1, combined in C1: formula is =A1+B1 and format as you describe. On Sat, 22 Jan 2005 14:03:02 -0800, "Kelly C" <KellyC@discussions.microsoft.com> wrote: >I have the date (m/dd/yy) in one cell and time (hh:mm) in another cell. How >can I merge these two in one cell with the format m/dd/yy hh:mm ? > >Thanks. =TEXT(A1,"m/dd/yy ")&TEXT(B1,"hh:mm") is one way -- HT...

International date formats
When performing an Edit, replace on the cell value 10.09.03 (a system genereated date vale of 10th Sep 03) replaceing the . with /, Excel (and in VBA when recorded as a macro) sometimes changes the cell value to 09/10/03 (9th October 03). This is driving me potty and have spent a vast amount of time on it trying to work out what Excel is doing!!!! My Regional settings are set fro the UK. Any ideas anyone? "Mike" <michael.may@npower.com> wrote in message news:2d44701c39483$97f77730$a601280a@phx.gbl... > When performing an Edit, replace on the cell value > 10.09.0...

entering current time in shared work book
Hi, i am maintaing a excel tracker in shared drive where i am manually entering the time when a person came into the office. Is it possible that when a person comes he himself open the shared file and click the cell corresponding to his name so that the time of that moment can be stored and that can not be further updated with new value (time)... below is the format Name of Person Date Name A Name B Name C 21-Apr-10 6:44 PM 6:48 PM 6:40 PM 22-Apr-10 6:30 PM 6:34 PM 6:31 PM 23-Apr-10 6:35 PM 6:39 PM 6:40 PM I would try first to setup a list on another she...

Sound when entering transaction into account
I just upgraded to Money 2004 from Money V5. Is it possible to change the lame "ding" sound when entering a transaction to the satisfying "ka-ching" sound in Version 5? -- Norm Although Money parameters turn on whether sounds are played, the actual sounds are stored in the system level. So go to the Start and select control panel. In the control panel select "Sounds" (slightly different name and location depending upon which operating system. scroll down the sounds until you find Money and the "enter transaction" you can then change it ...

Report Builder 1.0 Filter Prompts
I have created an ad-hoc report and want to add a filter. I understand that the Report Builder will only pre-populate the 'in this list' for that filter if the list contains less than a pre-determined number of entries. Fair enough. However, where can I find out what this magical number is...it would certainly be handy if I could be allowed to set the pre-determine number myself, or at least view it. I found an article that said that value is in the DataSource (or the DataSourceView) but I have found nothing that tells me what the tag, or the location of that special ...

Record Notes Report
Is there a Record Notes report? We'd like to print out all the notes attached to customer records. You could do this by creating a custom report in Report Writer or using a SQL query. Join the SY03900(Record Notes master) to the RM00101(Customer Master) on the NOTEINDX field. Alternatively, you could pull this into a Crystal Report. If you use Report Writer you may have to first create the relationship between the tables. VGrinam "Elaine" wrote: > Is there a Record Notes report? We'd like to print out all the notes > attached to customer records. ...

Subform..Deleting a Record by code
I have a Before Update event to Trap an Entry of "SN" (which is not acceptable) and results in a Msgbox and "Cancel = True". If the User backspaces the "SN" leaving a Null, and leaves the Record, how can I delete that record by code? TIA - Bob "Bob Barnes" <BobBarnes@discussions.microsoft.com> wrote in message news:46A5C7AA-F19F-477E-938A-D99EE9CBF4DC@microsoft.com... > I have a Before Update event to Trap an Entry of "SN" (which is not > acceptable) and results in a Msgbox and "Cancel = True". > > If the U...

downloaded transaction date
There used to be a setting in prior versions of Money to not overwrite your money file with the downloaded transaction dates when matching transactions. Does anybody know how to make that change in Money 2005? I've not had any luck finding it. Thanks. -z I found the setting - tools/settings/online services/uncheck the 'overwrite transactions' box. Cheers, -z "zender" <anonymous@aol.com> wrote in message news:uelFWDVuEHA.2788@TK2MSFTNGP09.phx.gbl... > There used to be a setting in prior versions of Money to not overwrite > your money file with t...

Automatic date editing
Hi When I edit a cell, I would like to know if the last editing date could be shown automaticly next to it example ID Value Last date value was edite Refrigerator $945.00 12/1/200 Thanks... Hi Marcio You can do it with the change event of the worksheet This example will place the date/time in the B column if you change a cell in the range A1:A20. Place the code in the Sheet module Right click on a sheet tab and choose view code Paste the code there Alt-Q to go back to Excel Private Sub Worksheet_Change(ByVal Tar...

Simple Filter
I have a datasheet with a list of sales numbers and multiple invoice dates (stored as text) for each sales number (there are duplicate sales numbers, and distinct invoice dates). I need to compress the list so I have only one (any one) invoice date for each sales order. Any Ideas? Can you post a small sample of the data AND what you would like the result to be? -- Lynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com "c.n.ricker" <c.n.ricker@gmail.com> wrote in message news:1181827217.711646.155340@d30g2000prg.googlegroups.com... >I have a datasheet with a li...

Fein FBK-1 Filter Bag Kit for 9-11-55 Turbo I
List Price:$48.20 Image: http://bestdeallocator.info/image.php?id=B001342J2E Best price found: http://bestdeallocator.info/index.php?id=B001342J2E This is a complete kit for converting the Turbo I vacuum to a two-stage filtration system. Remove the cloth dust bag and mount the 1 Micron filter and the dust bag to clean up smaller particulate material with the Turbo I vacuum. This kit includes everything necessary in one package. Great for work with drywall and sheet rock dust as well as other smaller particulate materials. SIMILAR PRODUCTS: Fein 913038K01 3-Pack of Vacuum Bags:htt...

Criteria to return all records if selection from form is null
Hi, I'm trying to pass multiple query criteria from form controls. I'm using the following type of syntax for the criteria: Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*") And this works fine for the fields where there are no Nulls in the data, but if there are Nulls in this field I don't get those records. Quite understandable since Like "*" doesn't return Nulls. What I'm trying to do is to return all records when there is nothing chosen in the selection box on the form. I've tried a bunch of different IIfs wi...

Serial Dates
Hi everyone, Recently a co-worker came to me with a work sheet that she had been using to track dates that invoices were received. She told me the sheet was working fine one day. The next day, when she entered a date, the cell would only return the serial date no matter how she formatted the cell. The actual date would appear correct in the formula bar, but the cell showed only the serial date. We have tried everything I can think of to get the cells to format to show the date but have not had any success. I finally solved her delima by copying all the data to another new worksheet...