And / Or Date Range Queries

[vbaInet]

Can I point out two things:

1. The +30 and -365 are inaccurate anyway. Not all months have 30 days and a leap year is 366 days. You can worry about getting the correct calculations later. Just sort out the logic first.

2. Is it this

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

Or this

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

Or this

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

?

 

[ConnorGiles]

Okay steven im currently playing around with your database and you have said that FRANK FLASH shouldnt show up because his dates are in 2015 - but the way im seeing it everyone but joe bloggs and john smith have all dates as 2015? so should sarah be showing either in your search results?

 

[STEVENCV]

Thanks Connor.

The date in the cowensform field do not matter, all I need the query to do is pull records if that field is empty.

The others all have various criteria that SHOULD be pulled through onto the query.

Joe Bloggs should show up because his motexpiry date is within the next 30 days.
John Smith should show up because his insuranceexpiry date is in the past
Sarah Jones should show up because her cowensform field is empty.
Frank Flash should not show up at all, as he does not meet the criteria.

I need all records to show up where ANY of the following conditions are met:

motexpiry date is in the past
motexpiry date is today
motexpiry date is in the next 30 days
motexpiry date is empty
insuranceexpiry date is in the past
insuranceexpiry date is today
insuranceexpiry date is in the next 30 days
insurance expiry date is empty
cowensform is empty

They don't need more than one, or all of those, criteria to be met, just any one of them.

 

[STEVENCV]

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.

So the criteria "<Date()+30" covers the past, today AND 30 days in the future? That's good to know, should make my criteria easier to write.

 

[STEVENCV]

Can I point out two things:

1. The +30 and -365 are inaccurate anyway. Not all months have 30 days and a leap year is 366 days. You can worry about getting the correct calculations later. Just sort out the logic first.

2. Is it this

Or this

Or this
?

The accuracy is not a problem. It doesn't have to be the last year, just in the past. I used that -365 criteria as I was unsure how to word it for past dates.

And the +30 is just a guide to show what is due for renewal in the near future, it does not have to be an actual calendar month.

As to your question, it's this one:

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

 

[vbaInet]

Lastly, I'll take the first one as an example. Notice the highlighted part:

motexpiry date is null, is today, is within 30 days after todayor is in the past.

The blue part can be one condition. BETWEEN Today and Today+30

 

[ConnorGiles]

BETWEEN Today and Today+30

Which would be set out as "Between Date() And Date()+30" - Not contridicting you Vba just putting it in a simpler form

 

[STEVENCV]

But how would I then factor in the past into that criteria?

 

[vbaInet]

... it in a simpler form

That's written in plain english, not in code.

 

[ConnorGiles]

Vba i meant something that would be better understood

 

[vbaInet]

See the attached screenshot.

 

[STEVENCV]

Thank you. That works beautifully.

I now need to be able to create queries by area.

I added the criteria "Oldham" as below:

And it gave me these results:

What it needs to do is pull all records that match all of the criteria you guys have helped me with, but ONLY for those records where the "area" field is "Oldham".

I will then copy the query, and edit Oldham to be each of my areas so I can do a full break down of by area if required.

Thank you for all your help - it is so close to being finished.

 

[vbaInet]

Add two more below.

 

[STEVENCV]

Perfect!

Thank you ALL so much for your help. I know it can be frustrating talking someone through something when they clearly don't have experience of doing it, so thank you for your patience and help.

This forum has helped me many times in the past (and I'm sure I'll need it again in the future), and it wouldn't be a success without people like you, willing to help a stranger for nothing.

 

[vbaInet]

Pleased to know that you're finding the forum useful.

We could have simply written the SQL statement for you, but seeing it on the grid makes it much easier for you to comprehend.

 

[ConnorGiles]

Glad you got it sorted and glad Vba sorted you out in the end