need both criteria to be true to display

[PlasticMonster]

I'm sure this is easy but I'm properly stuck

I am making a database that processes access control requests for a large building.

I need to filter what is a valid request between two dates.

Requests come in with an access from and an access to text box on the table.

I can get it to filter on any one of these but when they are both combined if either is true (and they always are with my between check) then I get everything.

How do I demand that both of these criteria are true, I'm sure it's an and needed somewhere..... I played with adding another hidden column to the query to ask if both were true but on opening or refreshing it wanted me to tell it!!!!

 

[mahenkj2]

It is not much clear what you want to know but I assume that you need to filter the data in a query which falls between two particular dates.

If this is the case, put below lines in the criteria of the query:

Between [forms]![frmSelectDate]![From] And [forms]![frmSelectDate]![To]

where frmSelectDate is the name of form which you are using to input the filter and From and To are the two fields for two dates.

Hope it helps.

 

[PlasticMonster]

Thanks for helping, sorry I wasnt clear.

The above solution is basically where I am now.

I have the query [QRY] and it has the two fields [Access From] and [Access To]

This check placed in the criteria will give me a vaild result for each column and shows the data if it is true.

I need to have it only show if both are true.

I need to specify a time frame for the query a {date from} and a {date to}. This might be the same day or it could be a week or a month apart. I only want to show what requests would be valid within that period.

 

[mahenkj2]

Can you please post a sample dB and indicate your needs by example. I am not getting clearly.

 

[PlasticMonster]

ok, ive tried to build a model of what im trying to do, obviously a clean version.

"Database concept" works as intended..

However when I try and expand on that it falls flat on my face. Im sure ive got the links wrong or missed something simple.

I need the results in the subfrom, ideally with the date from and date to boxes on the form. Changing the addresses of the criteria in the query to look at the form makes this strange behaviour come about and gives me nothing.

Id love a pointer.

 

[mahenkj2]

I attached again the dB. Hopefully, I understood what you are after.

I added one query, one form and one subform. Now when you enter the dates and press Run Query button, the subform shall be updated based on the date selected. Subform uses the recordsource on the query and the date filter are placed in subform record source not in the query directly, because you want to see updated query in subform and not directly.

I understood that you want to see the result true for any of date, so placed conditions in criteria for one field and one in OR for another field. Incase you have other targets, please play with it.

regards.

 

[boblarson]

This might also be helpful:
http://www.baldyweb.com/OverLap.htm

 

[mahenkj2]

Thanks Bob. It should be helpful for me too.

 

[PlasticMonster]

I was all excited!! But it wont open At least I have a guide now on where the queries should sit! I think that was where I was going wrong, if i know where they are meant to go I can play with them.

Bob: ill have a look now, ive looked at this buys links for other problems, hopefuly he will solve this too!! Might have helped if I knew enough to know what function I was looking for in the first place!

Thankyou both

I attached again the dB. Hopefully, I understood what you are after.

I added one query, one form and one subform. Now when you enter the dates and press Run Query button, the subform shall be updated based on the date selected. Subform uses the recordsource on the query and the date filter are placed in subform record source not in the query directly, because you want to see updated query in subform and not directly.

I understood that you want to see the result true for any of date, so placed conditions in criteria for one field and one in OR for another field. Incase you have other targets, please play with it.

regards.

 

[PlasticMonster]

OK following your advise that the check needs to go in the subform record source, I used the builder (for the first part of this) but am getting the error message that it cannot be calculated as its too complicated or mistyped...why would the builder get it wrong

SELECT [SDC CU Query].[First Name], [SDC CU Query].Surname, [SDC CU Query].Company, [SDC CU Query].[Access From], [SDC CU Query].[Access To], [SDC CU Query].[Card Issued] FROM [SDC CU Query] WHERE ((([SDC CU Query].[Access From])<Forms![SDC CU Form]!tbFrom Or ([SDC CU Query].[Access From])=Forms![SDC CU Form]!tbFrom)) Or ((([SDC CU Query].[Access From]) Is Null));

 

[PlasticMonster]

OK this now works properly except for the original issue of the thread.

I have the following code in the records source of the subfrom:

SELECT [SDC CU Query].[First Name], [SDC CU Query].Surname, [SDC CU Query].Company, [SDC CU Query].[Access From], [SDC CU Query].[Access To], [SDC CU Query].[Card Issued] FROM [SDC CU Query] WHERE ((([SDC CU Query].[Access From])>Forms![SDC CU Form]!tbFrom Or ([SDC CU Query].[Access From])=Forms![SDC CU Form]!tbFrom) And (([SDC CU Query].[Access To])<Forms![SDC CU Form]!tbTo Or ([SDC CU Query].[Access To])=Forms![SDC CU Form]!tbTo Or ([SDC CU Query].[Access To])=Forms![SDC CU Form]!tbTo Or ([SDC CU Query].[Access To])=[Forms]));

But this is producing all records that are true for EITHER after the "date from" checks or before the "date to" checks. I need BOTH to be true, where am I going wrong?

 

[PlasticMonster]

Ive now tried to modify it to do both checks but this is giving me the error again on too complex a calculation...IM sure it worked a few times then gives errors until i restart it.

SELECT [SDC CU Query].[First Name], [SDC CU Query].Surname, [SDC CU Query].Company, [SDC CU Query].[Access From], [SDC CU Query].[Access To], [SDC CU Query].[Card Issued] FROM [SDC CU Query] WHERE ((([SDC CU Query].[Access From])>Forms![SDC CU Form]!tbFrom And ([SDC CU Query].[Access From])<Forms![SDC CU Form]!tbTo) And (([SDC CU Query].[Access To])<Forms![SDC CU Form]!tbTo And ([SDC CU Query].[Access To])>Forms![SDC CU Form]!tbFrom)) Or ((([SDC CU Query].[Access From])=Forms![SDC CU Form]!tbFrom And ([SDC CU Query].[Access From])<Forms![SDC CU Form]!tbTo Or ([SDC CU Query].[Access From])=Forms![SDC CU Form]!tbTo) And (([SDC CU Query].[Access To])=Forms![SDC CU Form]!tbTo And ([SDC CU Query].[Access To])<Forms![SDC CU Form]!tbFrom Or ([SDC CU Query].[Access To])=Forms![SDC CU Form]!tbFrom));