Date ranges using child/master fields

[Dazzy]

Hi Folks

I have a form which I am using child/master fields to create a filter (see attached pic)

For the date ranges I need these to be

>= Start Date and <= Actual Finish Date

How can I set the criteria for these two fields, so that when someone picks a date it knows it's from or before the date entered.

Any help would be greatly appreciated.

Thanks

Gary

 

[Dazzy]

Any help would be greatly appreciated.

 

[JHB]

Post a striped version of you database with some sample data, (zip it).

 

[Galaxiom]

It can't be done with LinkFields. They only work where the values in the Master and Child match.

You could try to derive some value in the RecordSource query of the subform which could be matched against the Master but I don't know of one that would work.

An alternative is to use a subform RecordSource that refers to the two controls on the main form and requery the subform on the AfterUpdate of the these controls.

 

[jdraw]

Tell us WHAT you are trying to accomplish. You have told us HOW you have done something that isn't solving your need.

 

[Dazzy]

Thanks all

Basically each month we have to compile a report for an ESF funded project, the information expected for this report is quite intensive and by proving filters as shown above it allows the girl responsible to collate the data much more efficiently.

Each time the report will specify a particular date range which it wants the data for, and this is the reason for the from to fields.

I am beginning to think this may be to complicated for me to achieve.

If I were to advise her to use the datasheet date filters, is there a way to clear these filters when the clear all filters button is clicked?

Thanks

 

[RainLover]

Tell us WHAT you are trying to accomplish. You have told us HOW you have done something that isn't solving your need.

I am with jdraw and what he said.

Or to put it another way, if you don't know what the problem is you will never find a solution.

Where does this Master Child fit in. Your link to the pic does not take me there.

 

[Dazzy]

Hi Guys

I have stripped sensitive information, phone Nos. email, addresses etc out and have an attached a version to this thread. The form which I need help with in this thread will auto load.

Hopefully someone will able see the issue I am having.

I need to filter the form by date ranges as well as the existing filters on the form.

 

[jdraw]

Can you tell us in very plain English what your data base is about and what is the specific issue you are facing?

Pretend you're talking to the person behind you in a McDonalds line where that person doesn't know you; doesn't know database and has no idea of your business.

 

[contractor]

Hi Dazzy

Look at the example from Allen Browne http://allenbrowne.com/ser-62.html

success,

Contractor

 

[Dazzy]

Hi JDraw

Basically in this instance I need an easy way to filter all participants on a particular programme, ERNE 2, so the query behind the form filters all other data out.

I need to filter for specific date ranges, ie Number of participants in the 3 month period from 01/04/13 - 30/06/13. So for instance any participant starting in this period and any leaving in this period.

The database will eventually become a central hub for 95% of the information we need to track.

Does that help?

 

[RainLover]

I can't help with that attachment.

I only have Access 2003.

 

[Dazzy]

@RainLover thanks, the other solution I could use is to make the source query a parameter query but this will be a last option.

 

[jdraw]

Have you considered using user selected choices and adjusting a where clause accordingly, then resetting the recordsource all in vba?

I really do not understand your linkfields.

 

[Dazzy]

Hey

My VBA skills are nowhere near that level yet. The filter fields are as such

GenderFilter searches Gender field
LevelFilter searches Level upon commencing field

and so on

 

[JHB]

You need to find another way to get that to work, Master/Child link fields only shows the exact match, (Galaxiom mention the same in post #4), try to put in 23-10-2013 in the "From" control only one person shows up, the person with the Start date at the 23-10-2013.
The only reason why Gender "filter" works, is because there is an exact match.
Use a query or set a filter on the sub form.

 

[RainLover]

Dazzy

Master and Child should be linked by the Primary Key to the Foreign Key. It makes life easier if the Primary Key is AutoNumber.

You only select one record at a time in the Main Form.
The Sub contains all the Records where its Foreign Key matches the Primary Key of the Main Form.

You can filter the Sub Form by any field or fields that you require. You can even use a Date Range or any other Range you want.

Your earlier pic is a Datasheet and has nothing to do with Master Child situations.

Does this help at all?