And / Or Date Range Queries

[STEVENCV]

Hi all

I am trying to create a simple database to keep track of employee Car Insurances and MOT information.
I am trying to create a query that will show me the following:

When the field "motexpiry" is Empty OR has a date within 30 days from todays date (including if today's date is in the field) OR the date is in the past.

It also needs to show records with the same criteria for the field "insuranceexpiry".

And needs to show records where the field "cowensform" is blank.

These are all OR queries, so that as long as ONE of all of those criteria is met, the record shows up.

Here is what I have so far, which doesn't seem to work:

Once that query works, I need a very similar query but only showing records where one or more of those criteria is met, but only if the record also has "Oldham" in the "area" field.

I can then copy that query and edit the "Oldham" bit to have a query for each of our area offices.

I tried putting "Oldham" in the criteria line of the area field in the query design, but it seemed to have no affect.

Any help you can give me would be great, and if I have been confusing or not clear enough please ask for clarification!

I should point out, the -365 criteria is because I didn't know how to tell it to look for any date in the past, so it was meant as a workaround....

 

[plog]

Can you provide sample data from your table and then what you expect your query to produce based on that sample data? Be sure to include enough sample data to cover all cases.

 

[vbaInet]

Although the criteria isn't all there, I just wanted to show you what the OR criteria could look like.

 

[STEVENCV]

Certainly. When I run the query based on these criteria:

I get these results:

The coloured arrows show me who SHOULD show up, as follows:

Red Arrow - Joe Bloggs, because the "motexpiry" date is within the next 30 days.
Blue Arrow - John Smith, because the "insuranceexpiry" date is before today.
Green Arrow - Sarah Jones, because the "cowensform" date is empty.

Frank Flash should NOT show up on the list, because none of his fields match any of those criteria, which suggests that the whole query is incorrect and is bringing up all records.

Is this enough data for you, or do you wish me to run further tests?

 

[STEVENCV]

Although the criteria isn't all there, I just wanted to show you what the OR criteria could look like.

Thanks for the reply.

If I wanted to include "Is Null" on both the left hand column AND the middle column, would I just edit it say ">Date()+30 Or >Date()-365 Or Is Null" ?

And is there a more suitable query than ">Date()-365" that just encompasses all dates that prior to today's date?

 

[STEVENCV]

Also, does the +30 days criteria bring up records with TODAY'S date in that field?

 

[vbaInet]

Thanks for the reply.

If I wanted to include "Is Null" on both the left hand column AND the middle column, would I just edit it say ">Date()+30 Or >Date()-365 Or Is Null" ?

It depends on the data. If you're trying to do it in such a way that, if the mot and insurance criteria is not met, then cowensform should be null, the Is Null criteria will be on the same lines (i.e. twice) as both criteria.

plog is helping you out with the proper criteria anyway, so I'll just be on the sidelines.

 

[STEVENCV]

Thanks.

I think it definitely needs to be OR. CowensForm is the form the employees fill in with their information, so in theory if that form has a date, the rest of the information should be there also. Looking at the raw data, that doesn't seem to be the case. There are employees with a date in the cowensform field that have blanks in the insuranceexpiry field.

 

[plog]

To help, I need 2 sets of data: Starting data and resulting data. No explanations, no graphics, just 2 sets of data.

 

[STEVENCV]

Not a problem.

Table Data:

Query Criteria:

Results of Query:

Thank you.

 

[vbaInet]

Steve, I believe plog wants that in a spreadsheet so he can play with it.

 

[STEVENCV]

I have attached the actual database, does that help?

 

[JHB]

...
Frank Flash should NOT show up on the list, because none of his fields match any of those criteria, ..

Oh yes he should, because his motexpiry and insuranceexpiry is > 8 September 2013.

 

[STEVENCV]

Then I must have made a complete rookie mistake in my criteria, as it's supposed to only show up records where the actual date in the fields motexpiry or insuranceexpiry is in the past. As Frank Flash's dates are all in 2015, he should not show up.

 

[STEVENCV]

I think my initial attempts as creating this query may have confused people as to my intentions.

For the records, when I run the query, it needs to bring up all records where any one of these conditions are met:

motexpiry date is null, is today, is within 30 days after today or is in the past.
insuranceexpiry date is null, is today, is within 30 days after today or is in the past.
cowensform field is null.

I then need to create a query that matches that one, but with the added criteria of only bringing records who match any of those criteria but are also in a specific area, as denoted by the "area" field. Then I create a query for each of our area offices, and run a query that brings all results where any of those pieces of information are missing, expired or shortly due to expire.

I hope that clears it up, but please ask if I have not explained it well enough.

And thank you all for you help, I really do appreciate it.

 

[JHB]

Then the query must be:

SELECT Employees.firstname, Employees.surname, Employees.area, Employees.motexpiry, Employees.insuranceexpiry, Employees.cowensform, Date()-365 AS [>Date()-365], Date()+30 AS [<Date()+30]
FROM Employees
WHERE (((Employees.motexpiry)>Date()-365 And (Employees.motexpiry)<Date()+30)) OR (((Employees.motexpiry) Is Null)) OR (((Employees.insuranceexpiry)>Date()-365 And (Employees.insuranceexpiry)<Date()+30)) OR (((Employees.insuranceexpiry) Is Null)) OR (((Employees.cowensform) Is Null));

 

[STEVENCV]

Thank you for the reply. That seems to work perfectly.

Can I just clarify something, because I don't understand how that works.

You put the criteria as: >Date()-365 And >Date()+30

Why is "And" used and not "Or"? I am looking for records where the date is EITHER in the past or up to 30 days in the future.

I'm not trying to question you, just trying to learn so I can help myself in future.

Also. How would I amend that query so that it only showed records that had "Oldham" in the "area" field?

 

[JHB]

You put the criteria as: >Date()-365 And >Date()+30

Why is "And" used and not "Or"? I am looking for records where the date is EITHER in the past or up to 30 days in the future.

My criteria is >Date()-365 And <Date()+30 and not how you show it!
If you use OR then you'll get a result if only one of the criteria is true.
If you use AND then both of the criteria has to be true.

Also. How would I amend that query so that it only showed records that had "Oldham" in the "area" field?

Then put in "Oldham" in the "area" field for all criteria (5 times).

 

[STEVENCV]

I'm really sorry but I'm getting confused now.

When use your criteria, it brings up the correct results, and yet your criteria states that the "motexpiry" must be BOTH in the last 365 days AND in the next 30 days? Is that right?

For example

Joe Bloggs moteexpiry is 26/09/2014, he insurance expiry is 01/01/2015 and his cowens form is 31/08/2014.

When I run your query, his record is shown in the results, because his motexpiry is within the last 365 days. But your criteria states that the "motexpiry" must be BOTH in the last 365 days AND in the next 30 days? Is that right?

I don't need it to be both, it just needs to be either/or.

 

[JHB]

Moteexpiry date must be in the time frame from a year ago and until today + 30 days.
If you don't need the time frame Date()-365 - Date()+30, then take the Date()-365 out of the criteria.
Then you'll get all who had a moteexpiry in the past and until today + 30 days.