Date field greater than a certain date in two fields

kevsvette

New Member
Joined
Oct 5, 2015
Messages
24
I have a table that has 2-columns with dates in them and a 3rd column with peoples names. I want to use access to get rid of all records except associated with certain people (which I've figured out) AND that has a date in either of the 2-date columns that's greater than 12/2/2017. There are some blanks in either of the date fields that I also need to exclude.

Help!

So basically if the name field name matches what I want & the 1st date field is 12/3/2017 & the 2nd date field is null or <=12/2/2017 I need to retain it or vice versa. Only if both date fields are null or the the 2 dates in both fields are <=12/2/2017 do I want to exclude it.

Can this be done?
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
First create a select query to confirm you are selecting the right records for deletion.
As a starter, try fiddling with this one:

Code:
select *
from Table1
where 
	(NameField not in ('name1','name2','name3'))
	and 
	(Nz(DateField1, #12/3/2017#) < #12/2/2017#)
	and 
	(Nz(DateField2, #12/3/2017#) < #12/2/2017#)

Once it works, change it to a delete query:
Code:
[COLOR="#FF0000"]delete[/COLOR] *
from Table1
where 
	(NameField not in ('name1','name2','name3'))
	and 
	(Nz(DateField1, #12/3/2017#) < #12/2/2017#)
	and 
	(Nz(DateField2, #12/3/2017#) < #12/2/2017#)

Back up the database first and save it in case of screwups!
 
Upvote 0
Ok I should have said that I'm new to access... I wish I could share the sql query but its business related. I'm not sure how to input your recommendation... I meant to update my initial post, but was out of time or something so below is my updated ?

I have a table that has 2-columns with dates in them and a 3rd column with people’s names. I want to use access to get rid of all records except associated with certain people (which I've figured out) AND that also has a date in either of the two 2-date columns that's greater than 12/2/2017. There are some blanks in either of the date fields that I also need to exclude.

1st the Name field has to be one of 10 people that I'm interested in retaining & further ...

if 1st date field is 7/4/2018 & the 2nd is blank ... keep the record because of 1st date
if 1st date field is 8/1/2016 & the 2nd is 1/1/2018 ... keep the record because of 2nd date
if both date fields are blank ... exclude it, no dates
if both date fields have dated prior to 12/2/2017 ... exclude it

Can this be done in design view as its easier for me.
 
Last edited:
Upvote 0
This is the SQL Query that I have currently which limits the “RefTable” to just the 10-people that I want to retain out of gosh know how many. I’ve changed the Field Names and highlighted in red those that I’m interested in…

SELECT RefTABLE.[FIELD NAME], RefTABLE.[FIELD NAME], RefTABLE.[PERSON OF INTEREST FIELD NAME], RefTABLE.[FIELD NAME], RefTABLE.[1st DATE FIELD NAME], RefTABLE.[2nd DATE FIELD NAME], RefTABLE.[FIELD NAME], RefTABLE.[FIELD NAME], RefTABLE.FIELD NAME, RefTABLE.FIELD NAME, RefTABLE.[FIELD NAME], RefTABLE.FIELD NAME, RefTABLE.FIELD NAME, RefTABLE.FIELD NAME
FROM RefTABLE
WHERE (((RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME"));

… but I’d like to additionally limit it to only those 10-individuals along with a date of >12/2/2017 in either the 1st DATE FIELD NAME OR the 2nd DATE FIELD NAME.
Can this be done in design view as I don’t understand a lot of the syntax since I’m new to Access.

My attempts has excluded records that I know that should be included because one field or the other has a date less than my specified date.

Hope this helps… The only thing I’ve been able to find in my searches is how to limit 1 date field… not 2 with dependencies on whether or not one of the two fields has a date that is greater than a specified date.
 
Upvote 0
It can be done in the design grid but it will be far more complex than using SQL - especially if you continue to use 'OR' for every Name - instead consider using IN. So in one line of the criteria you would enter

Code:
IN("Name1","Name2","Name3",...)

What you are trying to do looks fairly straight forward:

Code:
Select [Field1], [Field2], [Field3], [Person of Interest Field], [1st Date], [2nd Date], [Field4]
From RefTable
Where [Person of Interest Field] IN ("Name1", "Name2", "Name3", "Name4", "Name5", "Name6") AND ((Nz([1st Date], #13/3/2017#) < #12/2/2017#) AND (Nz([2nd Date], #13/3/2017#) < #12/2/2017#))
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top